PuppetDB 5.0 » Puppet query language (PQL) » Examples

This version of PuppetDB is not included in Puppet Enterprise. The latest version of PE includes PuppetDB 4.4.

Example Queries

Below is a list of example PQL queries that you may find useful.

Other resources you may also find useful include:


Filtering on node names

Query nodes with green in their name.

nodes { certname ~ 'green' }

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:06:26.275Z",
        "certname": "greenserver.vm",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:06:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "4a956674b016d95a7b77c99513ba26e4a744f8d1",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "changed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:06:18.393Z"
    }
]

Querying for active nodes

nodes {deactivated is null and expired is null}

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:06:26.275Z",
        "certname": "foo.com",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:06:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "az956674b016d95a7b77c99513ba26e4a744f8d1",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "changed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:06:18.393Z"
    }
]

Basic fact filtering

Nodes with operating system name CentOS.

inventory { facts.os.name = "CentOS" }

Output (abbreviated):

[
    {
        "certname": "centos70.vm",
        "environment": "production",
        "facts": {
            ...
            "operatingsystem": "CentOS",
            "operatingsystemmajrelease": "7",
            "operatingsystemrelease": "7.0.1406",
            "os": {
                "architecture": "x86_64",
                "family": "RedHat",
                "hardware": "x86_64",
                "name": "CentOS",
                "release": {
                    "full": "7.0.1406",
                    "major": "7",
                    "minor": "0"
                },
                "selinux": {
                    "enabled": false
                }
            },
            "osfamily": "RedHat",
            ...
        },
        "timestamp": "2016-08-15T11:06:26.140Z",
        "trusted": {
            "authenticated": "remote",
            "certname": "centos70.vm",
            "domain": "vm",
            "extensions": {},
            "hostname": "centos70"
        }
    }
]

Fact and resource filtering

Nodes with CentOS operating system, and with a declared httpd service resource.

inventory[certname] { facts.operatingsystem = "CentOS" and
                      resources { type = "Service" and title = "httpd" } }

Output:

[
    {
        "certname": "greenserver.vm"
    }
]

Fact, resource and resource parameter filtering

RedHat boxes in the PDX datacenter, that have their java package forced to 1.7.0.

inventory[certname] { facts.osfamily = "RedHat" and
                      facts.datacentre = "PDX" and
                      resources { type = "Package" and
                                  title = "java" and
                                  parameters.ensure = "1.7.0" } }

Output:

[
    {
        "certname": "greenserver.vm"
    }
]

Fact and resource filtering for classes

CentOS boxes with the apache Puppet class.

inventory[certname] { facts.operatingsystem = "CentOS" and
                      resources { type = "Class" and
                                  title = "apache" } }

Output:

[
    {
        "certname": "greenserver.vm"
    }
]

Fact, resource and environment filtering

All windows servers, with service sqlserver in a particular feature branch environment.

nodes { certname in inventory[certname] { facts.osfamily = "Windows" } and
        resources { type = "Service" and
                    title = "sqlserver" } and
        report_environment = "feature_SYS-4926" }

Output:

[ {
  "deactivated" : null,
  "latest_report_hash" : "4a956674b016d95a7b77c99513ba26e4a744f8d1",
  "facts_environment" : "laboratory",
  "cached_catalog_status" : "not_used",
  "report_environment" : "laboratory",
  "latest_report_corrective_change" : false,
  "catalog_environment" : "laboratory",
  "facts_timestamp" : "2016-07-18T04:12:12.912Z",
  "latest_report_noop" : false,
  "expired" : null,
  "latest_report_noop_pending" : null,
  "report_timestamp" : "2016-07-18T04:12:15.907Z",
  "certname" : "windowserver.vm",
  "catalog_timestamp" : "2016-07-18T04:12:12.917Z",
  "latest_report_status" : "success"
} ]

Timestamp filtering

Nodes that haven’t checked in for 7 days.

nodes { report_timestamp <= "2016-08-03 00:00:00" }

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:06:26.275Z",
        "certname": "greenserver.vm",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:06:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "4a956674b016d95a7b77c99513ba26e4a744f8d1",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "changed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:06:18.393Z"
    }
]

Mcollective sub-collective filtering

Show nodes that belong in the sub-collective dc1.

inventory[certname] { facts.mcollective.server.collectives.match("\d+") = "dc1" }

Output:

[
    {
        "certname": "greenserver.vm"
    },
    {
        "certname": "yellowserver.vm"
    }
]

Profile querying

Show active nodes that have the profile class Profile::Remote_mgmt applied to it.

nodes { resources { type = "Class" and title = "Profile::Remote_mgmt" }
        and expired is null and deactivated is null}

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:06:26.275Z",
        "certname": "greenserver.vm",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:06:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "4a956674b016d95a7b77c99513ba26e4a744f8d1",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "changed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:06:18.393Z"
    }
]

Querying catalog submission time

Check for nodes that haven’t had a catalog applied since a certain time.

nodes { catalog_timestamp < "2016-08-15T11:37:00.000Z" }

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:06:26.275Z",
        "certname": "greenserver.vm",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:06:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "4a956674b016d95a7b77c99513ba26e4a744f8d1",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "changed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:06:18.393Z"
    }
]

List of nodes with report status failed

List all nodes that have had a failure on their last run.

nodes { latest_report_status = 'failed' }

Output:

[
    {
        "cached_catalog_status": "not_used",
        "catalog_environment": "production",
        "catalog_timestamp": "2016-08-15T11:03:26.275Z",
        "certname": "redserver.vm",
        "deactivated": null,
        "expired": null,
        "facts_environment": "production",
        "facts_timestamp": "2016-08-15T11:03:26.140Z",
        "latest_report_corrective_change": null,
        "latest_report_hash": "68f56674b016d95a7b77c99513ba26e4a744f001",
        "latest_report_noop": false,
        "latest_report_noop_pending": null,
        "latest_report_status": "failed",
        "report_environment": "production",
        "report_timestamp": "2016-08-15T11:03:18.393Z"
    }
]

Query code_id from latest reports

Query all latest reports and show the certname and code_id.

reports[certname, code_id] { latest_report? = true }

Output:

[
    {
        "certname": "greenserver.vm",
        "code_id": "urn:puppet:code-id:1:519e404a1b6217b010cc543494c2dc50df8a53e3;production"
    },
    {
        "certname": "yellowserver.vm",
        "code_id": "urn:puppet:code-id:1:519e404a1b6217b010cc543494c2dc50df8a53e3;production"
    }
]

Reports that have not applied a code_id

Show reports that have not had a particular code_id applied.

reports[certname, receive_time] {
  latest_report? = true and
  ! code_id = 'urn:puppet:code-id:1:519e404a1b6217b010cc543494c2dc50df8a53e3;production'
}

Output:

[
    {
        "certname": "whiteserver.vm",
        "receive_time": "2016-08-15T10:33:07.130Z"
    },
    {
        "certname": "brownserver.vm",
        "receive_time": "2016-08-15T11:06:26.553Z"
    }
]

Show all exported resources

Show all exported resources

resources[certname, type, title] { exported = true }

Output:

[
    {
        "certname": "purpleserver.vm",
        "title": "purpleserver.vm-mysql",
        "type": "Monitor"
    },
    {
        "certname": "purpleserver.vm",
        "title": "purpleserver.vm-httpd",
        "type": "Monitor"
    },
    {
        "certname": "purpleserver.vm",
        "title": "purpleserver.vm-/etc",
        "type": "Backup"
    }
]

↑ Back to top