![](/style/images/good.png)
![](/style/images/bad.png)
We've added more ways to parse and transform your data!
source link: https://newrelic.com/blog/how-to-relic/new-parse-and-transform-capabilities
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
We've added more ways to parse and transform your data!
New NRQL functions that parse your JSON, convert your timestamps, map your IP addresses, and many more!
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: {%'
![jparse](https://newrelic.com/sites/default/files/styles/900w/public/2024-06/1jparse%20screenshot.png?itok=cU8QIA28)
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
![toTimestamp](https://newrelic.com/sites/default/files/styles/900w/public/2024-06/2toTimestamp%20screenshot.png?itok=dbRsCbZo)
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’.
![toDatetime](https://newrelic.com/sites/default/files/styles/900w/public/2024-06/3toDatetime%20screenshot.png?itok=f50SAerw)
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%'
![cidrAddress](https://newrelic.com/sites/default/files/styles/900w/public/2024-06/4cidrAddress%20screenshot.png?itok=mWE8HXxV)
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')
![convert](https://newrelic.com/sites/default/files/styles/900w/public/2024-06/5convert.png?itok=RneTSru7)
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.
Next steps
- Watch the instructional Data Bytes video on these parsing and transformation functions.
- Take a deep dive into the NRQL subquery JOINs documentation.
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.
Alexander Jones is a Principal Product Manager at New Relic. He focuses on building the query language to help customers expand what they can do with their data. His work includes driving new language features and improving the system’s performance and reliability.
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK