Useful Insights NRQL queries library

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
7 Likes

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:

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
5 Likes

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

3 Likes

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.

`

7 Likes

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
3 Likes

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”

5 Likes

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

15 Likes

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.

1 Like

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

1 Like

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

5 Likes

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/

2 Likes

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
13 Likes

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

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

3 Likes

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

4 Likes

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
6 Likes

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

3 Likes

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: