Puppet/PQL

PQL is a simple query syntax for the PuppetDB api. its much nicer to use then the older query syntax and is also supported by pypuppetdb. 
== Examples ==
PQL can be quite useful for debugging or collecting data on puppet resources. the following lists a few working PQL examples
additional examples may be found on puppetdb1002:/home/jbond/pql
The boiler plate python code is as follows, in the examples below i will mostly just add the actual PQL code unless there is something a bit more complicated
from pypuppetdb import connect
db = connect()
pql = """ ...the query... """
results = db.pql(pql)
for result in results:
 print(result)
you can also use the following curl command to get the raw json response, assumes the pql query is in a file call ./pql.txt. Uses the example below
$ curl -sX GET http://localhost:8080/pdb/query/v4 --data-urlencode "query=$(<pql.txt)" | jq .
[
 {
 "parameters": {
 "key": "AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBK2t99nf1hGiPs3whPlgx4GTCeIIM55C5zrLIaaINE9rTEikbGHSDj1/Kv8B9vC6n9J9h4yLHLRhV3eNLcVsuck=",
 "type": "ecdsa-sha2-nistp256",
 "ensure": "present",
 "host_aliases": [
 "ganeti2028",
 "10.192.0.127",
 "2620:0:860:101:10:192:0:127"
 ]
 },
 "title": "ganeti2028.codfw.wmnet"
 }
]
=== All nodes with Package['confd'] ===
nodes {
 resources { type = 'Package' and title = 'confd' }
}
=== All exported resources ===
resources[parameters,title] {
 exported = true
}
=== All exported sshkey resources ===
resources[parameters,title] {
 exported = true and type = 'Sshkey' and
}
=== All exported sshkey resources for a specific host ===
resources[parameters,title] {
 exported = true and type = 'Sshkey' and
 nodes { certname = "ganeti2028.codfw.wmnet" }
}
=== All instances of a resource with a specific paramater value ===
from pypuppetdb import connect
db = connect()
pql = """ 
resources{
 type = 'Systemd::Timer::Job' and
 parameters.monitoring_enabled = true
}
"""
resources = db.pql(pql)
for resource in resources:
 print(f'{resource} ({resource.parameters["monitoring_contact_groups"]})')
=== A list files matching a specific path ===
The following was used in T309268 to get a list of all managed sudo files. This list was then used to see if we have any unmanaged files in the infrastructure. For the curious a similar script was used using the older puppetdb syntax to audit apt sources apt sources
The following was used 
from pypuppetdb import connect
found_files = set()
db = connect()
pql = """
resources[parameters,title] {
 type = 'File' and (parameters.path ~ '^\/etc\/sudoers\.d\/' or title ~ '^\/etc\/sudoers\.d\/')
}
"""
resources = db.pql(pql)
for resource in resources:
 if 'path' in resource['parameters']:
 path = resource['parameters']['path']
 else:
 path = resource['title']
 found_files.add(basename(path))
print('|'.join(found_files))
=== List all teams and the roles they own ===
#!/usr/bin/python3
from collections import defaultdict
from pypuppetdb import connect
db = connect()
pql = """ 
resources [parameters, tags]{
 type = 'Class' and
 title = 'Profile::Contacts'
}
"""
owners = defaultdict(set)
resources = db.pql(pql)
for resource in resources:
 try:
 owner = resource['parameters']['role_contacts'][0]
 except IndexError:
 owner = 'Unknown'
 role = [r for r in resource['tags'] if r.startswith('role::')][0]
 owners[owner].add(role)
for owner, roles in dict(sorted(owners.items())).items():
 print(f'= {owner} =')
 for role in sorted(roles):
 print(f'[] {role.lstrip("role::")}')
=== Produce a list of all files changed during a specific window ===
The following script was used to audit files that where unexpectedly altered when we [ https://gerrit.wikimedia.org/r/c/operations/puppet/+/809095 changed the default file owner to root]
from collections import defaultdict
from pypuppetdb import connect
resources = defaultdict(list)
fixed = [
 '/etc/ferm',
 # as files where fixed add them here
]
fixed_prefix = (
 '/srv/phab/phabricator/scripts',
 # when all files in a folder are fixed add it here
db = connect()
pql = """
reports {
 start_time >= "2022-09-07 11:33:25" and start_time <= "2022-09-07 12:03:25" 
 order by start_time
}
"""
reports = list(db.pql(pql))
for report in reports:
 if report.node in reports:
 continue
 for event in report.events():
 if event.item['type'] != 'File':
 continue
 if event.item['property'] not in ['group', 'owner']:
 continue
 if event.item['title'] in fixed:
 continue
 if event.item['title'].startswith(fixed_prefix):
 continue
 try:
 item = '|'.join([
 str(event.item['source_file']), # is sometimes none
 event.item['title'],
 event.item['property'],
 event.item['old'],
 event.item['new'],
 ])
 print(f'{report.node}:{item}')
 resources[report.node] = item
 except:
 print(event.item)
 raise
=== All changed resources using a specific change revision ===
For the above we could also query all reports with a specific sha hash. however if a second change is merged within the 30 then this may not align as expected as such its better to query for the specific window. however i include it here as it could be useful for something
reports {
 configuration_version ~ "7acc980cf4" 
 order by start_time
}
=== List of hosts on specific version ===
used to generate the list for the reboot tasks
#!/usr/bin/env python3
import logging
import re
# this is dprecate we should switch to packaging.version.parse but thats third party
from distutils.version import LooseVersion
from pypuppetdb import connect
TARGET_VERSION = '5.10.149'
def main():
 kernel_fact_matcher = re.compile(r'Debian\s+(?P<version>\d+\.\d+\.\d+)')
 db_matcher = re.compile(r'^(?:db(?:store|proxy)?|es|pc)\d{4}\.')
 wmcs_matcher = re.compile(r'^(?:cloud|labs)')
 min_kernel_version = LooseVersion(TARGET_VERSION)
 target_major = TARGET_VERSION.rsplit('.', 1)[0]
 db = connect()
 pql = fr"""
 inventory {{ facts.kernelmajversion = "{target_major}" }}
 """
 results = db.pql(pql)
 hosts = {
 'database': [],
 'wmcs': [],
 'prod': [],
 }
 for result in sorted(results, key=lambda x: x.node):
 match = kernel_fact_matcher.search(result.facts['kernel_details']['version'])
 if match is None:
 logging.warning(
 '%s: unable to parse kernel version (%s)',
 result.node, facts['kernel_details']['version']
 )
 continue
 current_version = LooseVersion(match['version'])
 upgraded = 'x' if current_version >= min_kernel_version else ''
 line = f"[{upgraded}] {result.node} ({result.facts['os']['distro']['codename']})"
 if db_matcher.match(result.node):
 hosts['database'].append(line)
 continue
 if wmcs_matcher.match(result.node):
 hosts['wmcs'].append(line)
 continue
 hosts['prod'].append(line)
 for team, hosts in hosts.items():
 print(f"{team} hosts\n{'='*40})")
 print("\n".join(hosts) + "\n\n")
if __name__ == '__main__':
 raise SystemExit(main())
== More information ==
PQL examples
Category:Puppet
Category:SRE Infrastructure Foundations