NRQL Query for SLA Report

Build on New Relic Data Queries & Synthetic Scripts
, , ,
#1

Does anyone have a NRQL query that would provide the same result as the daily SLA report for a specific App? I’m looking to pull this data into Domo using their New Relic connector.

How to make my own report easily?
Capturing and Getting API average response in SLA report
#2

Hi, @j.lazerus: I don’t think it’s possible to recreate the report with a single query, but these get pretty close. (You will need to replace 0.8 and 0.4 in the apdex functions with the actual Apdex threshold values for your application.)

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
3 Likes
NRQL query for weekly Synthetics SLA reports
Generating Monthly SLA Report for a Specific Synthetic Monitor
How to get weekly performance report for applications running in sub accounts?
#3

Great NRQL @philweber. Could you add it to the NQL library please?

1 Like
#4

I needed to add nr.apdexPerfZone instead of apdexPerfZone

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

I noticed that if I add each nr.apdexPerfZone I don’t get 100%. Is it normal ?

1 Like
#5

I found a better alternative where numbers add up to 100%:

SELECT 
  count(*) / 1000 AS 'Requests thousands', 
  average(duration) * 1000 AS 'Resp. time ms', 
  apdex(duration, 0.5),
  percentage(count(*), WHERE nr.apdexPerfZone NOT IN ('T', 'F')) AS '% Satisfied',
  percentage(count(*), WHERE nr.apdexPerfZone = 'T') AS '% Tolerating', 
  percentage(count(*), WHERE nr.apdexPerfZone = 'F') AS '% Frustrated' 
FROM Transaction 
WHERE appName = 'My Application' 
SINCE 1 month ago
1 Like
#6

Hi, @mpalma: There are likely some events where nr.apdexPerfZone is null. They would be excluded from your first query but included in the second. You might try changing the first query to:

SELECT 
  count(*) / 1000 AS 'Requests thousands', 
  average(duration) * 1000 AS 'Resp. time ms', 
  apdex(duration, 0.5), 
  percentage(count(*), WHERE nr.apdexPerfZone = 'S') AS '% Satisfied',
  percentage(count(*), WHERE nr.apdexPerfZone = 'T') AS '% Tolerating', 
  percentage(count(*), WHERE nr.apdexPerfZone = 'F') AS '% Frustrated' 
FROM Transaction 
WHERE appName = 'My Application' 
  AND nr.apdexPerfZone IS NOT NULL
SINCE 1 month ago
1 Like
#7