How to Extract Complex Field from Nested {JSON} events using Splunk SPL
Nowadays, we see several events being collected from various data sources in JSON format.
Splunk has built powerful capabilities to extract the data from JSON and provide the keys into field names and JSON key-values for those fields for making JSON key-value (KV) pair accessible. spath is very useful command to extract data from structured data formats like JSON and XML.
In this blog, an effective solution to deal with below mentioned JSON format will be presented. For example, we have below stated JSON as an event which is representing live online player data and a single event contains all the required set of data.
Our interested fields in JSON are `label`, `count ` and `peak24` with `mode`. Here, the values of `mode` are pc, ps3, Xbox, xone, ps4, etc. These values are not fixed so they cannot hard-code in the search.
Problem:
We can use `label`, `count ` and `peak24` as direct fields but `mode` as a field is not directly available in the event.
Sample Event:
Desired Output:
The goal is to extract fields from JSON data and represent the data in a tabular format.
Here, the idea is to display the “Count of live Players” GROUPED BY `mode`. So let’s start searching!
1) Generate sample event by using `makeresults` command.
NOTE: Using "\" is very important before any special characters.
2) Now add ` | spath` to above search.
Result:
PROBLEM PERSISTS: As you can see, the key is a combination of mode & values which is not as per our expectations.
So, let’s try and tackle this with a few commands that come in handy to handle these kinds of problems
Note: As per our expectations, desired values are already present as field values in the table. The only thing remaining is to assign these values to the proper `mode` field.
3) Use `untable` command to make a horizontal data set.
Use `untable` command to make a horizontal data set.
Once we get this, we can create a field from the values in the `column` field. Don’t be afraid of “| eval {Column}=Value”. This is just a magical move called dynamic eval. <>
Result:
You can see the new fields count, label and peak24. Now we have all the required fields but in different rows.
4) Get mode and column name using mvindex and split command
Once we get this, we can create a field from the values in the `column` field. Don’t be afraid of “| eval {Column}=Value”. This is just a magical move called dynamic eval. <>
Result:
You can see the new fields count, label and peak24. Now we have all the required fields but in different rows.
5) Last, use `stats` command to get values GROUPED BY mode.
Result:
You can use this technique in any JSON data similar to sample JSON event and display data in a tabular format.
Author
SEIN TUN
Sein Tun is a Software Engineer working at Crest Data. He has varied software development experience utilizing modern technologies and frameworks like React, Redux, Node.js, JavaScript, Python, and Golang to build secure high-performance systems. Before joining Crest, Sein worked as Financial Banker and Assistant Manager for 3 years with JPMorgan Chase Bank excelling in customer experience, compliance, and team management. He is a proud alumnus of the University of California Riverside with a Bachelor of Science in Management Information System. He enjoys building applications that improve and make a difference in people’s lifestyles.