Today we are excited to announce the addition of subqueries within NRQL. Finally you can query one dataset and use the results as the foundation for another dataset. With this new powerful (and highly requested) feature, the possibilities are endless!
Learn more in the video below, or read on for examples, limitations, and helpful links.
Subqueries are queries within another query. The subquery result can then be used in nearly any context of a normal value, with some exceptions. See some examples below (subqueries bolded) :
- FROM a SELECT b WHERE value IN (FROM x SELECT uniques(guid) WHERE …)
- FROM x SELECT (FROM z SELECT max(value)) WHERE …
- FROM q SELECT average(FROM r SELECT s WHERE …) WHERE …
- FROM (FROM f AS avg SELECT g WHERE …) SELECT avg WHERE …
- This last example is actually a Nested Aggregation and was already available in production
As you can see the subquery can be placed in the SELECT, WHERE, and FROM clause, as well as within a function. There is a maximum limit of three subqueries within one query, but with the addition of SHIFT + ENTER to start a new line, this allows you to write your large subquery in a more readable format:
FROM Span SELECT average(duration) WHERE parentId in (FROM Span select id WHERE service.name = 'WebPortal' AND traceId IN (FROM Span SELECT uniques(traceId) WHERE name LIKE '%/java%' LIMIT MAX))
Let’s walk through what this query is doing:
- First we want to get a unique list of traces that have “/java” in the name
- Then we would only like the IDs of the traces with service name ‘WebPortal’
- Next we’ll find all the children of this trace ID
- And lastly we calculate the average duration of these specific children
As you can see it is now possible to query multiple layers of queries all in one query, finally allowing you to combine your related data into one visualization!
With all features, there are limitations and Subqueries is no exception. Below is a list of current limitations but note these may change over time. To validate these limitations please also see the Subqueries documentation.
- Subqueries are currently not allowed in alerts. We understand this is a big limitation but the system is not designed to handle it at this time. Reach out to your account team if you would benefit from this functionality.
- You can use a maximum of three subqueries in a query.
- Subqueries are limited to 2,000 results. If a subquery produces more that 2,000 results, then only the top 2,000 will be used for the outer query
- Timeout durations still apply. The entire query must finish computing before a one-minute timeout. For Data Plus users, the timeout limit is two minutes.
- Subqueries inspected count still apply. “Inspected count” is one of our system’s measures of computational effort for a given query, and a key value in determining an account’s usage / limits. Subqueries contribute to an account’s inspected count as if the queries were run individually.
Now let’s look at some real live examples in NRQL:
SELECT average(newrelic.timeslice.value) * 1000 AS 'Response time (ms)' FROM Metric WHERE entity.guid IN (FROM Relationship SELECT targetEntityGuid WHERE sourceEntityGuid = 'MTAxMDU0NTd8TlIxfFdPUktMT0FEfDU2Nzc1' AND relationshipType = 'CONTAINS') AND metricTimesliceName in ('HttpDispatcher', 'OtherTransaction/all') FACET appName TIMESERIES LIMIT MAX
This first query gets a list of all the child entities under one parent entity, then displays the response time per app in a line chart.
Top 100 Hosts
FROM SystemSample SELECT latest(cpuPercent) WHERE hostname IN (FROM Metric SELECT latest(hostname) FACET hostname ORDER BY average(newrelic.timeslice.value) LIMIT 100 ) FACET hostname since 1 day ago TIMESERIES
This query uses a nifty technique to get the TOP 100 hosts with the highest response time. Here, you use FACET … ORDER BY to get the hosts with the highest average response times and LIMIT to the top 100. Now you must use LATEST(hostname) as the use of FACET requires an aggregate in the SELECT clause. Once you have this list of hostnames, you can now produce a line chart showing the CPU percentage of these high response time hosts over time.
Calculate the API calls compared to all Transactions
SELECT count(*)/ (SELECT count(httpResponseCode) FROM Transaction WHERE appName='my_App') FROM Metric WHERE metricName LIKE 'Data_Enrichment_%API%'
In this example you would like to divide the total number of API calls from Metrics, and divide the result by the total number of Transactions within the app “my_App”. This ratio across datasets can be easily written out now with subqueries.