Report N+1 via New Relic API

report.rb

require 'net/http'
require 'uri'
require 'json'
require 'time'
require 'csv'

class NrqlClient
  def initialize(api_key:, account_id:)
    @api_key = api_key
    @account_id = account_id
  end

  def query(nrql)
    uri = URI.parse('https://api.newrelic.com/graphql')
    http = Net::HTTP.new(uri.host, uri.port)
    http.use_ssl = true

    params = {
      query: <<~GQL,
        # query Q {
        query Q($accountId: Int!, $tableNrql: Nrql!) {
          actor {
            account(id: $accountId) {
              nrql(query: $tableNrql, timeout: 30) {
                results
              }
            }
          }
        }
      GQL
      variables: {
        accountId: account_id,
        tableNrql: nrql
      }
    }

    request = Net::HTTP::Post.new(uri.request_uri).tap do |req|
      req.body = params.to_json
    end

    request['Content-Type'] = 'application/json'
    request['API-Key'] = api_key
    response = http.request(request)
    json = JSON.parse(response.body)
    if json['errors']
      pp json
      raise
    end
    json['data']['actor']['account']['nrql']['results']
  end

  private

  attr_reader :api_key, :account_id
end

class QueryPerTxnReporter
  def initialize(api_key:, account_id:, since_time:, until_time:, hosts:, entity_guid:)
    @api_key = api_key
    @account_id = account_id
    @since_time = since_time
    @until_time = until_time
    @hosts = hosts
    @entity_guid = entity_guid
  end

  def run
    xs = client.query(<<~SQL)
      SELECT count(newrelic.timeslice.value) as count FROM Metric FACET metricTimesliceName
      WHERE (entity.guid = '#{entity_guid}')
      AND host IN (#{hosts.map {|h| "'#{h}'" }.join(",")})
      AND metricTimesliceName LIKE 'Controller/%'
      LIMIT 1000
      SINCE #{since_time.to_i * 1000} UNTIL #{until_time.to_i * 1000}
    SQL

    xs.each do |x|
      name = x['metricTimesliceName']
      ys = client.query(<<~SQL)
        SELECT count(newrelic.timeslice.value) as count FROM Metric FACET metricTimesliceName
        WHERE (entity.guid = '#{entity_guid}')
        AND host IN (#{hosts.map {|h| "'#{h}'" }.join(",")})
        AND scope = '#{name}'
        AND metricTimesliceName LIKE 'Datastore/%'
        LIMIT 1000
        SINCE #{since_time.to_i * 1000} UNTIL #{until_time.to_i * 1000}
      SQL

      ys.each do |y|
        avg_call_count_per_txn = y['count'] / x['count'].to_f

        puts [name, y['metricTimesliceName'], avg_call_count_per_txn].to_csv
      end
    end
  end

  private

  attr_reader :api_key, :account_id, :since_time, :until_time, :hosts, :entity_guid

  def client
    @client ||= NrqlClient.new(api_key: api_key, account_id: account_id)
  end
end

since_time = Time.parse('2020-01-01T00:00:00+0900')
until_time = Time.parse('2020-01-02T00:00:00+0900')
hosts = %w(
  server-a
  server-b
)
guid = 'ABCD12345'

QueryPerTxnReporter.new(api_key: ENV['NEW_RELIC_API_KEY'], account_id: 12345, since_time: since_time, until_time: until_time, hosts: hosts, entity_guid: guid).run