New Relic Query Language Enhancements: Wildcards, Empty Strings, Null, Empty Sets, and More

If you're working your way toward wizard status, you’ll want to know we just added a lot more power to New Relic Query Language (NRQL). As a refresher, NRQL is the query language of New Relic Database (NRDB), empowering you to explore your telemetry data. With New Relic dashboards and analytics, you can convert your data into charts for an at-a-glance view to make more efficient decisions based on the results.

To better serve our users, we recently made some significant enhancements to NRQL, which are available today for all New Relic users.

Aggregate or separate metrics with wildcards

Our new NRQL wildcards make it faster and easier to query and chart multiple metrics together. Wildcards also eliminate the need to manually add individual metrics to a query when new metrics are created that follow a similar naming pattern.

For instance, Hierarchical naming patterns are commonly used to add structure to metric data and make it easy to understand what a metric is measuring at a glance. For example:

• memory.heap.used
• memory.heap.committed
• A.duration
• B.duration

With NRQL’s old syntax, charting the average of algorithms A and B (from above) required the following:

FROM Metric SELECT average(algorithm.A.duration), average(algorithm.B.duration) TIMESERIES

The above isn’t particularly difficult, but as a user continues adding variations of this metric, or if values change over time, explicitly listing metric names individually becomes cumbersome and error prone.

If you were to add a new metric or change an existing one, you would have to manually update all of your dashboards, charts, alerts, and saved queries that use this particular metric, or else you would never see it.

Wildcards to the rescue

Let’s say you have the following metrics, which show the duration of different algorithms related to a single process:

• algorithm.A.duration
• algorithm.B.duration
• algorithm.C.duration

Now, you can use wildcarding to query all three algorithms with ease, and facet according to the structure of the rest of the query.

Scenario 1: Aggregate average duration as a single value:

FROM Metric SELECT average(algorithm.%.duration) TIMESERIES

Scenario 2: Facet average duration by metric:

FROM Metric SELECT average(algorithm.%.duration) FACET metricName TIMESERIES

In both scenarios, the wildcard (%) is processed as a variable. Thus, any metric with a metricName that starts with algorithm and ends with .duration will be returned.

Null, empty strings, empty sets, and NaN

Every day, NRDB ingests over 1 billion data points per minute, including metrics, events, logs, and traces (see Telemetry Data 101). The telemetry you send to NRDB consists of myriad data types and values, and now we’re enhancing the way we handle null, empty strings, empty sets, and NaN (not-a-number), so that you get the results you expect. Read below to find out more.

Empty strings are empty strings

Empty strings are just that—empty strings. They’re not null, and NRDB has always stored empty strings as distinct entities. But at query time, NRQL was returning empty strings as null, which was providing unexpected results. For instance, in cases where users wanted to utilize an attribute with an empty value as a way of representing “tags,” a condition like 'WHERE `label.node-role.kubernetes.io/master` IS NOT NULL would returns as FALSE, whether the tag is present or not.

We’ve updated NRQL so that an empty string in the storage format = an empty string at query time. Now, users can also get the benefits of the aforementioned “tags” on their integrations data.

Min, max, and average of the empty set are NaN, not 0

When our customers initially begin sending data to NRDB, sometimes that data lacks values—what’s known as an empty set.

NRDB treats the min, max, or average of such an empty set as NaN. But at query time, NRQL previously returned the min, max, or average as ‘0’ instead of ‘NaN.’

Now, as you spin up (or down) containers, you won’t see values of ‘0’ before any telemetry data is reported to NRDB. While these scenarios represent edge cases, rest assured that NRQL will now return the appropriate value of ‘NaN’ when querying empty sets.

If you’re interested in brushing up on your NRQL querying, visit our docs site or share your thoughts on these updates at the New Relic Explorers Hub.