Getting all of your telemetry data in one place—including metrics, events, traces, and logs— is the first step towards observability across your stack. However, all the great measurement and metrics database tools in the world won’t help if you’re not able to easily connect and explore your data to understand the health and performance of your apps and systems.
New Relic Query Language (NRQL) lets you slice and dice your data to get more insights from your data—and it just got more powerful! Through subqueries, you can now connect data from multiple different sources and time ranges within a single query. Watch this video, or continue reading to learn how to use subqueries to correlate deeper insights across your stack.
What is a subquery?
A subquery is simply a NRQL query which is embedded inside of another query. Each subquery is evaluated, then the result is used during the execution of the outer query. You can nest up to three levels of subqueries. For example, this query finds the number of transactions with durations above average:
FROM Transaction SELECT count(*) WHERE duration > (
FROM Transaction SELECT average(duration)
)If you had an average duration of 30ms, then the query would be equivalent to:
FROM Transaction SELECT count(*) WHERE duration > 30Previously, you’d first need to run a query to find the average and then manually input the average into a second query.
Connecting your data: subquery examples
Modern observability requires telemetry across all of your services. However, the more data sources that you integrate into your monitoring stack, the more difficult it is to tell a single cohesive story about how your application is performing. Here are some subquery use cases to help solve that problem.
Associating app errors and performance by connecting entity data
An entity is anything that has a unique entity ID and either reports data to New Relic or contains data that New Relic can access. For most entities, the ID is indicated by the attribute entityGuid.
This example NRQL subquery compares average duration for different app services, which recently had errors, to reveal how transaction errors are impacting app execution time. The example showcases how you can leverage the IN clause to connect identifiers from two different data sources.
Here's the sample query:
FROM Transaction SELECT average(duration) WHERE entity.guid IN (
FROM TransactionError SELECT uniques(entity.guid)
)FACET appName TIMESERIESFinding trends in multiple time ranges
Another powerful feature of subqueries is the ability to compare data from different time ranges. You get much more interesting and detailed comparisons than you could previously using just the COMPARE WITH feature. Here’s an example:
FROM Transaction SELECT average(duration)-(
FROM Transaction SELECT average(duration)SINCE 1 day AGO
) TIMESERIES 5 minutes SLIDE BY MAXThis query computes the average duration of all transactions for the last day, then finds the delta with a timeseries of the last hour. SLIDE BY is only used to smooth out the chart. This query shows you how your system is behaving relative to yesterday, giving you insight on recent trends. In the example in the screen shot, the system is quite stable, only deviating by a few nanoseconds, but that isn’t always the case!
Connecting your data with logs
Logs provide immensely useful information, and with New Relic logs in context, you can quickly drill down to relevant log details from applications, serverless, and Kubernetes clusters. Now, you can also use NRQL subqueries to analyze connections between transactions and logs.
FROM Log SELECT * WHERE hostname = (
FROM Transaction SELECT latest(host) WHERE containerId = 'insertcontainerid'
) AND level = 'ERROR'The previous example queries transactions to associate a particular containerId with a host that contains relevant error logs. With subqueries, you can make these types of associations easily without manually querying and copying values. You can also discover transactions from hosts that produce log errors, as shown in this query:
FROM Transaction SELECT * WHERE hostname = (
FROM Log SELECT latest(host) WHERE level = 'ERROR'
)Connecting your transactions and spans with nested subqueries
Spans and transactions each represent parts of a request moving through your system, but connecting them can be difficult. The next query leverages multiple nested subqueries so you can see the total number of transactions in each service that includes spans slower than 99% of all spans. This query can help you find bottlenecks in your system.
FROM Transaction SELECT count(*) WHERE entity.guid IN (
FROM Span SELECT latest(entity.guid) WHERE duration > (
FROM Span SELECT percentile(duration, 99)
) FACET entity.guid ORDER BY average(duration) LIMIT 100
) FACET appNameThe subquery pattern FROM Event SELECT latest(attribute) FACET attribute ORDER BY average(numAttribute) can be very useful! In this case, it allows you to get the top 100 entity guids by average duration, and use those 100 values in the parent query. This is the second level of subquery in the previous query.
Tips for using subqueries
Using subqueries can take a little practice. Here are a few tips to help:
- Test the query without a subquery. Run the parent query with test values to ensure it works as expected before introducing a subquery.
- Test the subquery on its own. Run the subquery to review the results before using it in the parent query.
- Understand the limitations of subqueries. Review the NRQL subqueries documentation for details.
- Upgrade your query experience with Data Plus. Data Plus users get up to 3 times the maximum query limits and 2 times the maximum query duration. With Data Plus, you can inspect up to one trillion data points per 30 minutes and 100 billion data points per minute (compared to 300 billion data points per 30 minutes and 10 billion data points per minute with the original data option).
- Check the subquery result type. This will affect the type of clauses that you can use the subquery in.
- If the subquery returns a table result of many values, it will only work within an
INclause, such asFACETqueries oruniques(...)queries. - For other contexts such as equality
(=)or math(+, -, etc.),FACETorcolumnsqueries will needLIMIT 1to work. - You can use queries that return a single value anywhere a normal literal value could be used, such as a number or a text string. There are edge cases, such as the number of buckets chosen for a histogram function, that aren't currently supported. Generally, the subquery system will provide a useful error in cases that are incorrect.
- If the subquery returns a table result of many values, it will only work within an
Subqueries are a powerful new tool for data exploration, allowing for more sophisticated queries across different data sources and time ranges. This blog post covered just a handful of examples but the possibilities are endless. We would love to see what you create as you dive deeper with NRQL subqueries. Happy querying!
Next steps
- See more subquery examples, post your own subqueries, and share your feedback in the Explorers Hub discussion post.
- Review the NRQL subqueries documentation to learn more.
- Check out the instructional NRQL subqueries video.
- If you’re not already using New Relic, get started with New Relic for free. Your free account includes 100 GB/month of free data ingest, one free full-access user, and unlimited free basic users.
The views expressed on this blog are those of the author and do not necessarily reflect the views of New Relic. Any solutions offered by the author are environment-specific and not part of the commercial solutions or support offered by New Relic. Please join us exclusively at the Explorers Hub (discuss.newrelic.com) for questions and support related to this blog post. This blog may contain links to content on third-party sites. By providing such links, New Relic does not adopt, guarantee, approve or endorse the information, views or products available on such sites.
