Parse and transform your data with NRQL

Today we’re excited to announce a handful of parsing and transformation functions to help you more easily find the data you need at query time, allowing you to build dashboards and alerts faster, no matter how unstructured your data is. 

Let’s be honest, data isn’t always pretty. Data is often stored in a way that can’t be used “out of the box,” meaning a timestamp or entity guid or store ID might be hidden deep within a dataset. One way of fixing this is to clean up the data before it’s ingested, but the teams that digest and analyze the data (for example, DevOps) aren’t always the ones responsible for how it got there in the first place. Which is why we’re making your life easier. We’ve added the following functions to the New Relic Query Language (NRQL), which can be used by anyone with access to New Relic:

• Parse JSON at query time: jparse(), mapKeys(), mapValues()
• Convert timestamps: toTimestamp(), toDatetime()
• Map IP addresses: cidrAddress()
• Encode and decode Base64: encode(), decode()
• Convert from one unit to another: convert()

See below for walkthroughs and examples of each.

Parse JSON at query time

Data comes in many formats, with one of the more common being JSON. To parse this data, you typically have to use a complicated regex or anchor parse, which is no fun. Now, with jparse(), a parser is specifically tailored to this data type:

jparse(attribute, [path])

Let’s say we have a log message that has JSON after the text “Order response:”, as in the following example: 

Order response: {"timestamp":1709938901291,"status":500...

First, we can use anchor parse to pull out the JSON, then use the jparse() function to extract the fields we want:

WITH aparse(message, 'Order response: *') AS json

FROM Log 

SELECT  jparse(json, 'timestamp'), jparse(json, 'status'), message

WHERE message LIKE 'Order response: {%'

You can see extracting the timestamp and status code from the JSON is much easier with this new functionality.

There are two additional functions that work well with JSON parse that we also recently released:

• mapKeys() extracts a list of all the keys when passed a map as an input. 
  • In the example above it would be ['timestamp', 'status', etc.]
• mapValues() extracts a list of all the values when passed a map as an input
  •  ['1709938901291', '500', etc.]

To learn more, see the documentation of each of these functions here: jparse(), mapKeys(), mapValues()

Convert timestamps

Let’s say you’ve parsed out a timestamp from your JSON (finally), but you now have an epoch time '1709938901291' that isn’t readable. The opposite thing can happen as well: you extract a date in string format, like 'March 08, 2024 23:01:41', and have no way to compare this timestamp with other timestamps.

To address this, use the new timestamp conversion functions, so you can format timestamps however you like.

toTimestamp(datestring [, pattern [, timezone]])

toTimestamp()—alternatively known as fromDateTime()—takes a datetime string and translates it into a timestamp represented in epoch milliseconds. 

Example: 

FROM Log
SELECT toTimestamp('2023-10-18 15:27', 'yyyy-MM-dd HH:mm', timezone: 'America/Los_Angeles') 

This will result in the epoch time: 1697668020000

Note: The New Relic UI translates this to a readable date.

toDatetime(timestamp [, pattern [, timezone]])

toDatetime()—alternatively known as fromTimestamp()—takes a timestamp numeric and translates it into a datetime string of your choosing. Note that the format defaults to: yyyy-MM-dd'T'HH:mm:ss[.SSS][XXX].

An optional timezone value can be used to interpret the date string parameter.

Example: 

FROM Log

SELECT toDatetime(1697668020000, 'yyyy-MM-dd HH:mm', timezone: 'America/Los_Angeles')

The above query returns ‘2023-10-18 15:27’. 

To learn more, see the documentation of each of these functions here: toTimestamp(), toDatetime()

Map IP addresses

IP addresses can be a crucial data point in your system, and grouping them by base network helps give you a broad view of where data is coming from. Until now, converting an IP address to its base network was very tricky, but with cidrAddress this problem is now solved:

cidrAddress(<attribute>[, <number> [ ,cidrFormat:true|false]])

cidrAddress() takes a CIDR IP address and outputs the corresponding base network address. The attribute can be an IP address on its own or with a prefix length in CIDR notation. The number represents the prefix length, and if the attribute is in CIDR notation, it takes precedence over the CIDR string provided prefix length.

Let’s say we have a log message with an IP address and a port embedded in the message:

...{id:"etcd-events-a" endpoints:"172.20.44.143:3997" }...

We again use anchor parse to extract the IP address, then using our new function we can find its base address.

WITH aparse(message, '%endpoints:"*:*"%') AS (IP, Port)

FROM Log SELECT  count(*) 

FACET cidrAddress(IP, 24)

WHERE message LIKE '%etcdClusterPeerInfo%'

To learn more, see the documentation of this function here: cidrAddress()

Encode and decode Base64

Data might also arrive in the Base64 format, which can cause issues if you’re unable to decode it. To address this, use encode() and decode() to reformat your data whenever you’d like:

encode(input, encoding)

encode() performs Base64 conversions on a provided string, following the Base64 standard specified in the second parameter. encode() supports ‘base64’, ‘base64mime’, and ‘base64url’ as encoding parameters. encode() is not supported for blobs.

Example: 

FROM Event SELECT encode('Hello World', 'base64')

The above query returns ‘SGVsbG8gV29ybGQ=’.

decode()

decode() performs Base64 conversions on strings and blobs, following the Base64 standard specified in the second parameter. decode() supports ‘base64’, ‘base64mime’, and ‘base64url’ as decoding parameters.

Example: 

FROM Event SELECT decode(‘SGVsbG8gV29ybGQ=', ‘base64’)

The above query returns ‘Hello World’.

To learn more, see the documentation of each of these functions here: encode(), decode()

Convert from one unit to another

It’s as simple as it sounds. If you have data stored in, for example, milliseconds, and you want to convert to seconds, this function is for you:

convert(attribute, fromUnits, toUnits)

Example 1: 

FROM Transaction 

SELECT  duration, convert(duration, 's', 'ms')

This function will be especially helpful if your OpenTelemetry (OTel) metrics have a unit specified, making the conversion dynamic.

Example 2: 

FROM FROM Metric

SELECT average(convert(apm.service.transaction.duration, unit, 'ms')) AS 'Response time'

To learn more, see the documentation of this function here: convert()

Conclusion

With all of these parsing and transformation functions added, we’ve made it a lot easier for you to understand your data, no matter how hidden or encoded it is. Tasks that were arduous in the past can now be accomplished with just one function. We’re excited to see how you use these new functions to further visualize and gain deeper insights into your data.