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

Useful Insights NRQL queries library

nrql
developer

#21

Here are queries to (almost) replicate the APM SLA report:

End-user tier

SELECT 
  count(*) / 1000 AS 'Page views thousands', 
  average(duration) AS 'Load time sec', 
  apdex(duration, 0.8)
FROM PageView 
WHERE appName = 'My Application' 
SINCE 1 day ago

Application server

SELECT 
  count(*) / 1000 AS 'Requests thousands', 
  average(duration) * 1000 AS 'Resp. time ms', 
  apdex(duration, 0.4), 
  percentage(count(*), WHERE apdexPerfZone = 'S') AS '% Satisfied', 
  percentage(count(*), WHERE apdexPerfZone = 'T') AS '% Tolerating', 
  percentage(count(*), WHERE apdexPerfZone = 'F') AS '% Frustrated' 
FROM Transaction 
WHERE appName = 'My Application' 
SINCE 1 day ago

Weekly Email of data we want with custom time frames
Export Insights data for historical tracking
#22

A post was split to a new topic: How to Query HTTPResopnse and Total Count in NRQL


Need help with the NR insights query
#23

Another version.
SELECT count(*) FROM Transaction WHERE (httpResponseCode like ‘4%’) or (httpResponseCode like ‘5%’ ) SINCE 3 hour ago TIMESERIES 1 minute FACET httpResponseCode


#24

Not sure, whether this is of interest, as it’s part of the default dashboards, but here is how to show ApM and Browser apdex in one diagram. If you have a link or filter on a dashboard, this also applies to this NRQL:

SELECT apdex(PageView.duration, 7.0) as ‘Browser Apdex’, apdex( Transaction.duration, 0.5 ) as ‘Backend Apdex’ from PageView, Transaction since 24 hours ago timeseries


#25

I was looking to create a query which will provide me details on session information such as the duration, number of pages viewed, etc. I thought I would detail my approach to building the query for others. So I started with a basic query

SELECT
  min(timestamp) as 'Start',
  max(timestamp) as 'End',
  ((MAX(timestamp) - MIN(timestamp)) / 1000) AS 'Length (seconds)',
  count(*) as 'Pages'
FROM
  PageView
FACET
  session

Then I thought I would add device and location information about the session. I then thought, I need some kind of function which will provide a unique value. I added uniques() on the query and although the query runs, I only get JSON output instead of a table, which is what I wanted to view.

SELECT
  min(timestamp) as 'Start',
  max(timestamp) as 'End',
  ((MAX(timestamp) - MIN(timestamp)) / 1000) AS 'Length (seconds)',
  count(*) as 'Pages',
  uniques(deviceType),
  uniques(userAgentName) as 'Browser',
  uniques(userAgentVersion) as 'Browser Version',
  uniques(userAgentOS) as 'OS',
  uniques(asnOrganization) as 'ISP',
  uniques(city),
  uniques(countryCode) as 'Country'
FROM
  PageView
FACET
  session

Then I thought, I only need one piece of data for those so I will try latest() and I got the results I expected

SELECT
  min(timestamp) as 'Start',
  max(timestamp) as 'End',
  ((MAX(timestamp) - MIN(timestamp)) / 1000) AS 'Length (seconds)',
  count(*) as 'Pages',
  latest(deviceType),
  latest(userAgentName) as 'Browser',
  latest(userAgentVersion) as 'Browser Version',
  latest(userAgentOS) as 'OS',
  latest(asnOrganization) as 'ISP',
  latest(city),
  latest(countryCode) as 'Country'
FROM
  PageView
FACET
  session

#26

Query to get monitor failure time in last month.

SELECT uniqueCount(location), ((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 / 60 ) AS ‘Total Failure time (hrs)’, min(timestamp) as ‘Start’,
max(timestamp) as ‘End’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName,locationLabel SINCE last month limit 100


#27

Why are you saying this a SQL Transaction throughput? You are not using any unit of time I reckon, Average SQL transaction time makes sense here, as far as I can perceive. Please acknowledge. :slight_smile:


#28

thanks for this! but can you explain how this would work if there were say 2 distinct 1 hour periods of monitor failure at different times during the month? Wouldn’t the max(timestamp) - min(timestamp) just get the difference between the latest and earliest events as if it was a single long failure? I’m probably misunderstanding how this works so any pointers would be appreciated.


#29

In this thread about having an alerts dashboard, I posted a NRQL query, for showing recent alert events that includes the duration. Including it here also:

SELECT max(timestamp) as 'Time',
       latest(current_state) as 'State',
       (max(timestamp)-min(timestamp))/60/1000 as 'Duration (min)',
       latest(severity) as 'Severity',
       latest(policy_name) as 'Policy',
       latest(details)
  FROM alert
  FACET incident_id
  SINCE 1 day ago
  LIMIT 99

It does show some odd data, though, if an incident spans from before the SINCE time as it won’t see the open event so won’t be able to calculate the total duration accurately… it’ll either be 0 if there’s only a single event or it might show a total duration between an acknowledge and closed, but not have the open event… but it mostly works.


#30

Thanks so much for adding to this thread @tstansell!


#31

this is gold, thanks alot