Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Try this tool: An Interactive Command Line Interface for querying NRQL data

nrql
python
command-line

#1

One of the recent projects I worked on was nrql-simple: a command line tool that lets you query your NRQL data via the command line. With the command line interface, the users enters nrql followed by the query statement, for example nrql "select * from Transaction". The script then make a request to the New Relic Query API endpoint and pretty prints the JSON response formatted like this:

I thought it would be interesting to extend this project and build a REPL with autocompletion as this would allow for a more interactive command line experience. Below you can see a demo of what I have built:

How it works

With a Python library called prompt_tookit, a lot of the groundwork has already been done, such as a PromptSession class that uses InMemoryHistory underneath that keeps track of user history meaning if the user presses the up-arrow, the user will see the previous entries made.

The prompt() method raises an KeyboardInterrupt exception when ctrl-c has been entered and an EOFError exception when ctrl-d has been entered. This is typical behaviour when cancelling commands and exiting in a REPL. The try/except block below handles these error conditions and ensures we go to the next iteration of the loop or quit the loop respectively:

session = PromptSession()
while True:
	try:
  	text = session.prompt('> ')
  except KeyboardInterrupt:
  	continue
  except EOFError:
  	break

One of the main features I wanted to include is auto-completion. With prompt_toolkit we can do this by creating a nrql_completer object from the WordCompleter class and defining a set of keywords for the auto-completion.

When defining the set of keywords, I added the reserved NRQL keywords to an array. When the program starts up, I also make two calls to the Query API: one to fetch the Event types and the other to fetch the keyset for each event type and merge them into the initial array created:

nrql = NRQL()
events = nrql.query("show event types since 1 week ago")
events = events['results'][0]['eventTypes']
keysets = nrql.query("select keyset() from %s since 1 week ago" % ", ".join([event for event in events]))
nrql_completer = WordCompleter(list(set(words + events + keysets['results'][0]['allKeys'])), ignore_case=True)

The nrql_completer instance can then be passed to the PromptSession class:

session = PromptSession(completer=nrql_completer)

For syntax highlighting, we can leverage the Pygments library for coloring the input. The closest syntax to NRQL would be SQL so I used the SqlLexer from the Pygments library for highlighting. In the PromptSession class, the lexer parameter allows us to set the syntax lexer:

session = PromptSession(lexer=PygmentsLexer(SqlLexer), completer=nrql_completer, style=style)

Now that I have the REPL, syntax highlighting and auto-completion, the next step is to pass the user input to the NRQL query method and output the data response pretty formatted:

req = nrql.query(text)
formatted_json = json.dumps(req, sort_keys=True, indent=4)
print(highlight(str(formatted_json).encode('utf-8'), 
								lexers.JsonLexer(), 
								formatters.TerminalFormatter()))

And that’s it. See below the complete source code:

from __future__ import unicode_literals

from prompt_toolkit import PromptSession
from prompt_toolkit.completion import WordCompleter
from prompt_toolkit.lexers import PygmentsLexer
from prompt_toolkit.styles import Style
from pygments.lexers.sql import SqlLexer

from nrql import NRQL

from pygments import highlight, lexers, formatters

import json

import sys

words = ['ago', 'and', 'as', 'auto', 'begin', 'begintime', 'compare', 'day', 'days', 'end', 				 'endtime', 'explain','facet', 'from', 'hour', 'hours', 'in', 'is', 'like', 'limit', 					'minute', 'minutes', 'month', 'months', 'not','null', 'offset', 'or', 'second', 					 'seconds', 'select', 'since', 'timeseries', 'until', 'week', 'weeks',
         'where','with']

style = Style.from_dict({
    'completion-menu.completion': 'bg:#008888 #ffffff',
    'completion-menu.completion.current': 'bg:#00aaaa #000000',
    'scrollbar.background': 'bg:#88aaaa',
    'scrollbar.button': 'bg:#222222',
})


def main():
    nrql = NRQL()
    events = nrql.query("show event types since 1 week ago")
    events = events['results'][0]['eventTypes']
    keysets = nrql.query("select keyset() from %s since 1 week ago" % ", ".join([event for event in events]))
    nrql_completer = WordCompleter(list(set(words + events + keysets['results'][0]['allKeys'])), ignore_case=True)
    session = PromptSession(lexer=PygmentsLexer(SqlLexer), completer=nrql_completer, style=style)

    print("Welcome to your data, let's get querying...")
    while True:
        try:
            text = session.prompt('> ')
        except KeyboardInterrupt:
            continue
        except EOFError:
            break
        else:
            if not (text and text.strip()):
                continue
            if text == 'quit()':
                sys.exit(0)
            req = nrql.query(text)
            formatted_json = json.dumps(req, sort_keys=True, indent=4)
            print(highlight(str(formatted_json).encode('utf-8'),
                            lexers.JsonLexer(),
                            formatters.TerminalFormatter()))


if __name__ == '__main__':
    main()

To try out this project, you can clone the repository here: https://github.com/AnthonyBloomer/nrql-cli

git clone https://github.com/AnthonyBloomer/nrql-cli.git
cd nrql-cli
pip install -r requirements.txt

Export your API key and Account ID as environment variables.

export NR_API_KEY='YOUR_API_KEY'
export NR_ACCOUNT_ID='YOUR_ACCOUNT_ID'

And then run:

python nrql-cli.py

I hope you find this useful! :slight_smile:

Support

Please note that this is offered for use as-is without warranty. You are free to use and modify as needed. It has been created for use with New Relic, but is not a supported product of New Relic.