Is it possible to run NRQL query on New Relic via Python?

I want to run a SQL query everyday on new relic. Is it possible to achieve it via python ?

Hi, @rigar: New Relic cannot be queried with SQL. Do you mean NRQL? If so, you may use the query API to execute NRQL queries. You may execute GET requests with Python.

1 Like

Yes i meant NRQL. I used ’ pip install nrql-simple’ and was able to successfully run it locally via python. However i want to run it via Azure Data factory for which i will be needing a jar to run it on Cluster via Pyspark. Is there any such jar available or is there any other way to accomplish this task ?

Below code i used to execute query via python locally.

from nrql.api import NRQL
import datetime

nrql = NRQL()
nrql.api_key = '****'
nrql.account_id = '****'

queryInsights = "SELECT Count(*) from Transaction where appName='ANC' and name like '%data%' "

reqvariable = nrql.query(queryInsights)

for k in reqvariable['results']:
    print(k['count'])

Unfortunately I can only help with the New Relic side of the equation; perhaps another member of the community can help with the Azure side.

I will say, though, that I don’t think you need a special jar or any custom packages. If Azure can run Python scripts, you can execute a NRQL query using the standard request package, and parse the result using the json package.

2 Likes

Putting this to help my fellow community members

I used a request module of Python

import requests

and define a function which take certain query parameters as input
like private DNS, Instance ID and no of days.

def nr_get_data(pdns, iid, days): dict_keys = apik() cdate, pdate = daterange(days) pri_dns = pdns inst_id = iid URL = "https://insights-api.newrelic.com/v1/accounts/<acc_no>/query" headers = {"Accept":"application/json", "X-Query-Key":"xxxxxxxxxxxxxxxxxx"}
Then I defined a query string with place holders
Query = "SELECT average(memoryTotalBytes) as TotalMem_GB, average(memoryUsedBytes) as UsedMem_GB, average(memoryFreeBytes) as FreeMem_GB from SystemSample FACET ec2InstanceId WHERE fullHostname = '<priv_dns>' or ec2InstanceId ='<inst_id>' or externalKey = '<inst_id>' since '<start_date>'"
These place folders will be replaced with actual values which I will get from different functions like apik, daterange and accno

Query = Query.replace("<priv_dns>",pri_dns)
Query = Query.replace("<inst_id>",inst_id)
Query = Query.replace("<start_date>",pdate)
Id = tmem = umem = fmem = 'NA'
for k,v in  dict_keys.items():
  acc_num = accno(k)
  URL1 = URL.replace('<acc_no>', str(acc_num))
  headers.update({"X-Query-Key":v})
  Payload = {"nrql": Query}
  r = requests.get(URL1, params=Payload, headers=headers)

  data = r.json()
  if 'facets' in data:
    if len(data['facets']) > 0:

      Id = data['facets'][0].get('name')
      tmem = data['totalResult']['results'][0].get('result')
      umem = data['totalResult']['results'][1].get('result')
      fmem = data['totalResult']['results'][2].get('result')
      acc = k
      k = ""
      return acc, Id, tmem, umem, fmem

return k, Id, tmem, umem, fmem
`

1 Like

Thanks for your feedback @Jidnesh.Bhogare!

Hi…i am trying something similar.I need to run a get query to get all records under a particular custom event located under Browse data(Browse data → events).The query i want to run my python code is SELECT * FROM ‘’. Can someone provide the exact URL and code snippet if posiible to do this in python.
I have tried the below code,but doesnt seem to help,it gives 404/401 error.I have replaced acc-id and key with my newrelic account specific values.

import requests

URL = "https://insights-api.newrelic.com/v1/accounts/acct-id/query"
headers = {"Accept":"application/json", "X-Query-Key":"xxxxxxxxxxxxxxxxxxxxxxxx"}
Query = "SELECT * FROM 'custom-eventtype-name' SINCE 3 HOURS AGO TIMESERIES"
Payload = {"nrql": Query}

r = requests.get(URL, params=Payload, headers=headers)
print(r.json)

Hi, @Sonal.Rakwal: This code works for me (replace the ACCOUNT_ID, QUERY_KEY, and NRQL values with your own):

import requests
import urllib.parse

ACCOUNT_ID = "12345678"
QUERY_KEY = "NRIQ-xxxxxxxxxxxxxxxxxxxxxxxx"
NRQL = "SELECT average(duration) FROM PageView"

headers = { "Accept": "application/json", "X-Query-Key": QUERY_KEY }
url = "https://insights-api.newrelic.com/v1/accounts/{}/query?nrql={}".format(ACCOUNT_ID, urllib.parse.quote_plus(NRQL))

response = requests.get(url, headers = headers)
print(response.json())

Note that SELECT * FROM... TIMESERIES is invalid: you cannot get a TIMESERIES of SELECT *.

1 Like

Thanks @philweber ,this works perfectly.