Pulling information from puppet stored config DB
I recently enabled stored configs for my puppet installation. My primary aim for doing this was to use the collected infomation to dynamically generate lists of nodes to use with capistrano based on joe-mac's recent blog post.
The concept is simple, but very powerful: run a query on the puppet stored config database to return a list of host names matching some criteria. In my case, I just need a list of hosts that have a specific puppet class applied. So, without further ado, here's the SQL needed to produce just such a list:
select h.name from hosts h join resources r on h.id = r.host_id join resource_tags rt on r.id = rt.resource_id join puppet_tags pt on rt.puppet_tag_id = pt.id where pt.name = 'class' and r.restype = 'Class' and r.title = 'site::service::dnscache::local' order by h.name;
This example returns a list of nodes that have a local dnscache service.
To find all nodes running a mysql service I use the same query, replacing the class with site::service::mysql.
I can also modify the query slightly to find all nodes that have apache installed:
select h.name from hosts h join resources r on h.id = r.host_id join resource_tags rt on r.id = rt.resource_id join puppet_tags pt on rt.puppet_tag_id = pt.id where pt.name = 'class' and r.restype = 'Package' and r.title = 'httpd' order by h.name;
However, be aware that this does not actually tell me all nodes that have apache installed; it tells me all nodes that have the httpd package included in their puppet definitions. For example, on my LVS master node, I include the piranha package, which pulls in httpd and php as dependencies. The above query doesn't return the name of my LVS master node.
More to follow...