Chef Server Report: Script to list all nodes in all orgs, grouped by org, then environment, then chef client version.

README.md

Example output:

root@chef:~# ./chef_server_nodes_report.rb -h
Usage: chef_server_nodes_report.rb [options]
    -g, --group-by TYPE              Group results by: ["version", "org", "environment", "check_in_month", "check_in_date", "platform_family", "platform_version", "platform", "os"]

root@chef:~# ./chef_server_nodes_report.rb --group-by version
loading config from /etc/opscode
{
  "version": {
    "12.13.37": {
      "client02": {
        "version": "12.13.37",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-09",
        "check_in_date": "2016-09-06",
        "platform_family": "debian",
        "platform_version": "14.04",
        "platform": "ubuntu",
        "os": "linux"
      }
    },
    "12.15.19": {
      "es-node-02": {
        "version": "12.15.19",
        "org": "e9",
        "environment": "production",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-04",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      },
      "es-node-03": {
        "version": "12.15.19",
        "org": "e9",
        "environment": "production",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-05",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      }
    },
    "12.16.42": {
      "uploader01": {
        "version": "12.16.42",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-11",
        "check_in_date": "2016-11-14",
        "platform_family": "debian",
        "platform_version": "14.04",
        "platform": "ubuntu",
        "os": "linux"
      },
      "linktest-01": {
        "version": "12.16.42",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-03",
        "check_in_date": "2017-03-06",
        "platform_family": "rhel",
        "platform_version": "7.1.1503",
        "platform": "centos",
        "os": "linux"
      }
    },
    "12.17.44": {
      "policyfile-01": {
        "version": "12.17.44",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-01",
        "check_in_date": "2017-01-03",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      }
    },
    "12.18.31": {
      "audit01": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "union",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-16",
        "platform_family": "rhel",
        "platform_version": "7.2.1511",
        "platform": "centos",
        "os": "linux"
      },
      "ThomasCate-PC": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-10",
        "platform_family": "windows",
        "platform_version": "6.1.7601",
        "platform": "windows",
        "os": "windows"
      },
      "automatebn01.e9.io": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-02",
        "platform_family": "rhel",
        "platform_version": "7.3.1611",
        "platform": "centos",
        "os": "linux"
      },
      "infra01.e9.io": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-02",
        "platform_family": "rhel",
        "platform_version": "7.3.1611",
        "platform": "centos",
        "os": "linux"
      },
      "windows01": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-10",
        "platform_family": "windows",
        "platform_version": "6.3.9600",
        "platform": "windows",
        "os": "windows"
      },
      "sink-aix-6-powerpc-thinker-71bce9": {
        "version": "12.18.31",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-09",
        "check_in_date": "2017-09-12",
        "platform_family": "aix",
        "platform_version": "6.1",
        "platform": "aix",
        "os": "aix"
      }
    },
    "12.21.12": {
      "automatebackend03.e9.io": {
        "version": "12.21.12",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-09",
        "check_in_date": "2017-09-28",
        "platform_family": "rhel",
        "platform_version": "7.3.1611",
        "platform": "centos",
        "os": "linux"
      },
      "automatebackend02.e9.io": {
        "version": "12.21.12",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-09",
        "check_in_date": "2017-09-28",
        "platform_family": "rhel",
        "platform_version": "7.3.1611",
        "platform": "centos",
        "os": "linux"
      },
      "automatebackend01.e9.io": {
        "version": "12.21.12",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-10",
        "check_in_date": "2017-10-03",
        "platform_family": "rhel",
        "platform_version": "7.3.1611",
        "platform": "centos",
        "os": "linux"
      }
    },
    "12.5.1": {
      "linux-dev": {
        "version": "12.5.1",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2015-11",
        "check_in_date": "2015-11-19",
        "platform_family": "debian",
        "platform_version": "14.04",
        "platform": "ubuntu",
        "os": "linux"
      }
    },
    "12.6.0": {
      "log-forward-01": {
        "version": "12.6.0",
        "org": "e9",
        "environment": "production",
        "check_in_month": "2017-11",
        "check_in_date": "2017-11-14",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      },
      "lesson-01": {
        "version": "12.6.0",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-02",
        "check_in_date": "2016-02-07",
        "platform_family": "rhel",
        "platform_version": "7.1.1503",
        "platform": "centos",
        "os": "linux"
      },
      "idlebox-01": {
        "version": "12.6.0",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-10",
        "check_in_date": "2016-10-02",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      },
      "conflict01": {
        "version": "12.6.0",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-02",
        "check_in_date": "2017-02-09",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      },
      "kibana-01": {
        "version": "12.6.0",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-09",
        "check_in_date": "2017-09-26",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      }
    },
    "12.7.2": {
      "supermarket.e9.io": {
        "version": "12.7.2",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-02",
        "check_in_date": "2016-02-24",
        "platform_family": "debian",
        "platform_version": "14.04",
        "platform": "ubuntu",
        "os": "linux"
      },
      "sosayweall-01": {
        "version": "12.7.2",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-09",
        "check_in_date": "2016-09-28",
        "platform_family": "debian",
        "platform_version": "14.04",
        "platform": "ubuntu",
        "os": "linux"
      }
    },
    "12.9.38": {
      "centos7": {
        "version": "12.9.38",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2016-04",
        "check_in_date": "2016-04-28",
        "platform_family": "rhel",
        "platform_version": "7.1.1503",
        "platform": "centos",
        "os": "linux"
      }
    },
    "13.2.20": {
      "blackpi.e9.io": {
        "version": "13.2.20",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2018-08",
        "check_in_date": "2018-08-28",
        "platform_family": "debian",
        "platform_version": "8.0",
        "platform": "raspbian",
        "os": "linux"
      },
      "bluepi.e9.io": {
        "version": "13.2.20",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2018-08",
        "check_in_date": "2018-08-28",
        "platform_family": "debian",
        "platform_version": "8.0",
        "platform": "raspbian",
        "os": "linux"
      },
      "whitepi.e9.io": {
        "version": "13.2.20",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-05",
        "platform_family": "debian",
        "platform_version": "8.0",
        "platform": "raspbian",
        "os": "linux"
      },
      "brownpi.e9.io": {
        "version": "13.2.20",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-08",
        "platform_family": "debian",
        "platform_version": "8.0",
        "platform": "raspbian",
        "os": "linux"
      },
      "windows_laptop": {
        "version": "13.2.20",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-08",
        "platform_family": "windows",
        "platform_version": "10.0.10240",
        "platform": "windows",
        "os": "windows"
      }
    },
    "13.3.44": {
      "es-node-01": {
        "version": "13.3.44",
        "org": "e9",
        "environment": "production",
        "check_in_month": "2017-08",
        "check_in_date": "2017-08-26",
        "platform_family": "debian",
        "platform_version": "8.2",
        "platform": "debian",
        "os": "linux"
      }
    },
    "14.0.202": {
      "gem_test": {
        "version": "14.0.202",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2018-04",
        "check_in_date": "2018-04-17",
        "platform_family": "debian",
        "platform_version": "16.04",
        "platform": "ubuntu",
        "os": "linux"
      }
    },
    "14.5.33": {
      "a2.e9.io": {
        "version": "14.5.33",
        "org": "e9",
        "environment": "_default",
        "check_in_month": "2018-10",
        "check_in_date": "2018-10-02",
        "platform_family": "rhel",
        "platform_version": "7.5.1804",
        "platform": "centos",
        "os": "linux"
      }
    }
  },
  "metadata": {
    "total_nodes": 32,
    "unprocessable_nodes": 0
  }
}

chef_server_nodes_report.rb

#!/opt/opscode/embedded/bin/ruby
# Chef Server Report:  Script to list all nodes in all orgs via direct DB query
# Run me on your chef server, as root
# Requires the chef_fixie gem to be installed

GROUPINGS = %w[version org environment check_in_month check_in_date platform_family platform_version platform os].freeze

require 'json'
require 'date'
require 'zlib'
require 'stringio'
require 'optparse'
require 'chef'

@skipped_nodes = 0

begin
  require 'chef_fixie'
rescue LoadError
  puts 'Please install the chef_fixie gem before proceeding'
  puts '  /opt/opscode/embedded/bin/gem install chef_fixie'
end

# argparse
@options = {}
OptionParser.new do |opts|
  opts.banner = 'Usage: chef_server_nodes_report.rb [options]'
  opts.on('-g TYPE', '--group-by TYPE', "Group results by: #{GROUPINGS}") do |g|
    @options[:group_by] = g
  end
end.parse!
@options[:group_by] = 'version' if @options[:group_by].nil?

def gunzip(s)
  reader = Zlib::GzipReader.new(StringIO.new(s))
  reader.read
end

def orgs
  ChefFixie::Sql::Orgs.new
end

def guid_to_name(guid)
  orgs.by_id(guid).list.first
rescue
  guid
end

def sql
  ChefFixie::Sql.default_connection
end

def extract_node_data(serialized_object)
  n_obj = JSON.parse(gunzip(serialized_object))
  n_obj['automatic']['chef_packages']['chef']['version']

  { version: n_obj['automatic']['chef_packages']['chef']['version'],
    platform_version: n_obj['automatic']['platform_version'],
    platform_family: n_obj['automatic']['platform_family'],
    platform: n_obj['automatic']['platform'],
    os: n_obj['automatic']['os'],
  }
rescue
  @skipped_nodes += 1
  '0.0.0'
end

def grouped_report
  # auto-vivified hashes ftw
  # https://github.com/chef/chef/blob/master/lib/chef/node/attribute_collections.rb#L122
  chef_versions = Chef::Node::VividMash.new

  sql.select(:name, :org_id, :updated_at, :environment, :serialized_object)
     .from(:nodes).each do |n|
    n[:org] = guid_to_name(n[:org_id])
    n[:check_in_month] = DateTime.parse(n[:updated_at].to_s).strftime('%Y-%m')
    n[:check_in_date] = DateTime.parse(n[:updated_at].to_s).strftime('%Y-%m-%d')

    node_data = extract_node_data(n[:serialized_object])
    node_data.keys.each { |k| n[k] = node_data[k] }

    n[:pkey] = n[@options[:group_by].to_sym]

    GROUPINGS.each do |grp|
      chef_versions[n[:pkey]][n[:name]][grp] = n[grp.to_sym]
    end
  end
  chef_versions.sort.to_h
end

def metadata_report
  node_count = sql.select(:name).from(:nodes).count
  {
    total_nodes: node_count,
    unprocessable_nodes: @skipped_nodes
  }
end

# main
ChefFixie.load_config
return_data = {
  @options[:group_by] => grouped_report,
  'metadata' => metadata_report
}

File.open("chef_nodes_report_by_#{@options[:group_by]}.json", 'w') do |file|
  file.write(JSON.pretty_generate(return_data))
end

puts JSON.pretty_generate(return_data)