To write good NRQL queries, it helps to understand how our various NRQL clauses and functions work. This doc contains definitions of NRQL clauses and functions, and gives examples of how to use them.
Looking for basic NRQL syntax rules? See How to use NRQL . For a tutorial, see Introductory NRQL tutorial .
If you're brand new to New Relic and want to see how easy using NRQL can be, you can get started with the platform for free and follow along with the tutorial. All you have to do is:
- If you haven't already, create your free New Relic account below to start monitoring your data today.
- Use our agents and integrations to automatically collect data from common frameworks and tools.
- Visit the quick launch guide for a recommended path on how to set up New Relic. You can start ingesting data to query, and be all set for using this or any of our tutorials.
Query components
As noted in our
basic NRQL syntax doc
, every NRQL query will contain a
SELECT
clause and a
FROM
clause, with all other clauses being optional. The clause definitions below also contain example NRQL queries.
Required clauses
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
ASclause .
You can also
use
SELECT
with basic math functions
.
This query returns the average response time since last week.
SELECT average(duration) FROM PageView SINCE 1 week agoSELECT ... 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.
This query returns the count of all APM transactions over the last seven days:
SELECT count(*) FROM Transaction SINCE 7 days agoThis 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
See
lookup()
.
Optional clauses
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. Note that
AS
clause labels in time series charts will not be displayed if a
FACET
clause is used.
This query returns the number of page views per session:
SELECT count(*)/uniqueCount(session) AS 'Pageviews per Session' FROM PageViewThis 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 agoSELECT ... (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 the
COMPARE 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 11:00am through 1:00pm.
COMPARE WITH
can be formatted as either a line chart or a billboard:
-
With
TIMESERIES,COMPARE WITHcreates a line chart with the comparison mapped over time. -
Without
TIMESERIES,COMPARE WITHgenerates a billboard with the current value and the percent change from theCOMPARE WITHvalue.
Example : This query returns data as a line chart showing the 95th percentile for the past week compared to the same range one week ago. First as a single value, then as a line chart.
SELECT percentile(duration, 95) FROM PageView SINCE 1 week ago COMPARE WITH 1 week AGO
SELECT percentile(duration, 95) FROM PageView SINCE 1 week ago COMPARE WITH 1 week AGO TIMESERIES AUTOImportant
For
FACET
queries using
COMPARE WITH
, the facets in the result are selected based on the time range specified using
SINCE
and
UNTIL
and not the prior time range being compared. The results of a
FACET
query for the prior time range alone may include a different set of facets.
You can use this clause with these data types:
-
Transaction -
TransactionError -
Custom events reported via agent APIs
The purpose of
EXTRAPOLATEis 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 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
EXTRAPOLATEis 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.Important
Note that
EXTRAPOLATEis most useful for homogenous data (like throughput or error rate). It's not effective when attempting to extrapolate a count of distinct things (likeuniqueCount()oruniques()).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 supportsEXTRAPOLATE) -
rate(if function it takes as an argument supportsEXTRAPOLATE) -
stddevA query that will show the extrapolated throughput of a service named
interestingApplication.SELECT count(*) FROM Transaction WHERE appName='interestingApplication' SINCE 60 minutes ago EXTRAPOLATEA query that will show the extrapolated throughput of a service named
interestingApplicationby transaction name, displayed as a time series.SELECT count(*) FROM Transaction WHERE appName='interestingApplication'SINCE 60 minutes ago FACET name TIMESERIES 1 minute EXTRAPOLATE
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 5,000 unique values, a subset of facet values is selected and sorted according to the query type. Note that if a time series chart returns no data (NRQL matches no matching data, invalid NRQL, etc.) then it will only show a flat line with the label matching the first table in the
FROM
clause.
When selecting
min()
,
max()
,
percentile()
,
average()
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.
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 cityThis 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 cityAdvanced 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.
Important
When using functions to aggregate attribute values, it's important the attribute being aggregated in the first function of your query contains non-null values. Facets will only be chosen for rows which contain a non-null value for the attribute in the first function.
Example:
FROM Event SELECT average(attribute) FACET nameNames will only be chosen from rows where attribute is not null.
To check if the attribute you're using in your function contains non-null values, run the following query:
FROM Event SELECT attribute, name WHERE attribute IS NOT NULL
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, and you can use the
OR
operator to facet results that don't match any of your specified cases.
SELECT count(*) FROM PageView FACET CASES (WHERE duration < 1, WHERE duration >
1 and duration < 10, WHERE duration > 10)
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')
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')
This example uses the
OR
operator to facet results that didn't match any of your cases:
SELECT count(*) FROM Transaction FACET CASES (WHERE name LIKE '%login%', WHERE name LIKE '%feature%' AND customer_type='Paid') OR name
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)
Keep in mind that if you use the
FACET ... ORDER BY
clause, you can't change the sort order by adding the
ASC
and
DESC
modifiers. By default, this clause uses
DESC
.
Tip
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-time series queries.
Important
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.
Use the
JOIN
clause to combine data from one event type with the results of a subquery based on a common attribute or key.
FROM Event [INNER|LEFT] JOIN (SELECT... FROM...) ON [key =] key SELECT ...There are a few simple rules for subquery joins:
-
The
JOINclause must always follow immediately after theFROMclause. -
Prefixing a join type (
INNERorLEFT) is optional. When omitted, the join type defaults toINNER. -
Parenthesis containing a
subquery
must immediately follow
JOIN. -
The
ONclause must immediately follow the subquery.
|
Join types |
|
|---|---|
|
|
The result will only include values from the outer query that have a matching value in the results of the joined subquery. This is the default join type. |
|
|
The result will include events from the outer query that do not have a match from the joined subquery. |
|
Join
|
|
|---|---|
|
|
Defines the key values to compare in the subquery and the outer query. The only comparison operator allowed is equality.
|
|
|
This is an abbreviated syntax for when the key identifier is the same in both contexts. It is equivalent to
|
Restrictions and limitations to consider:
-
The joined subquery will continue to have a default
LIMITof 10, with a maximumLIMITof 5,000. Note that the outer query'sLIMITdoes not affect the inner query. -
The use of
TIMESERIESin the joined subquery is not supported. If your outer query usesTIMESERIES, keep in mind that the joined subquery will provide a single result for the full query timespan. - Like all subqueries, joined subqueries cannot be used in alert conditions.
- While SELECT * is supported in the parent query, it is not supported in the joined subquery.
- The cardinality of the join is limited to 1:100, meaning a single join key cannot map to more than one hundred rows in the subquery result.
For an in depth look at the JOIN clause, please see the NRQL subquery joins tutorial.
This query finds the count of events faceted by
browserTransactionName
from the
PageView
event type, and then by
currentUrl
from the
PageAction
event type. This joins
the two event types based on common
session
attribute values.
FROM PageViewJOIN (FROM PageAction SELECT count(*) FACET session, currentUrl) ON sessionSELECT count(*) FACET browserTransactionName, currentUrl
Faceted
INNER JOIN
example
This example queries the same data as the
faceted
INNER JOIN
example
, but as a
LEFT JOIN
query, the results include items
in the
PageView
table that do not have matching
session
values in the results of the
PageAction
subquery.
FROM PageViewLEFT JOIN (FROM PageAction SELECT count(*) FACET session, currentUrl) ON sessionSELECT count(*) FACET browserTransactionName, currentUrl
Faceted
LEFT JOIN
example
Here we are performing an unaggregated, row-wise subquery, with the outer
query finding the count of events faceted by
currentUrl
from the
PageAction
event type, and then by
browserTransactionNamed
from the
PageView
event type.
This joins the two event types based on common
session
attribute values.
Note that the
session
value
34d5ce6acf4c60be
has two
browserTransactionName
values from
the subquery's
PageView
event type, adding additional rows to the result.
FROM PageActionleft JOIN (FROM PageView SELECT session, browserTransactionName LIMIT MAX) ON sessionSELECT count(*) FACET session, currentUrl, browserTransactionName LIMIT MAX
Unaggregated
INNER JOIN
example
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 5,000. Queries can use the
LIMIT MAX
clause instead of a specific value, which automatically defaults to the current maximum value. You can use this if you always want to post the maximum number of results, even if it changes in the future. If you want your query's behavior to remain unchanged, specify an explicit value instead of using
LIMIT MAX
.
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 YESTERDAYSELECT
... 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 two specific transaction attributes 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.
This query orders all transaction attributes by duration in descending order.
FROM Transaction SELECT * ORDER BY duration DESCImportant
The
ORDER BY
clause does not apply to
FACET
queries. It should not be confused with the
FACET ... ORDER BY
clause, which guides facet selection. For more information, see
FACET ... ORDER BY
.
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
.
Important
In this context, "event types" refers to the data types you can access with a NRQL query.
This query will return all the data types present over the past day:
SHOW EVENT TYPES SINCE 1 day agoSELECT ... SINCE [numerical units AGO | phrase] ...The default value is 1 hour ago .
Use the
SINCE
clause to define the inclusive beginning of a time range for the returned data. You can specify a time zone for the query but not for the results. NRQL results are based on your system time.
When using NRQL, you can set a UTC timestamp, a relative time, or a DateTime string. See Specifying a time .
See also:
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
. Or, watch this short video (approx. 3:20 minutes).