puppetdb2csv.rb

#!/opt/puppetlabs/puppet/bin/ruby

# This script exists to convert the output of simple PuppetDB queries into
# CSV format (tab-separated). It expects a JSON array of objects with 
# string/string keys/values.

require 'json'
require 'csv'

# Read in the PuppetDB query data from STDIN or filename argument
data = JSON.parse(ARGF.read)

# Bail out if no results returned
exit 0 if data.empty?

# Sort "certname" first; alphabetically thereafter
sorted = data.map(&:to_a).map do |row|
  row.sort do |a,b|
    case
    when a[0] == 'certname'; -1
    when b[0] == 'certname'; 1
    else a[0] <=> b[0]
    end
  end
end

# Generate headers and data in a tab-separated file format
csvstr = CSV.generate(col_sep: "\t") do |csv|
  csv << sorted[0].map(&:first).map(&:upcase)
  sorted.each do |row|
    csv << row.map(&:last)
  end
end

# Write the tab-separated data to STDOUT
STDOUT.write(csvstr)

queries.sh

# Return an array of certnames for systems that HAVE run Puppet since the
# given date:

puppet query '
  nodes[
    certname,
    report_timestamp
  ] {
    report_timestamp > "2019-09-18T00:00:00.000Z"
  }
'

# Return an array of certnames for systems that have NOT run Puppet since the
# given date:

puppet query '
  nodes[
    certname,
    report_timestamp
  ] {
    report_timestamp < "2019-09-18T00:00:00.000Z"
  }
'

# Return an array of all changes Puppet has made to a given system since the
# given date:

puppet query '
  events[
    new_value,
    corrective_change,
    property,
    old_value,
    resource_type,
    resource_title,
    message,
    timestamp
  ] {
    report in reports[hash] {
      certname = "se-puppet-dev-1.se.puppet.net" and
      start_time > "2019-09-18T00:00:00.000Z" and
      noop = false
    } and (
      status = "success" or
      status = "failure"
    )
    order by timestamp asc
  }
'

# Return an array of all changes Puppet has made to any systems since the
# given date:

puppet query '
  events[
    certname,
    new_value,
    corrective_change,
    property,
    old_value,
    resource_type,
    resource_title,
    message,
    timestamp
  ] {
    report in reports[hash] {
      start_time > "2019-09-18T00:00:00.000Z" and
      noop = false
    } and (
      status = "success" or
      status = "failure"
    )
    order by certname, timestamp asc
  }
'

run-example.sh

# Assume that the file query.pql contains a query to run.

puppet query "$(cat query.pql)" | ./puppetdb2csv.rb > results.csv