Connect data across your stack with new NRQL subqueries

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:

If you had an average duration of 30ms, then the query would be equivalent to:

Previously, 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:

Finding 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:

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

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:

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.

The 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 IN clause, such as FACET queries or uniques(...) queries.
  • For other contexts such as equality (=) or math (+, -, etc.), FACET or columns queries will need LIMIT 1 to 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.

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!