NRQL syntax, components, functions

The New Relic Query Language (NRQL) is query language similar to SQL that you use to make calls against the New Relic Insights Events database. This document explains the NRQL syntax, components, and functions.

Syntax

For basic NRQL syntax rules and examples, see Get started with NRQL syntax. To query multiple events or columns, see Query multiple events/columns.

For information on how to simulate a JOIN clause in NRQL, see Simulate SQL "join" functions with custom attributes.

For information on how to format NRQL queries when using the Insights API, see Generate API request.

Query components

Every NRQL query begins with a SELECT statement and a FROM clause. Use the FROM clause to specify the event type you wish to query. The SELECT specifies a portion of that dataset, by specifying an attribute or function. All other clauses are optional.

SELECT function(attribute) ...

The SELECT statement starts every NRQL query and is used to specify your results. It is followed by one or more arguments separated by commas. In each argument you can:

  • Get the values of all attributes of an event by using the asterisk as a wildcard *
  • Get values associated with a specified attribute or multiple attributes specified in a comma separated list
  • Get aggregated values from specified attributes by selecting an aggregator function
  • Label the results returned in each argument with the AS clause
Avg response time since last week

This query returns the average response time since last week.

SELECT average(duration) FROM PageView SINCE 1 week ago
SHOW EVENT TYPES...

SHOW EVENT TYPES will return a list of all the event types present in your account for a given time range. It is used as the first clause in a query instead of SELECT.

Events types in the last day

This query will return all the event types from the past day:

SHOW EVENT TYPES SINCE 1 day ago
SELECT ...
  FROM event
  ...

Use the FROM clause to specify the event containing the attributes being queried. The FROM clause is required. You can merge values for the same attributes across multiple event types in a comma separated list.

Query one event

This query returns the count of all APM transactions over the last three days:

SELECT count(*) FROM Transaction SINCE 3 days ago
Query multiple events

This query returns the count of all APM transactions and Browser events over the last three days:

SELECT count(*) FROM Transaction, PageView SINCE 3 days ago
SELECT function(attribute) ...
  WHERE attribute [operator 'value' | IN ('value' [, 'value]) | IS [NOT] NULL ]
  [AND|OR ...]
  ...

Use the WHERE clause to specify a series of one or more conditions separated by the keywords AND or OR.

The conditions can be:

  • A comparison using standard operators (for example: state = 'WA')
  • A set match using IN or NOT IN (for example: animalType IN ('cat', 'dog', 'fish'))

To generate funnels, return multiple columns using a comma-separated list. Label the returned columns with the AS clause.

Query with 3 conditions

This query returns the browser response time for pages with checkout in the URL for Safari users in the United States and Canada over the past 24 hours.

SELECT histogram(duration, 50, 20) FROM PageView
WHERE countryCode IN ('CA', 'US') AND userAgentName='Safari' AND pageUrl LIKE '%checkout%'
SINCE 1 day ago

The WHERE clause accepts the following operators:

Operator Description
=, !=, <, <=, >, >=

NRQL accepts the standard comparision operator set for determining equality, inequality, and the direction of inequality.

AND

Used to define an intersection of two conditions.

OR

Used to define a union of two conditions.

IS NULL

Determines if an attribute has a null value.

IS NOT NULL

Determines if an attribute does not have a null value.

IN

Determines if the string value of an attribute is in a specified set. Using this method will yield better performance than stringing together multiple WHERE clauses.

NOT IN

Determines if the string value of an attribute is not in a specified set. Using this method will yield better performance than stringing together multiple WHERE clauses.

Values must be in parentheses, separated by commas. For example:

SELECT * FROM PageView WHERE countryCode NOT IN ('CA', 'WA')
LIKE

Determines if an attribute contains a specified sub-string.

The string argument for the LIKE operator accepts the percent sign (%) as a wildcard anywhere in the string. If the substring does not begin or end the string being matched against, the wildcard must begin or end the string.

Examples:

userAgentName LIKE 'IE%'

  • IE
  • IE Mobile

userAgentName LIKE 'o%a%'

  • Opera
  • Opera Mini

userAgentName LIKE 'o%a'

  • Opera

userAgentName LIKE '%o%a%'

  • Opera
  • Opera Mini
  • Mozilla Gecko
NOT LIKE

Determines if an attribute does not contain a specified sub-string.

SELECT ...
  AS 'label'
  ...

Use the AS clause to label an attribute, aggregator, step in a funnel, or the result of a math function with a string delimited by single quotes. The label is used in the resulting widget.

Query using math function and AS

This query returns the number of page views per session:

SELECT count(*)/uniqueCount(session) AS 'Pageviews per Session'
  FROM PageView
Query using funnel and AS

This query returns a count of people who have visited both the main page and the careers page of a site over the past week:

SELECT funnel(SESSION,
    WHERE name='Controller/about/main' AS 'Step 1',
    WHERE name = 'Controller/about/careers' AS 'Step 2')
    FROM PageView SINCE 1 week ago
SELECT ...
  FACET attribute
  ...

Use FACET to break out your data by any string attribute. Use the LIMIT clause to specify how many facets appear (default is 10).

The facets are sorted in descending order by the first field you provide in the SELECT clause. If you are faceting on attributes with more than 1,000 unique values, Insights picks a subset of facet values and sorts them according to the query type.

When selecting min(), max(), or count(), FACET uses those functions to determine how facets are picked and sorted. When selecting any other function, FACET uses the frequency of the attribute you are faceting on to determine how facets are picked and sorted.

Faceted query using count()

This query shows cities with the highest pageview counts. This query uses the total number of pageviews per city to determine how facets are picked and ordered.

SELECT count(*) FROM PageView FACET city
Faceted query using uniqueCount()

This query shows the cities that access the highest number of unique URLs. This query uses the total number of times a particular city appears in the results to determine how facets are picked and ordered.

SELECT uniqueCount(pageUrl) FROM PageView FACET city
Grouping results across time

Advanced segmentation and cohort analysis allow you to facet on bucket functions to more effectively break out your data in New Relic Insights.

Cohort analysis is a way to group results together based on timestamps. You can separate them into buckets that cover a specified range of dates and times.

SELECT ...
  LIMIT count
  ...

Use the LIMIT clause to control the number of values returned. It takes a single integer value as an argument. If no value is provided, the limit defaults to 10 (or 100 in the case of * queries). The maximum number of values is always 1000.

Query using LIMIT

This query shows the top 20 countries by session count and provides 95th percentile of response time for each country for Windows users only.

SELECT uniqueCount(session), percentile(duration, 95)
  FROM PageView WHERE userAgentOS = 'Windows'
  FACET countryCode LIMIT 20 SINCE YESTERDAY
SELECT ...
  SINCE [numerical units AGO | phrase]
  ...

The default value is 1 hour ago.

Use the SINCE clause to define the beginning of a time range for the returned data. You can specify a UTC timestamp or relative time range. You can specify a timezone for the query but not for the results. Insights always shows the results based on your system time.

See Set time range on Insights dashboards and widgets for detailed information and examples.

SELECT ...
  UNTIL integer units AGO
  ...

The default value is NOW. Only use UNTIL to specify an end point other than the default.

Use the UNTIL clause to define the end of a time range across which to return data. Once a time range has been specified, the data will be preserved and can be reviewed after the time range has ended. You can specify a UTC timestamp or relative time range. You can specify a time zone for the query but not for the results. Insights always shows the results based on your system time.

See Set time range on Insights dashboards and widgets for detailed information and examples.

SELECT ... WITH TIMEZONE (selected zone)
  ...

Use the WITH TIMEZONE clause to select a time zone for a date or time in the query that hasn't already had a time zone specified for it.

For example, the query clause since monday until tuesday with timezone 'America/New_York' will return data recorded from Monday at midnight, Eastern Standard Time, until midnight Tuesday, Eastern Standard Time.

The query suggestion dropdown tool displays your local timezone first. For example, if a user in California uses the WITH TIMEZONE clause, the suggested time zones will have America/Los Angeles at the top of the list.

Query results are displayed in the timezone of the browser you are using.

See Set time range on Insights dashboards and widgets for detailed information and examples.

SELECT ... (SINCE or UNTIL) (integer units) AGO
  COMPARE WITH (integer units) AGO
  ...

Use the COMPARE WITH clause to compare the values for two different time ranges.

COMPARE WITH requires a SINCE or UNTIL statement. The time specified by COMPARE WITH is relative to the time specified by SINCE or UNTIL. For example, SINCE 1 day ago COMPARE WITH 1 day ago compares yesterday with the day before.

The time range for theCOMPARE WITH value is always the same as that specified by SINCE or UNTIL. For example, SINCE 2 hours ago COMPARE WITH 4 hours ago might compare 3:00 P.M. through 5:00 P.M. against 1:00 through 3:00 P.M.

COMPARE WITH can be formatted as either a line chart or a billboard:

  • With TIMESERIES, COMPARE WITH creates a line chart with the comparison mapped over time.
  • Without TIMESERIES, COMPARE WITH generates a billboard with the current value and the percent change from the COMPARE WITH value.

Example: This query returns data as a line chart showing the 95th percentile for the past hour compared to the same range one week ago. First as a single value, then as a line chart.

SELECT percentile(duration) FROM PageView
  SINCE 1 week ago COMPARE WITH 1 week AGO

SELECT percentile(duration) FROM PageView
  SINCE 1 week ago COMPARE WITH 1 week AGO TIMESERIES AUTO
SELECT ...
  TIMESERIES integer units
  ...

Use the TIMESERIES clause to return data as a time series broken out by a specified period of time. Since TIMESERIES is used to trigger certain results widgets, there is no default value.

To indicate the time range, use integer units. For example:

  • TIMESERIES 1 minute
  • TIMESERIES 30 minutes
  • TIMESERIES 1 hour
  • TIMESERIES 30 seconds
Use a set interval

The value provided indicates the units used to break out the graph. For example, to present a one-day graph showing 30 minute increments:

SELECT ... SINCE 1 day AGO TIMESERIES 30 minutes
Use automatically set interval

TIMESERIES can also be set to AUTO, which will divide your graph into a reasonable number of divisions. For example, a daily chart will be divided into 30 minute intervals and a weekly chart will be divided into 6 hour intervals.

This query returns data as a line chart showing the 50th and 90th percentile of client-side transaction time for one week with a data point every 6 hours.

SELECT average(duration), percentile(duration, 50, 90)
  FROM PageView SINCE 1 week AGO TIMESERIES AUTO

For functions such as average( ) or percentile( ), a large interval can have a significant smoothing effect on outliers.

Aggregator functions

New Relic Insights does not support "coercion." This means that a float stored as a string is treated as a string and cannot be operated on by functions expecting float values.

function(attribute [, argument [, ...]])

Use aggregator functions to filter and aggregate data returned in a SELECT statement. The function takes as arguments an attribute name followed by zero or more values to modify how the results are returned.

The NRQL command line tool in New Relic Insights filters attributes for a function. It only shows attributes in the dropdown menu that are appropriate for that function. For example, userAgentName will not appear as an option for average().

Cohort analysis functions appear on the New Relic Insights Cohort analysis page. The cohort functions aggregate transactions into time segments.

Use the apdex function to return an Apdex score for a single transaction or for all your transactions. The attribute can be any attribute based on response time, such as duration, backendDuration, or memcacheDuration. The t: argument defines an Apdex T threshold in seconds.

The Apdex score returned by the apdex( ) function is based only on execution time. It does not account for APM errors. If a transaction includes an error but completes in Apdex T or less, that transaction will be rated satisfying by the apdex ( ) function.

Get Apdex for specific customers

If you have defined custom attributes, you can filter based on those attributes. For example, you could monitor the Apdex for a particularly important customer:

SELECT apdex(duration, t: 0.4) FROM Transaction
  WHERE customerName='MrBigshot' SINCE 1 day ago
Get Apdex for specific transaction

Use the name attribute to return a score for a specific transaction, or return an overall Apdex by omitting name. This query returns an Apdex score for the Controller/notes/index transaction over the last hour:

SELECT apdex(duration, t: 0.5) from Transaction
  WHERE name='Controller/notes/index' SINCE 1 hour ago
crop-apdex-function
Insights > Data Explorer: The apdex function returns an Apdex score that measures user satisfaction with your site. Arguments are a response time attribute and an Apdex T threshold in seconds.
Get overall Apdex for your app

This example query returns an overall Apdex for the application over the last three weeks:

SELECT apdex(duration, t: 0.08) FROM Transaction SINCE 3 week ago

Use the average( ) function to return an average value for an attribute across some facet. If no facet is provided, it will average all values for that attribute in the specified time range. If the attribute is not numeric, it will return a value of zero.

It takes a single argument. Arguments after the first will be ignored with no error reported. If the attribute is not numeric, it will return a value of zero.

Use the count( ) function to return a count of events. It takes a single argument. Currently it follows typical SQL behavior and only counts all values within a given event or facet. This makes count(*) the only meaningful call.

Since count(*) does not name a specific attribute, the results will be formatted in the default "humanize" format.

Use the filter( ) function to limit the results for one of the aggregator functions in your SELECT statement. You can use filter() in conjunction with FACET, TIMESERIES, or as a workaround for a multi-facet.

Analyze purchases that used offer codes

You could use filter() to compare the items bought in a set of Transactions for those using an offer code versus those who aren't:

screenshot insights filter
Insights > Data Explorer: Use the filter( ) function to limit the results for one of the aggregator functions in your SELECT statement.

Use the histogram( ) function to generate histograms. It takes three arguments:

  • Attribute name
  • Maximum value of the sample range
  • Total number of buckets
Histogram of response times

This query results in a histogram of response times ranging up to 10 seconds over 20 buckets.

SELECT histogram(duration, 10, 20) FROM PageView SINCE 1 week ago

Using keyset() will allow you to see all of the attributes for a given event type over a given time range. It takes no arguments. It returns a JSON structure containing groups of string-typed keys, numeric-typed keys, and all keys.

See all attributes for an event type

This query returns the attributes for PageView events from the last day:

SELECT keyset() FROM PageView SINCE 1 day ago

Use the latest( ) function to return the most recent value for an attribute over a specified time range.

It takes a single argument. Arguments after the first will be ignored.

If used in conjunction with a FACET it will return the most recent value for an attribute for each of the resulting facets.

Get most recent country per user agent from PageView

This query returns the most recent country code per each user agent from the PageView event.

SELECT latest(countryCode) FROM PageView FACET userAgentName

Use the max( ) function to return the maximum recorded value of a numeric attribute over the time range specified.

It takes a single argument. Arguments after the first will be ignored. If the attribute is not numeric, it will return a value of zero.

Use the min( ) function to return the minimum recorded value of a numeric attribute over the time range specified.

It takes a single argument. Arguments after the first will be ignored. If the attribute is not numeric, it will return a value of zero.

Use the percentage( ) function to return the percentage of a target data set that matches some condition.

The first argument requires an aggregator function against the desired attribute. Use exactly two arguments (arguments after the first two will be ignored). If the attribute is not numeric, this function returns a value of 100%.

Use the percentile( ) function to return an attribute's approximate value at a given percentile. It requires an attribute and can take any number of arguments representing percentile points.

  • Use TIMESERIES to generate a line chart with with percentiles mapped over time.
  • Omit TIMESERIES to generate a billboard and attribute sheet showing aggregate values for the percentiles.

If no percentiles are listed, the default is the 95th percentile.

Basic percentile query

This query will generate a line chart with lines for the 5th, 50th, and 95th percentile.

SELECT percentile(duration, 5, 50, 95) FROM PageView TIMESERIES AUTO

Use the rate( ) function to visualize the frequency or rate of a given query per time bucket. For example, you might want to know the number of pageviews per minute over an hour-long period or the count of unique sessions on your site per hour over a day-long period.

  • Use TIMESERIES to generate a line chart with rates mapped over time.
  • Omit TIMESERIES to generate a billboard showing a single rate value averaged over time.
Basic rate query

This query will generate a line chart showing the rate of throughput for APM Transactions per 10 minutes over the past 3 hours.

SELECT rate(count(*), 10 minute) FROM Transaction SINCE 6 hours ago 
TIMESERIES

Use the stddev() function to return one standard deviation for a numeric attribute over the time range specified.
It takes a single argument. Additional arguments after the first will be ignored. If the attribute is not numeric, it will return a value of zero.

Use the sum( ) function to return the sum recorded values of a numeric attribute over the time range specified.

It takes a single argument. Arguments after the first will be ignored. If the attribute is not numeric, it will return a value of zero.

Use the uniqueCount( ) function to return the number of unique values recorded for an attribute over the time range specified.

It takes a single argument. Arguments after the first will be ignored.

To optimize query performance, this function returns approximate results for queries that inspect more than 256 unique values.

Use the uniques( ) function to return a list of unique values recorded for an attribute over the time range specified.

It takes a single argument. Arguments after the first will be ignored.

To optimize query performance, this function returns approximate results for queries that inspect more than 1,000 unique values.

For more help

Additional documentation resources include:

For assistance with New Relic Insights, join us in the New Relic Online Technical Community. Ask and answer questions, and learn more about New Relic Insights from fellow Insights users.

If you need additional help, get support at support.newrelic.com.