Useful Insights NRQL queries library

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

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

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

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')

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

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

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

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

And please keep it comin’

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

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

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.

`

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

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”

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

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

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.

@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.

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

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/