SQL Schema

CFEngine allows all standardized SQL SELECT constructs to query, with the following additions:

• TIMESTAMP_UNIX() - seconds elapsed since 1970
• TIMESTAMP_UNIX_DAYS() - days elapsed since 1970

These are added to avoid use of non-portable SQL date/time functions.

Examples

• Group LAST status of promises in bundle "ntp" in hosts matching class "ntp_server".

The result should include a list of the promises in the bundle, together with counts of the four statuses (kept, repaired, not kept, unknown).

    SELECT Status, Count(*)
      FROM PromiseStatusLast
     WHERE Bundle=ntp_server AND Context=ntp_server
     GROUP BY Status

• Group all file changes by file name occurred on context "ubuntu". There are counters on how many distinct host occurrences and total occurrences found.

    SELECT
         FileChanges.FileName,
         Count(Distinct(FileChanges.HostKey)) AS DistinctHostCount,
         COUNT(1) AS ChangeCount
      FROM
         FileChanges JOIN Contexts
      WHERE 
         Contexts.ContextName='ubuntu'
      GROUP BY
         FileChanges.FileName
      ORDER BY
         ChangeCount DESC

• List all software names and versions installed, with the host and host OS.

    SELECT 
          Hosts.Hostname,
          Variables.VariableValue,
          Software.SoftwareName, 
          Software.SoftwareVersion
     FROM 
          Hosts,
          Variables,
          Software 
     WHERE
          Variables.VariableName='flavor'
          AND Hosts.HostKey=Variables.HostKey
          AND Variables.HostKey=Software.HostKey
     ORDER BY 
          Software.SoftwareName

Inventory

Hosts

Contexts

Variables

Note: Lists are split into multiple rows.

Possible types: string, int, real, menu, string list, int list, real list, menu list.

File Changes

FileChanges

Promise information

PromiseStatusLast

Note: Only the last promise status (not log).

Join from PromiseDefinitons to query by other promise attributes.

PromiseDefinitions

Note: This uses expanded promise definitions.

Promisees is a list, split into multiple rows. This means that promise handles might appear multiple times, if they have multiple promisees, depending on the exact query. Use an embedded query with DISTINCT() to avoid this.

example:

SELECT
       Hosts.Name, BundleNTP.PromiseHandle
  FROM 
       Hosts,
       (SELECT
               DISTINCT(PromiseHandle)
          FROM
               PromiseDefinitions
          WHERE
               Bundle='ntp'
       )
    AS
       BundleNTP, PromiseStatusLast
 WHERE
       Hosts.HostKey=PromiseStatusLast.HostKey
       AND BundleNTP.PromiseHandle=PromiseStatusLast.PromiseHandle
       AND PromiseStatusLast.PromiseStatus='notkept'

PromiseLogs

Until CFEngine Enterprise v3.0 Promise logs were separated into: Promise Repaired log and Promise NotKept log. The SQL Reporting Engine merges these reports into one with the introduction of a new field(column): PromiseStatus

BundleStatus

Benchmarks

PolicyStatus

Software

Software

SoftwareUpdates

Database Diagnostics

Diagnostics of the MongoDB database. For detailed documentation, see the MongoDB documentation.

LastSeenHosts

DatabaseServerStatus

Data can be correlated with hub diagnostics by ReportTimeStamp.

DatabaseStatus

Data can be correlated with hub diagnostics by ReportTimeStamp.

DatabaseCollectionStatus

Data can be correlated with hub diagnostics by ReportTimeStamp.

Hub Diagnostics

Data can be correlated with MongoDB diagnostics by ReportTimeStamp.

Performance is measured in time the operation took, in millisecond. Data sizes are measured in kilobytes.

HubReportingPerformance

HubConnectionErrors

HubMaintenancePerformance

HubMaintenanceErrors