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

#1

Hi All

We have all been there, spent a couple of hours working on a query to get a dashboard that your team/manager or product owner just has to have. How about some community spirit and helping our fellow Data Nerds to get a running start with some dashboards by posting some here?

Please ensure that you post a short description of the purpose of your query along with the NRQL.

This query will get the number of PageViews broken into Mobile, Desktop and Tablet:

SELECT 
percentage(count(*), WHERE deviceType = 'Mobile') AS 'Mobile', 
percentage(count(*), WHERE deviceType = 'Desktop') AS 'Desktop', 
percentage(count(*), WHERE deviceType = 'Tablet') AS 'Tablet' 
FROM PageView SINCE 1 week ago

This query will get the biggest resources used in the pages in your synthetic tests:

SELECT max(responseBodySize) FROM SyntheticRequest FACET URL

NRQL Join event types, add multiple columns
4/1/2016 Post of the Week! NRQL Library, Synthetics and Infographics!
11/6/2015 Post of the Week: Helping each other out. APIs, NRQL, Promises, and more!
Introductory/Beginner/Educate-Me question
Boss Bingo #DataLikeABoss
NRQL Query for SLA Report
Useful Insights Queries for before and after Release Deployment
NQL query for total monthly downtime based on web-checks
Love our Lurkers Week DAY 2: List of Legendary Posts
NRQL : SINCE 1 week ago, gives only the results of today not of entire week, can someone please help, Thanks
Customer Experience NRQL Suggestions for a Dashboard
Help. I would love to know how to start
Looking for a way to exclude maintenance windows from synthetic checks
Most widely used new relic metrics for performance/load test on front-end and backend
Most widely used new relic metrics for performance/load test on front-end and backend
View pages listed by number of hits received for an SPA
NRQL Query of APM HttpDispatcher MaxResponse Time Threshold Counts
Help Us Pick our New Badges
Learn New Relic, Earn Cool Stuff
Make the Most of the Community: Organizing Content
Community Digest Issue 15 - November 2015
Relic Solution: How to Create a Server Overview Dashboard in New Relic Insights
Query Results Bug / Custom Attributes
Reporting stats API
How to print uptime/downtime by Host in Insights page since begin of 2018
Sending Alerts data to Insights
Hi There, can anyone please guide me to get query for comparison of performance/load time of 2 different url/pages? Thanks!
How to query Average spent time of users in my website?
How to send an alert when the NRQL query response is below specified number
How to measure throughput and alert if it impacts latency
Self Healing Systems!
#2

#3

Thank you for starting this @stefan_garnham! I have pinned it and am looking forward to seeing this library grow!


#4

Taken from this post by @aaronsnyder on how to exclude days or times:

SELECT average(duration) 
FROM SyntheticCheck 
WHERE (
  hourOf(timestamp) NOT IN ('1:00','2:00','3:00','4:00') 
  AND weekdayOf(timestamp) = 'Sunday'
) 
OR weekdayOf(timestamp) NOT IN ('Sunday')
SINCE 1 week ago 
TIMESERIES 1 hour

Exclude a date range from a query
Looking for a way to exclude maintenance windows from synthetic checks
Different alerts for certain time periods
Exclude 2 saturdays
#5

Various forms of filtering as identified by @eadams , all of these queries return the same result!

SELECT count(*) FROM PageAction FACET appName IN ('App 1', 'App 2', 'App 4')

SELECT filter(count(*), appName = 'App 1'), filter(count(*), appName = 'App 2'), filter(count(*), appName = 'App 4'), FROM PageAction

SELECT count(*) FROM PageAction FACET appName WHERE appName IN ('App 1', 'App 2', 'App 4')

#6

Competition NRQL covering Wifi or non-Wifi and MySQL database transactions.


#7

These are great @stefan_garnham! Thanks for gathering these from around the community and sharing here!


#8

Use this to graph % of session timeouts, handled errors etc… Replace the %wildcard% string if your app serves a different URL.

SELECT percentage(count(*), WHERE pageUrl LIKE '%timeout%' ) FROM  PageView  since 3 hour ago TIMESERIES 1 minute COMPARE WITH 1 day ago

SELECT percentage(count(*), WHERE pageUrl like '%Error%' ) FROM  PageView SINCE 3 hour ago TIMESERIES 1 minute COMPARE WITH 1 day ago

If you use Synthetics, grab your uptime %s here:

SELECT percentage(count(result), WHERE result = 'SUCCESS') FROM SyntheticCheck since 12 months ago facet monthOf(timestamp)

Another Synthetics query, get actual counts on Failed/Successful tests for each monitor

SELECT filter(count(result), WHERE result = 'FAILED') as 'Failed', filter(count(result), WHERE result = 'SUCCESS') as 'Success' FROM SyntheticCheck since 1 months ago FACET monitorName LIMIT 1000

Top 10 Apps Page Speed Heat Map and Top 10 URLs Page Speed Heat Map
Amend the LIMIT parameter to show more. Target specific Apps if necessary.

SELECT histogram(duration,60,300 ) from PageView SINCE 1 months ago FACET appName limit 10

SELECT histogram(duration ,10,10) from PageView   SINCE 30 minutes ago FACET pageUrl limit 10

Have methods that throw errors? Count them by host using something like this:

SELECT count(*) FROM Transaction WHERE name like '%Error%' SINCE 1 DAY ago TIMESERIES 30 minutes FACET host

SLA report show multiple URL monitors
Monitoring availability of various systems
Uptime summary report
Feature Idea: Query Uptime Available in the API
#9

Awesome share @andrew_brockway! I love seeing the expertise coming out here and sharing it with others. Thank you!

And please keep it comin’ :slight_smile:


#10

Following a question on % Mobile Data or Wifi Connection I thought an example for the answer would make a suitable candidate for this library.

This sample will give you the unique sessions per deviceType grouped by Asn Organisation

SELECT 
filter(uniquecount(session), WHERE deviceType = 'Mobile') AS 'Mobile', 
filter(uniquecount(session), WHERE deviceType = 'Desktop') AS 'Desktop', 
filter(uniquecount(session), WHERE deviceType = 'Tablet') AS 'Tablet' 
FROM PageView WHERE appName='xxxxxxxxxx'
facet asnOrganization

Yearly Uptime Report
#11

Two quick views on HTTP 400 & 500 error frequency

Get overall % rate for last 3H compared with last wk

SELECT percentage( count(*), WHERE (response.status like ‘4%’) or (response.status like ‘5%’ )) FROM Transaction SINCE 3 hour ago TIMESERIES 1 minute COMPARE WITH 1 week ago

And split by HTTP response status

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


APM Transactions by Status Code per Day
#12

Data Base Analytic
found these useful for when diagnosing the cause of a sql infrastructure alert - while none of these NRQL queries are particularly unique, I do love how with a page filters you can build a really good database analytical pack.

build a dashboard to Graph Database Throughput, Response Times, and Errors and drill down with these facets

Response Time:
SELECT average(databaseDuration ) FROM Transaction WHERE databaseDuration >= 0 TIMESERIES 1 minute since 6 hours ago until 1 minute ago COMPARE WITH 1 week ago
Throughput:
SELECT count(*) FROM Transaction WHERE databaseDuration >= 0 TIMESERIES 1 minute SINCE 6 hours ago until 1 minute ago LIMIT 100 COMPARE WITH 1 week ago
Errors:
SELECT count(errorType ) FROM Transaction WHERE databaseDuration >= 0 TIMESERIES 1 minute SINCE 6 hours ago until 1 minute ago COMPARE WITH 1 week ago
Hosts
SELECT count(*) FROM Transaction WHERE databaseDuration >= 0 FACET host TIMESERIES 1 minute since 6 hours ago

Once you have these graphed you can facetthe dashboard from the following tables

App throughput
SELECT count(*), average(databaseDuration) FROM Transaction WHERE databaseDuration >= 0 FACET appName limit 100
SQL transaction throughput
SELECT count(*), average(databaseDuration) FROM Transaction WHERE databaseDuration >= 0 FACET name limit 100
Slowest Transactions
SELECT average(databaseDuration ) FROM Transaction where databaseDuration >= 0 FACET name
Database Transaction Errors
SELECT count(errorType ) FROM Transaction where databaseDuration >= 0 FACET name
Hosts making the database calls
SELECT count(*), average(databaseDuration) FROM Transaction WHERE databaseDuration >= 0 FACET host limit 100

Hope you find these useful - let me know if you can recommend any NRQL that might enhance the tool.

`


Creating a template data app
#13

Here’s how to find the length of a session from the Concurrent users post:

SELECT ((MAX(timestamp) - MIN(timestamp)) / 1000 / 60) AS 'Session Length (minutes)' 
FROM PageView SINCE 1 day ago FACET session LIMIT 100

#14

As requested by @hross Here is some nrql to create an uptime report for the past 12 months broken down by month. Useful when presenting to management

SELECT percentage(count(*), WHERE result =‘SUCCESS’) FROM SyntheticCheck WHERE type =‘SIMPLE’ since 12 months ago FACET monthOf(timestamp)

Obviously you would alter the "type = " to suit your own needs. This just queries our Synthetic ping results. For your scripted synthetics you might use “type = SCRIPT_BROWSER” and also perhaps modify the “since” to show only this year’s results to “since this year”


9/23/16 Post of the Week—Synthetics, Workarounds and NRQL Queries
9/30/16—Post of the Week! NRQL, Browser SPA and Workarounds!
#15

This query will give you all the event types that you have available in Insights:

SHOW EVENT TYPES

Once you have the event type, you can use this to get the attributes. In this example, from the Transaction event type

SELECT keyset() FROM Transaction


#16

Someone has tried to create a NRQL to alert after 3 consecutive errors (any kind of error) for a Synthetic Monitor?


#17

Hi @victor.silva - Synthetics tests automatically re-try 3 times prior to failing. You can add the synthetic check to an alert policy and the alert will then fire on the final failure.


#18

@stefan_garnham Yeah, I know. But on NR you only see one event or one error because it is part of the same iteration (even if internally it tried 3 times), I would like to send an alert just after 3 or 5 consecutive errors on different iterations using NRQL.


#19

A useful example of how to use the filter function in NRQL.


#20

For those looking for Infrastructure based NRQL queries, @philweber has detailed some information in this blog post.

https://blog.newrelic.com/2017/08/10/server-overview-dashboard/