NRQL syntax, clauses, and functions

NRQL is a query language you can use to query the New Relic database. This document explains NRQL syntax, clauses, components, and functions.

Syntax

This document is a reference for the functions and clauses used in a NRQL query. Other resources for understanding NRQL:

Query components

Every NRQL query will begin with a SELECT statement or a FROM clause. All other clauses are optional. The clause definitions below also contain example NRQL queries.

SELECT attribute ...
SELECT function(attribute) ...

The SELECT specifies what portion of a data type you want to query by specifying an attribute or a function. It's followed by one or more arguments separated by commas. In each argument you can:

  • Get the values of all available attributes by using * as a wildcard. For example: SELECT * from Transaction.
  • 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.

You can also use SELECT with basic math functions.

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
SELECT ...
  FROM data type
  ...

Use the FROM clause to specify the data type you wish to query. You can start your query with FROM or with SELECT. You can merge values for the same attributes across multiple data types in a comma separated list.

Query one data type

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

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

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
SHOW EVENT TYPES...

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

In this context, "event types" refers to the data types you can access with a NRQL query.

Data types in the last day

This query will return all the data types present over the past day:

SHOW EVENT TYPES SINCE 1 day ago

Use the WHERE clause to filter results. NRQL returns the results that fulfill the condition(s) you specify in the clause.

