Resolve business-critical incidents with your data in one query

Today, we’re announcing the release of three powerful query capabilities—subquery JOINs, lookups, and GeoIP—that will allow you to use your data to resolve business-critical incidents with one query. Not only will you be able to get answers from your data much faster, but we’ve unlocked entirely new use cases for you, including understanding how your technology stack impacts your business.

With today’s query enhancements and the New Relic unified database, you can quickly and easily understand how each component of your tech stack impacts the others. By correlating any telemetry data and any business data in one place, you can connect the dots between the performance of your applications and infrastructure, business operations, and customer experiences, all without using additional tools or importing and exporting data between other systems.

Use cases for JOINs, lookups, and GeoIP queries

Here are just a few of the questions you can answer with these new offerings from New Relic: 

Subquery JOINs

Much of your data stored within New Relic relates to other data, which is what makes querying our unified database so powerful! Subquery JOINs allow you to combine data from different tables in the New Relic database. You can use subquery JOINs to correlate application performance monitoring (APM) event data with logs data, or infrastructure metrics with synthetics, to understand how the performance of different systems impact each other. 

For example, maybe you want to find the Kubernetes nodes where allocatableCpuCores is less than the sum of cpuLimitCores requested by the container. You would use the following syntax:

This query compares two different metrics from two separate datasets by joining them based on a common key (nodeName): 

1. cpuLimitCore (metric) from K8sContainerSample (table)
2. allocatableCpuCores (metric) from K8sNodeSample (table)

The query returns unique nodes, by name, where the allocatable CPU cores are less than the defined limit.

To learn more about subquery JOINs:

• Read the JOINs technical deep-dive blog.
• Read the NRQL subquery joins documentation.
• Watch the Data Bytes video.

Lookups

Sometimes it’s difficult to map data with alphanumeric strings, such as user IDs, store IDs, GUIDs, and more, to the physical world or to business data. For example, you wouldn’t expect an engineer to know that StoreID = 15kd637 is the same as New York City store 5. Lookups allow you to combine data that’s not in New Relic with the rest of your telemetry, so you can understand how the performance of your systems impacts other business-related metrics. Or you can simply enrich your existing New Relic data, such as translating IDs into text that users can understand. Either way, the process is the same. Simply upload a .csv file to New Relic and combine it with your telemetry data.

Let’s look at an example. If you have a set of StoreIDs that aren’t immediately meaningful, you can use a Lookup table with JOINs to help translate these into values you can understand.

This query returns the average duration of a transaction grouped by ‘store name.’ Because only the ‘store ID’ was included in the original transaction data, the query can use an uploaded spreadsheet that includes both ‘store ID’ and ‘store name’ to look up and map the ‘store name’ using JOIN syntax.

To learn more about Lookups, read the documentation and watch the Data Bytes video.

GeoIP

GeoIP translates IP addresses into geographical data, allowing you to understand the origin of your traffic.

For example, let’s say you want to know where your CDN traffic is coming from, given log data that contains IP addresses. First, you would create a log parsing rule that uses the geo function combined with a lookup to return any of the following: country name or code, region name or code, postal code, latitude, and longitude.

Then you can write a query similar to the next example, using the attributes you’re interested in.

This query returns a count of IP addresses by country name and country code so you can better understand where log traffic is coming from.

To learn more about GeoIP, read the spatial dimension documentation and watch the Data Bytes video at the bottom of this blog.