For a few releases now PuppetDB had a new query language called Puppet Query Language or PQL for short. It’s quite interesting, I thought a quick post might make a few more people aware of it.<\/p>\n
PQL Queries look more or less like this:<\/p>\n
\r\nnodes { certname ~ 'devco' }\r\n<\/pre>\nThis is your basic query it will return a bunch of nodes, something like:<\/p>\n
\r\n[\r\n {\r\n \"deactivated\": null,\r\n \"latest_report_hash\": null,\r\n \"facts_environment\": \"production\",\r\n \"cached_catalog_status\": null,\r\n \"report_environment\": null,\r\n \"latest_report_corrective_change\": null,\r\n \"catalog_environment\": \"production\",\r\n \"facts_timestamp\": \"2016-11-01T06:42:15.135Z\",\r\n \"latest_report_noop\": null,\r\n \"expired\": null,\r\n \"latest_report_noop_pending\": null,\r\n \"report_timestamp\": null,\r\n \"certname\": \"devco.net\",\r\n \"catalog_timestamp\": \"2016-11-01T06:42:16.971Z\",\r\n \"latest_report_status\": null\r\n }\r\n]\r\n<\/pre>\nThere are a bunch of in-built relationships between say a node and it’s facts and inventory, so queries can get quite complex:<\/p>\n
\r\ninventory[certname] { \r\n facts.osfamily = \"RedHat\" and\r\n facts.dc = \"linodeldn\" and\r\n resources { \r\n type = \"Package\" and\r\n title = \"java\" and\r\n parameters.ensure = \"1.7.0\" \r\n } \r\n}\r\n<\/pre>\nThis finds all the RedHat machines in a particular DC with Java 1.7.0 on them. Be aware this will also find machines that are deactivated. <\/p>\n
I won’t go into huge details of the queries, the docs are pretty good – examples<\/a>, overview<\/a>. <\/p>\n
Using<\/H2>
\nCLI<\/H3>
\nYou can of course query this stuff on the CLI and I suggest you familiarise yourself with JQ<\/a>.<\/p>\nFirst you’ll have to set up your account:<\/p>\n
\r\n{\r\n \"puppetdb\": {\r\n \"server_urls\": \"https:\/\/puppet:8081\",\r\n \"cacert\": \"\/home\/rip\/.puppetlabs\/etc\/puppet\/ssl\/certs\/ca.pem\",\r\n \"cert\": \"\/home\/rip\/.puppetlabs\/etc\/puppet\/ssl\/certs\/rip.mcollective.pem\",\r\n \"key\": \"\/home\/rip\/.puppetlabs\/etc\/puppet\/ssl\/private_keys\/rip.mcollective.pem\"\r\n }\r\n}\r\n<\/pre>\nThis is in ~\/.puppetlabs\/client-tools\/puppetdb.conf<\/em> which is a bit senseless to me since there clearly is a standard place for config files, but alas.<\/p>\n
Once you have this and you installed the puppet-client-tools<\/em> package you can do queries like:<\/p>\n
\r\n$ puppet query \"nodes { certname ~ 'devco.net' }\"\r\n<\/pre>\nPuppet Code<\/H3>
\nYour master will have the puppetdb-termini<\/em> package on it and this brings with it Puppet functions to query PuppetDB so you do not need to use a 3rd party module anymore:<\/p>\n\r\n$nodes = puppetdb_query(\"nodes { certname ~ 'devco' }\")\r\n<\/pre>\nPuppet Job<\/H3>
\nAt the recent PuppetConf Puppet announced that their enterprise tool puppet job<\/em> supports using this as discovery, if I remember right it’s something like:<\/p>\n\r\n$ puppet job run -q 'nodes { certname ~ 'devco' }'\r\n<\/pre>\nMCollective<\/H3>
\nAt PuppetConf I integrated this into MCollective and my Choria tool, mco feature still due a release (MCO-776<\/a>, choria<\/a>):<\/p>\nRun Puppet on all the nodes matched by the query:<\/p>\n
\r\n$ puppet query \"nodes { certname ~ 'devco.net' }\"|mco rpc puppet runonce\r\n<\/pre>\nThe above is a bit limited in that the apps in question have to specifically support this kind of STDIN discovery – the rpc<\/em> app does.<\/p>\n
I then also added support to the Choria CLI:<\/p>\n
\r\n$ mco puppet runonce -I \"pql:nodes[certname] { certname ~ 'devco.net' }\"\r\n<\/pre>\nThese queries are a bit special in that they must return just the certname as here, I’ll document this up. The reason for this is that they are actually part of a much larger query done in the Choria discovery system<\/a> (that uses PQL internally and is a good intro on how to query this API from code).<\/p>\n
\r\nnodes[certname, deactivated] { \r\n # finds nodes in the chosen subcollective via a fact\r\n (certname in inventory[certname] { \r\n facts.mcollective.server.collectives.match(\"\\d+\") = \"mcollective\" \r\n }) and \r\n \r\n # does the supplied PQL query, note the specific certname match\r\n (certname in nodes[certname] {\r\n certname ~ 'devco.net'\r\n }) and\r\n\r\n # limited to machines with mcollective installed\r\n (resources {\r\n type = \"Class\" and title = \"Mcollective\"\r\n }) and \r\n\r\n # who also have the service started\r\n (resources {\r\n type = \"Class\" and title = \"Mcollective::Service\"\r\n }) \r\n}\r\n<\/pre>\nConclusion<\/H2>
\nThis is really handy and I hope more people will become familiar with it. I don’t think this quite rolls off the fingers easily – but neither does SQL or any other similar system so par for the course. What is amazing is that we can get nearer to having a common language across CLI, Code, Web UIs and 3rd party tools for describing queries of our estate so this is a major win.<\/p>\n","protected":false},"excerpt":{"rendered":"
For a few releases now PuppetDB had a new query language called Puppet Query Language or PQL for short. It’s quite interesting, I thought a quick post might make a few more people aware of it. Overview To use it you need a recent PuppetDB and as this is quite a new feature you really […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","footnotes":""},"categories":[7],"tags":[85,21],"_links":{"self":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/3528"}],"collection":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/comments?post=3528"}],"version-history":[{"count":7,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/3528\/revisions"}],"predecessor-version":[{"id":3535,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/posts\/3528\/revisions\/3535"}],"wp:attachment":[{"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/media?parent=3528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/categories?post=3528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devco.net\/wp-json\/wp\/v2\/tags?post=3528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}