SELECT function(attribute) ...
  WHERE attribute [operator 'value' | IN ('value' [, 'value]) | IS [NOT] NULL ]
  [AND|OR ...]
  ...
  • If you specify more than one condition, separate the conditions by the operators AND or OR.
  • If you want to simulate a SQL join, use custom attributes in a WHERE or FACET clause.
Operators that the WHERE clause accepts Description
=, !=, <, <=, >, >=

NRQL accepts standard comparison operators.

Example: state = 'WA'

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 yields better performance than stringing together multiple WHERE clauses.

Example: animalType IN ('cat', 'dog', 'fish')

NOT IN

Determines if the string value of an attribute is not in a specified set. Using this method yields 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 you are matching 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.

RLIKE

Determines if an attribute contains a specified Regex sub-string. Uses RE2 syntax.

Examples:

appName RLIKE 'z.*|q.*''

  • z-app
  • q-app

hostname RLIKE 'ip-10-351-[0-2]?[0-9]-.*'

  • ip-10-351-19-237
  • ip-10-351-2-41
  • ip-10-351-24-238
  • ip-10-351-14-15

Note:

  • Slashes must be escaped in the Regex pattern. For example, \d must be \\d.

  • Regex defaults to full-string matching, therefore ^ and $ are implicit and you do not need to add them.
  • If the Regex pattern contains a capture group, the group will be ignored. That is, the group will not be captured for use later in the query.
NOT RLIKE

Determines if an attribute does not contain a specified Regex sub-string. Uses RE2 syntax.

Example query with three 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
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 chart.

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 separate and group your results by attribute values. For example, you could FACET your PageView data by deviceType to figure out what percentage of your traffic comes from mobile, tablet, and desktop devices.

Use the LIMIT clause to specify how many facets appear (default is 10). For more complex grouping, use FACET CASES. FACET clauses support up to five attributes, separated by commas.

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, a subset of facet values is selected and sorted 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.

For more on faceting on multiple attributes, with some real-world examples, see this New Relic blog post.

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.

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.

Use FACET ... AS to name facets using the AS keyword in queries. This clause is helpful for adding clearer or simplified names for facets in your results. It can also be used to rename facets in nested aggregation queries.

FACET ... AS queries will change the facet names in results (when they appear as headers in tables, for example), but not the actual facet names themselves.

FROM Transaction SELECT count(*) FACET response.headers.contentType AS 'content type'
SELECT ...
  FACET CASES (
    WHERE attribute operator value, WHERE attribute operator value, ...
    )
  ...

Use FACET CASES to break out your data by more complex conditions than possible with FACET. Separate multiple conditions with a comma ,. For example, you could query your PageView data and FACET CASES into categories like less than 1 second, from 1 to 10 seconds, and greater than 10 seconds. You can combine multiple attributes within your cases, and label the cases with the AS selector. Data points will be added to at most one facet case, the first facet case that they match.

You may also use a time function with your attribute.

Basic usage with WHERE
SELECT count(*) FROM PageView FACET CASES (WHERE duration < 1, WHERE duration > 1 and duration < 10, WHERE duration > 10)
Group based on multiple attributes

This example groups results into one bucket where the transaction name contains login, and another where the URL contains login and a custom attribute indicates that the user was a paid user:

SELECT count(*) FROM Transaction FACET CASES (WHERE name LIKE '%login%', WHERE name LIKE '%feature%' AND customer_type='Paid')
Label groups with AS

This example uses the AS selector to give your results a human-readable name:

SELECT count(*) FROM Transaction FACET CASES (WHERE name LIKE '%login%' AS 'Total Logins', WHERE name LIKE '%feature%' AND customer_type='Paid' AS 'Feature Visits from Paid Users')

In NRQL, the default is for the first aggregation in the SELECT clause to guide the selection of facets in a query. FACET ... ORDER BY allows you to override this default behavior by adding an aggregate function with the ORDER BY modifier to specify how facets are selected. Specifically, the clause will override the priority by which facets are chosen to be in the final result before being limited by the LIMIT clause. This clause can be used in querying but not for alerts or streaming.

This example shows how to use FACET ... ORDER BY to find the average durations of app transactions, showing the top 10 (default limit) highest durations by apps which have the highest response size. In this case, if FACET ... ORDER BY is not used, the query results will instead show the top 10 by highest durations, with response size being irrelevant to the app selection.

FROM Transaction SELECT average(duration) TIMESERIES FACET appName ORDER BY max(responseSize)

Because the operations are performed before the LIMIT clause is applied, FACET ... ORDER BY does not impact the sort of the final query results, which will be particularly noticeable in the results for non-timeseries queries.

The ORDER BY modifier in this case works differently than the ORDER BY clause. When parsing queries that follow the format FACET attribute1 ORDER BY attribute2, New Relic will read these as FACET ... ORDER BY queries, but only if ORDER BY appears immediately after FACET. Otherwise ORDER BY will be interpreted by New Relic as a clause.

SELECT ...
  LIMIT count
  ...

Use the LIMIT clause to control the maximum number of facet values returned by FACET queries or the maximum number of items returned by SELECT * queries. This clause takes a single integer value as an argument. If the LIMIT clause is not specified, or no value is provided, the limit defaults to 10 for FACET queries and 100 in the case of SELECT * queries.

The maximum allowed value for the LIMIT clause is 2,000.

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 ...
  LIMIT count OFFSET count
  ... 

Use the OFFSET clause with LIMIT to control the portion of rows returned by SELECT * or SELECT column queries. Like the LIMIT clause, OFFSET takes a single integer value as an argument. OFFSET sets the number of rows to be skipped before the selected rows of your query are returned. This is constrained by LIMIT.

OFFSET rows are skipped starting from the most recent record.

For example, the query SELECT interestingValue FROM Minute_Report LIMIT 5 OFFSET 1 returns the last 5 values from Minute_Report except for the most recent one.

The ORDER BY clause allows you to specify how you want to sort your query results in queries that select event attributes by row.

This query orders transactions by duration.

FROM Transaction SELECT appName, duration ORDER BY duration

The default sort order is ascending, but this can be changed by adding the ASC or DESC modifiers.

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. When using NRQL, you can set a UTC timestamp or relative time range. You can specify a timezone for the query but not for the results. NRQL results are based on your system time.

See Set time range on dashboards and charts 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. The returned results are based on your system time.

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

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

By default, query results are displayed in the timezone of the browser you're using.

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.

Available Time Zone Selections
Africa/Abidjan Africa/Addis_Ababa Africa/Algiers Africa/Blantyre
Africa/Cairo Africa/Windhoek America/Adak America/Anchorage
America/Araguaina America/Argentina/Buenos_Aires America/Belize America/Bogota
America/Campo_Grande America/Cancun America/Caracas America/Chicago
America/Chihuahua America/Dawson_Creek America/Denver America/Ensenada
America/Glace_Bay America/Godthab America/Goose_Bay America/Havana
America/La_Paz America/Los_Angeles America/Miquelon America/Montevideo
America/New_York America/Noronha America/Santiago America/Sao_Paulo
America/St_Johns Asia/Anadyr Asia/Bangkok Asia/Beirut
Asia/Damascus Asia/Dhaka Asia/Dubai Asia/Gaza
Asia/Hong_Kong Asia/Irkutsk Asia/Jerusalem Asia/Kabul
Asia/Katmandu Asia/Kolkata Asia/Krasnoyarsk Asia/Magadan
Asia/Novosibirsk Asia/Rangoon Asia/Seoul Asia/Tashkent
Asia/Tehran Asia/Tokyo Asia/Vladivostok Asia/Yakutsk
Asia/Yekaterinburg Asia/Yerevan Atlantic/Azores Atlantic/Cape_Verde
Atlantic/Stanley Australia/Adelaide Australia/Brisbane Australia/Darwin
Australia/Eucla Australia/Hobart Australia/Lord_Howe Australia/Perth
Chile/EasterIsland Etc/GMT+10 Etc/GMT+8 Etc/GMT-11
Etc/GMT-12 Europe/Amsterdam Europe/Belfast Europe/Belgrade
Europe/Brussels Europe/Dublin Europe/Lisbon Europe/London
Europe/Minsk Europe/Moscow Pacific/Auckland Pacific/Chatham
Pacific/Gambier Pacific/Kiritimati Pacific/Marquesas Pacific/Midway
Pacific/Norfolk Pacific/Tongatapu UTC

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

For information on querying metric data, see Query metrics.

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:00pm through 5:00pm against 1:00 through 3:00pm.

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

The SLIDE BY clause supports a feature known as sliding windows. With sliding windows,SLIDE BY data is gathered into "windows" of time that overlap with each other. These windows can help to smooth out line graphs with a lot of variation in cases where the rolling aggregate (such as a rolling mean) is more important than aggregates from narrow windows of time.

To use SLIDE BY, place it in a query after the TIMESERIES clause. For example, this query pulls data in 5-minute windows with a 1-minute SLIDE BY interval, meaning that each window lasts 5 minutes, but window 1 starts at 0 minutes, window 2 starts at 1 minute, window 3 starts at 2 minutes, and so on.

SELECT average(duration) FROM Transaction TIMESERIES 5 minutes SLIDE BY 1 minute

To learn more about how and when you can use SLIDE BY, see Create smoother charts with sliding windows.

Use SLIDE BY with MAX or AUTO interval

You can use sliding windows in combination with MAX or AUTO. However, MAX or AUTO may not be placed between TIMESERIES and SLIDE BY.

This query will automatically decide a SLIDE BY window interval.

SELECT average(duration) FROM Transaction TIMESERIES 5 minutes SLIDE BY AUTO
​​This query will set the SLIDE BY window to the maximum interval granularity.
SELECT average(duration) FROM Transaction TIMESERIES 5 minutes SLIDE BY MAX

The SLIDE BY value as determined by AUTO or MAX can produce a step interval greater than the window size, which can cause gaps and unexpected results.

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 charts, 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

TIMESERIES can be combined with arguments such as MAX, AUTO, and SLIDE BY to further tailor query results, as shown in the examples below.

For functions such as average( ) or percentile( ), a large aggregation window can have a significant smoothing effect on outliers. This is true whether or not the query makes use of sliding windows.

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 an 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
Use MAX interval

You can set TIMESERIES to MAX, which will automatically adjust your time window to the maximum number of intervals allowed for a given time period. This allows you to update your time windows without having to manually update your TIMESERIES buckets and ensures your time window is being split into the peak number of intervals allowed. The maximum number of TIMESERIES buckets that will be returned is 366.

For example, the following query creates 4-minute intervals, which is the ceiling for a daily chart.

SELECT average(duration) FROM Transaction since 1 day ago TIMESERIES MAX

You can use this clause with these data types:

  • Transaction
  • TransactionError
  • Custom events reported via APM agent APIs

The purpose of EXTRAPOLATE is to mathematically compensate for the effects of APM agent sampling of event data so that query results more closely represent the total activity in your system.

This clause will be useful when a New Relic APM agent reports so many events that it often passes its harvest cycle reporting limits. When that occurs, the agent begins to sample events.

When EXTRAPOLATE is used in a NRQL query that supports its use, the ratio between the reported events and the total events is used to extrapolate a close approximation of the total unsampled data. When it is used in a NRQL query that doesn’t support its use or that hasn’t used sampled data, it has no effect.

Note that EXTRAPOLATE is most useful for homogenous data (like throughput or error rate). It's not effective when attempting to extrapolate a count of distinct things (like uniqueCount() or uniques()).

This clause works only with NRQL queries that use one of the following aggregator functions:

  • apdex
  • average
  • count
  • histogram
  • sum
  • percentage (if function it takes as an argument supports EXTRAPOLATE)
  • rate (if function it takes as an argument supports EXTRAPOLATE)
  • stddev
Example of extrapolating throughput

A query that will show the extrapolated throughput of a service named interestingApplication.

SELECT count(*) FROM Transaction WHERE appName='interestingApplication' 
SINCE 60 minutes ago EXTRAPOLATE
Example of extrapolating throughput as a time series

A query that will show the extrapolated throughput of a service named interestingApplication by transaction name, displayed as a time series.

SELECT count(*) FROM Transaction WHERE appName='interestingApplication' 
SINCE 60 minutes ago FACET name TIMESERIES 1 minute EXTRAPOLATE

Query metric data

There are several ways to query metric data using NRQL:

For more on understanding metrics in New Relic, see Metric data types.

Aggregator functions

Use aggregator functions to filter and aggregate data in a NRQL query. Some helpful information about using aggregator functions:

Here are the available aggregator functions. The definitions below contain example NRQL queries.

Examples:

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

Use the aggregationendtime() function to return the time of the relevant aggregation. More specifically, for a given aggregate, the aggregationendtime() function provides the timestamp of the end of the time period of that aggregation. For example, in a timeseries query, for a data point that encompasses an hour’s worth of data, the function would return the timestamp of the end of that hour period.

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 or backendDuration. 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='ReallyImportantCustomer' 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
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 the average value for an attribute. It takes a single attribute name as an argument. If a value of the attribute is not numeric, it will be ignored when aggregating. If data matching the query's conditions is not found, or there are no numeric values returned by the query, it will return a value of null.

Use the buckets() function to aggregate data split up by a FACET clause into buckets based on ranges. You can bucket by any attribute that is stored as a numerical value in the New Relic database.

It takes three arguments:

  • Attribute name
  • Maximum value of the sample range. Any outliers will appear in the final bucket.
  • Total number of buckets

For more information and examples, see Split your data into buckets.

The bucketPercentile( ) function is the NRQL equivalent of the histogram_quantile function in Prometheus. It is intended to be used with dimensional metric data. Instead of the quantile, New Relic returns the percentile, which is the quantile * 100.

Use the bucketPercentile( ) function to calculate the quantile from the histogram data in a Prometheus format.

It takes the bucket name as an argument and reports percentiles along the bucket's boundaries:

SELECT bucketPercentile(duration_bucket) FROM Metric SINCE 1 day ago

Optionally, you can add percentile specifications as an argument:

SELECT bucketPercentile(duration_bucket, 50, 75, 90) FROM Metric SINCE 1 day ago

Because multiple metrics are used to make up Prometheus histogram data, you must query for specific Prometheus metrics in terms of the associated <basename>.

For example, to compute percentiles from a Prometheus histogram, with the <basename> prometheus_http_request_duration_seconds using NRQL, use bucketPercentile(prometheus_http_request_duration_seconds_bucket, 50). Note how _bucket is added to the end of the <basename> as a suffix.

See the Prometheus.io documentation for more information.

Use the cardinality( ) function to obtain the number of combinations of all the dimensions (attributes) on a metric.

It takes three arguments, all optional:

  • Metric name: if present, cardinality( ) only computes the metric specified.
  • Include: if present, the include list restricts the cardinality computation to those attributes.
  • Exclude: if present, the exclude list causes those attributes to be ignored in the cardinality computation.
SELECT cardinality(metric_name, include:{attribute_list}, exclude:{attribute_list})

Use the count( ) function to return a count of available records. It takes a single argument; either *, an attribute, or a constant value. Currently, it follows typical SQL behavior and counts all records that have values for its argument.

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

derivative() finds the rate of change for a given dataset. The rate of change is calculated using a least-squares regression to approximate the derivative.

The time interval is the period for which the rate of change is calculated. For example, derivative(attributeName, 1 minute) will return the rate of change per minute.

Use the dimensions( ) function to return all the dimensional values on a data type.

You can explicitly include or exclude specific attributes using the optional arguments:

  • Include: if present, the include list limits dimensions( ) to those attributes.
  • Exclude: if present, the dimensions( ) calculation ignores those attributes.

FROM Metric SELECT count(node_filesystem_size) TIMESERIES FACET dimensions()

When used with a FACET clause, dimensions( ) produces a unique timeseries for all facets available on the event type, similar to how Prometheus behaves with non-aggregated queries.

Use the earliest( ) function to return the earliest value for an attribute over the 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 earliest country per user agent from PageView

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

SELECT earliest(countryCode) FROM PageView FACET userAgentName
...WHERE eventType() = 'EventNameHere'...
...FACET eventType()...

Use the eventType() function in a FACET clause to break out results by the selected data type or in a WHERE clause to filter results to a specific data type. This is particularly useful for targeting specific data types with the filter() and percentage() functions.

In this context, "event type" refers to the types of data you can access with a NRQL query.

Use eventType() in filter() function

This query returns the percentage of total TransactionError results out of the total Transaction results. You can use the eventType() function to target specific types of data with the filter() function.

SELECT 100 * filter(count(*), where eventType() = 'TransactionError') / filter(count(*), where eventType() = 'Transaction') FROM Transaction, TransactionError WHERE appName = 'App.Prod' TIMESERIES 2 Minutes SINCE 6 hours ago
Use eventType() with FACET

This query displays a count of how many records each data type (Transaction and TransactionError) returns.

SELECT count(*) FROM Transaction, TransactionError FACET eventType() TIMESERIES

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 or TIMESERIES.

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
Use the filter( ) function to limit the results for one of the aggregator functions in your SELECT statement.

Use the funnel() function to generate a funnel chart. It takes an attribute as its first argument. You then specify steps as WHERE clauses (with optional AS clauses for labels) separated by commas.

For details and examples, see the funnels documentation.

Use the getField() function to extract a field from complex metrics.

It takes the following arguments:

Metric type Supported fields
summary count, total, max, min
gauge count, total, max, min, latest
distribution count, total, max, min
counter count
Examples:
SELECT max(getField(mySummary, count)) from Metric
SELECT sum(mySummary) from Metric where getField(mySummary, count) > 10

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 from PageView events

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
Prometheus histogram buckets

histogram( ) accepts Prometheus histogram buckets:

SELECT histogram(duration_bucket, 10, 20) FROM Metric SINCE 1 week ago
New Relic distribution metric

histogram( ) accepts Distribution metric as an input:

SELECT histogram(myDistributionMetric, 10, 20) FROM Metric SINCE 1 week ago

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

See all attributes for a data type

This query returns the attributes found 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 latestrate( ) function to return the rate of change of a value based on the last 2 data points. It takes the attribute in question as the first argument and the unit of time for the resulting rate as the second argument. The function returns a result in units of change in attribute/time interval.

This function can be useful to provide the most recent rate of change for an attribute in order to see leading-edge trends.

Get the most recent rate of change of PageView Duration

This query returns the rate of change of duration based on the last 2 data points. It will be returned in units of duration/second because of the 1 SECOND argument.

SELECT latestrate(duration, 1 SECOND) FROM PageView

Use the max( ) function to return the maximum recorded value of a numeric attribute over the time range specified. It takes a single attribute name as an argument. If a value of the attribute is not numeric, it will be ignored when aggregating. If data matching the query's conditions is not found, or there are no numeric values returned by the query, it will return a value of null.

Use the median( ) function to return an attribute's median, or 50th percentile. For more information about percentile queries, see percentile().

The median( ) query is only available when using the query builder.

Median query

This query will generate a line chart for the median value.

SELECT median(duration) FROM PageView TIMESERIES AUTO

Use the min( ) function to return the minimum recorded value of a numeric attribute over the time range specified. It takes a single attribute name as an argument. If a value of the attribute is not numeric, it will be ignored when aggregating. If data matching the query's conditions is not found, or there are no numeric values returned by the query, it will return a value of null.

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. The percentile() function enables percentiles to displays with up to three digits after the decimal point, providing greater precision. Percentile thresholds may be specified as decimal values, but be aware that for most data sets, percentiles closer than 0.1 from each other will not be resolved.

percentile.png
Percentile display examples

Use TIMESERIES to generate a line chart 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. To return only the 50th percentile value, the median, you can also use median().

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

predictLinear() is an extension of the derivative() function. It uses a similar method of least-squares linear regression to predict the future values for a dataset.

  • The time interval is how far the query will look into the future. For example, predictLinear(attributeName, 1 hour) is a linear prediction 1 hour into the future of the query time window.
  • Generally, predictLinear() is helpful for continuously growing values like disk space, or predictions on large trends.
  • Since predictLinear() is a linear regression, familiarity with the dataset being queried helps to ensure accurate long-term predictions.
  • Any dataset which grows exponentially, logarithmically, or by other nonlinear means will likely only be successful in very short-term predictions.
  • New Relic recommends against using predictLinear in TIMESERIES queries. This is because each bucket will be making an individual prediction based on its relative timeframe within the query, meaning that such queries will not show predictions from the end of the timeseries forward.

Use the rate( ) function to visualize the frequency or rate of a given query per time interval. 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 6 hours.

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

Use the round( ) function to return the rounded value of an attribute.

Optionally round( ) can take a second argument, to_nearest, to round the first argument to the closest multiple of the second one. to_nearest can be fractional.

SELECT round(n [, to_nearest])

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

Use the stdvar( ) function to return the standard variance for a numeric attribute over the time range specified.

It takes a single argument. 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.

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. When used along with the facet clause, a list of unique attribute values will be returned per each facet value.

The limit parameter is optional. When it is not provided, the default limit of 1,000 unique attribute values per facet is applied. You may specify a different limit value, up to a maximum of 10,000. The uniques( ) function will return the first set of unique attribute values discovered, until the limit is reached. Therefore, if you have 5,000 unique attribute values in your data set, and the limit is set to 1,000, the operator will return the first 1,000 unique values that it discovers, regardless of their frequency.

The maximum number of values that can be returned in a query result is the product of the uniques( ) limit times the facet limit. In the following query, the theoretical maximum number of values that can be returned is 5 million (5,000 x 1,000).

From Transaction SELECT uniques(host,5000) FACET appName LIMIT 1000

However, depending on the data set being queried, and the complexity of the query, memory protection limits may prevent a very large query from being executed.

Type conversion

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

You can convert a string with a numeric value or a boolean with a string value to their numeric and boolean types with these functions:

  • Use the numeric() function to convert a number with a string format to a numeric function. The function can be built into a query that uses math functions on query results or NRQL aggregator functions, such as average().
  • Use the boolean() function to convert a string value of "true" or "false" to the corresponding boolean value.

For more help

If you need more help, check out these support and learning resources: