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:
|
Use case |
JOIN |
Lookup |
GeoIP |
|---|---|---|---|
|
How does application response time correlate with the CPU usage of your Kubernetes containers? |
✓ |
||
|
Which hosts running a specific version of an application have not reported logs recently? |
✓ |
||
|
Which logs have linked traces so you can see when the system is behaving unusually? |
✓ |
||
|
At which locations are point-of-sale terminals having more errors? |
✓ |
✓ |
|
|
Which partner is seeing failures when selling tickets to an event, by venue? |
✓ |
✓ |
|
|
Which requests are coming from known malicious IP addresses? |
✓ |
✓ |
✓ |
|
Where is your content delivery network (CDN) experiencing failures? |
✓ |
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:
FROM K8sNodeSample
JOIN (FROM K8sContainerSample SELECT sum(cpuLimitCores) AS cpuLCores
FACET nodeName LIMIT MAX) ON nodeName
SELECT uniques(nodeName) WHERE latest(allocatableCpuCores) < cpuLCoresThis query compares two different metrics from two separate datasets by joining them based on a common key (nodeName):
- cpuLimitCore (metric) from K8sContainerSample (table)
- 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.
FROM Transaction
JOIN (FROM lookup(storeNames) SELECT store_ID, store_name)
ON StoreID=store_ID
SELECT average(duration) FACET store_nameThis 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.
FROM Log
SELECT count(*) where ClientIP is not null FACET ClientIP.countryName, ClientIP.countryCode SINCE 1 day agoThis 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.
Next steps
To get started, log into New Relic and select Query your data. To learn more, read the JOINs technical deep-dive blog or watch the Data Bytes video embedded in this blog.
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.
