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

Refreshing the package lists

If you need to do Enterprise reporting on installed software but don’t use CFEngine to manage packages otherwise, you need to refresh the package list for a specific package method. This is already implemented in the masterfiles, under inventory/any.cf with special handling for Debian and Red Hat systems to avoid network traffic at the time of the refresh. If you want to do it yourself, you can force it like this (the package cfe_internal_non_existing_package has some internal handling to avoid warnings):

cf3 packages: "cfe_internal_non_existing_package" package_policy => "add", package_method => generic; }

That will cause the package manager to generate a package list, which will then be reported in the Software table. You can’t use an existent package there, it has to be a bad package to force the package manager inventory to be refreshed.

See also: packages.

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