--- title: NRQL reference source: https://docs.newrelic.com/docs/nrql/nrql-syntax-clauses-functions --- To write good [NRQL](https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/introduction-nrql-new-relics-query-language) 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](https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/introduction-nrql-how-nrql-works). For a tutorial, see [Introductory NRQL tutorial](https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/introduction-nrql-tutorial). ## Query components [#clauses] As noted in our [basic NRQL syntax doc](https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/get-started/introduction-nrql-how-nrql-works/#syntax), 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. ### DELETE keyword [#delete] The `DELETE` keyword in New Relic Query Language (NRQL) is used to remove specific data and attributes within Pipeline Control. This helps manage the data that is ingested into the New Relic Database (NRDB). The `DELETE` keyword is applied in NRQL queries to specify which data or attributes should be removed. Here are some examples: Here're some examples: - **Deleting specific metrics:** ```sql DELETE FROM Metric WHERE metricName = 'newrelic.goldenmetrics.infra.kubernetes_pod.podScheduled' ``` - Removes metrics with the specified name. - **Deleting specific spans:** ```sql DELETE FROM Span WHERE appName = 'My Application' ``` - Removes spans associated with the specified application. - **Deleting specific agent updates:** ```sql DELETE FROM AgentUpdate WHERE a = 'b' ``` - Removes agent updates where the attribute `a` equals `b`. ### Required clauses [#required] **Required: `SELECT` statement** ````sql SELECT attribute ... ``` ```sql SELECT function(attribute) ... ``` The `SELECT` specifies what portion of a data type you want to query by specifying an [attribute](/docs/using-new-relic/welcome-new-relic/get-started/glossary/#attribute) or a [function](#functions). 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](#functions). * Label the results returned in each argument with [the `AS` clause](#sel-as). You can also [use `SELECT` with basic math functions](/docs/query-data/nrql-new-relic-query-language/getting-started/nrql-math-using-select). This query returns the average response time since last week. ```sql SELECT average(duration) FROM PageView SINCE 1 week ago ``` You can include both the `*` wildcard and individual attributes, [functions](#non-aggregator-functions), math expressions, and [NRQL variables](#with-as-nrql-var) in the same `SELECT` statement. `*` may appear at the beginning or end of the `SELECT` list, and the additional columns selected will appear at the beginning or end of the results table, respectively: ```sql SELECT *, attribute, function(attribute), attribute1 + attribute2 FROM ... ``` ```sql WITH attribute1 + attribute2 AS attrSum SELECT attrSum, attribute, function(attribute), * FROM ... ``` SELECT * with additional columns} > This query returns all available PageView attributes with additional columns at the beginning. ```sql WITH concat('(', asnLatitude, ', ', asnLongitude, ')') AS coordinates SELECT coordinates, city, connectionSetupDuration + pageRenderingDuration AS partialDuration, * FROM PageView ``` select star with additional columns
SELECT * with additional columns example
Only [non-aggregator functions](#non-aggregator-functions) are supported in the `SELECT` list along with `*`. ```` **Required: `FROM` clause** ````sql SELECT ... FROM data type ... ``` Use the `FROM` clause to specify the [data type](/docs/query-data/nrql-new-relic-query-language/getting-started/introduction-nrql#what-you-can-query) you wish to query. You can start your query with `FROM` or with [`SELECT`](#state-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](/docs/insights/new-relic-insights/decorating-events/insights-attributes#transaction-defaults) over the last seven days: ```sql SELECT count(*) FROM Transaction SINCE 7 days ago ``` This query returns the count of all [APM transactions](/docs/insights/new-relic-insights/decorating-events/insights-attributes#transaction-defaults) and [browser events](/docs/insights/new-relic-insights/decorating-events/browser-default-attributes-insights#browser-attributes-table) over the last three days: ```sql SELECT count(*) FROM Transaction, PageView SINCE 3 days ago ``` See [`lookup()`](#func-lookup). ```` ### Optional clauses [#optional] **`AS` clause** ````sql 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. Query using math function and AS} > This query returns the number of page views per session: ```sql 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: ```sql 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 ``` ```` **`COMPARE WITH` clause** ````sql 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 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 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 week compared to the same range one week ago. First as a single value, then as a line chart. ```sql 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 AUTO ``` 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. ```` **`EXTRAPOLATE` clause** 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](https://docs.newrelic.com/docs/agents/manage-apm-agents/agent-data/new-relic-events-limits-sampling) 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](https://docs.newrelic.com/docs/using-new-relic/metrics/analyze-your-metrics/data-collection-metric-timeslice-event-data#event-data) that it often passes its [harvest cycle](https://docs.newrelic.com/docs/using-new-relic/welcome-new-relic/getting-started/glossary#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. > #### ⚠️ IMPORTANT > > 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](https://docs.newrelic.com/docs/insights/nrql-new-relic-query-language/nrql-reference/nrql-syntax-components-functions#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`. ````sql 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. ````sql SELECT count(*) FROM Transaction WHERE appName = 'interestingApplication' SINCE 60 minutes ago FACET name TIMESERIES 1 minute EXTRAPOLATE ``` ```` **`FACET` clause** ````sql 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`](#sel-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](#functions), `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. ```sql 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. ```sql SELECT uniqueCount(pageUrl) FROM PageView FACET city ``` [Advanced segmentation](/docs/insights/new-relic-insights/features/advanced-segmentation) and [cohort analysis](/docs/insights/new-relic-insights/features/cohort-analysis-grouping-results-across-time) 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. 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: ```sql FROM Event SELECT average(attribute) FACET name ``` Names 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: ```sql FROM Event SELECT attribute, name WHERE attribute IS NOT NULL ``` ```` **`FACET ... AS` clause** 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](https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/nrql-query-tutorials/nested-aggregation-make-ordered-computations-single-query) 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. ````sql FROM Transaction SELECT count(*) FACET response.headers.contentType AS 'content type' ``` ```` **`FACET CASES` clause** ````sql 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`](#sel-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`](#sel-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](/docs/query-data/nrql-new-relic-query-language/nrql-query-examples/group-results-across-time) with your attribute, and you can use the `OR` operator to facet results that don't match any of your specified cases. Basic usage with WHERE} > ```sql 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: ```sql 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`](#sel-as) selector to give your results a human-readable name: ```sql 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' ) ``` Facet non-matching data with OR} > This example uses the `OR` operator to facet results that didn't match any of your cases: ```sql SELECT count(*) FROM Transaction FACET CASES ( WHERE name LIKE '%login%', WHERE name LIKE '%feature%' AND customer_type = 'Paid' ) OR name ``` ```` **`FACET ... ORDER BY` clause** 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. ````sql 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`. 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. 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. ```` **`JOIN` 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. ````sql FROM Event [INNER|LEFT] JOIN (SELECT... FROM...) ON [key =] key SELECT ... ``` There are a few simple rules for subquery joins: * The `JOIN` clause must always follow immediately after the [`FROM`](#sel-from) clause. * Prefixing a join type (`INNER` or `LEFT`) is optional. When omitted, the join type defaults to `INNER`. * Parenthesis containing a [subquery](/docs/query-your-data/nrql-new-relic-query-language/get-started/subqueries-in-nrql) must immediately follow `JOIN`. * The `ON` clause must immediately follow the subquery.
**Join types**
`INNER` 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.
`LEFT` The result will include events from the outer query that do not have a match from the joined subquery.
**Join `ON` clause**
`ON parentKey = subqueryKey` Defines the key values to compare in the subquery and the outer query. The only comparison operator allowed is equality. * The left-hand side is always the key used in the parent query and may be an attribute or function. * The right-hand side is used for the subquery key value, and must be an identifier.
`ON key` This is an abbreviated syntax for when the key identifier is the same in both contexts. It is equivalent to `ON key = key`.
Restrictions and limitations to consider: * The joined subquery will continue to have a default [`LIMIT`](#sel-limit) of 10, with a maximum `LIMIT` of 5,000. Note that the outer query's `LIMIT` does not affect the inner query. * The use of `TIMESERIES` in the joined subquery is not supported. If your outer query uses `TIMESERIES`, 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](/docs/query-your-data/nrql-new-relic-query-language/nrql-query-tutorials/subquery-joins) tutorial. INNER JOIN with a faceted subquery} id="example-faceted-inner-join" > 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. ```sql FROM PageView JOIN ( FROM PageAction SELECT count(*) FACET session, currentUrl ) ON session SELECT count(*) FACET browserTransactionName, currentUrl ``` screenshot faceted inner join
Faceted INNER JOIN example
LEFT JOIN with a faceted subquery}> This example queries the same data as the [faceted `INNER JOIN` example](#example-faceted-inner-join), 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. ```sql FROM PageView LEFT JOIN ( FROM PageAction SELECT count(*) FACET session, currentUrl ) ON session SELECT count(*) FACET browserTransactionName, currentUrl ``` screenshot faceted left join
Faceted LEFT JOIN example
INNER JOIN with an unaggregated subquery}> 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. ```sql FROM PageAction LEFT JOIN ( FROM PageView SELECT session, browserTransactionName LIMIT MAX ) ON session SELECT count(*) FACET session, currentUrl, browserTransactionName LIMIT MAX ``` screenshot columnar inner join
Unaggregated INNER JOIN example
```` **`LIMIT` clause** ````sql 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`. 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. ```sql SELECT uniqueCount(session), percentile(duration, 95) FROM PageView WHERE userAgentOS = 'Windows' FACET countryCode LIMIT 20 SINCE YESTERDAY ``` ```` **`OFFSET` clause** ````sql 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. ```` **`ORDER BY` clause** 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. ````sql 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. ```sql FROM Transaction SELECT * ORDER BY duration DESC ``` 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`](#sel-facet-order). ```` **`PREDICT` clause** ````sql FROM Transaction SELECT count(*) WHERE error IS TRUE TIMESERIES PREDICT ... ``` With the `PREDICT` clause, you can add forecasts of future data trends into line charts based on historical data. Consider the following points when using this clause: * Applies only to queries with a `TIMESERIES` clause. * Uses the `TIMESERIES