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:

{
	"pc": {
		"label": "PC",
		"count": 24,
		"peak24": 12
	},
	"ps3": {
		"label": "PS3",
		"count": 51,
		"peak24": 10
	},
	"xbox": {
		"label": "XBOX360",
		"count": 40,
		"peak24": 11
	},
	"xone": {
		"label": "XBOXONE",
		"count": 105,
		"peak24": 99
	},
	"ps4": {
		"label": "PS4",
		"count": 200,
		"peak24": 80
	}
}

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. 

| makeresults
| eval _raw="{\"pc\":{\"label\":\"PC\",\"count\":24,\"peak24\":12},\"ps3\":
{\"label\":\"PS3\",\"count\":51,\"peak24\":10},\"xbox\":
{\"label\":\"XBOX360\",\"count\":40,\"peak24\":11},\"xone\":
{\"label\":\"XBOXONE\",\"count\":105,\"peak24\":99},\"ps4\":
{\"label\":\"PS4\",\"count\":200,\"peak24\":80}}"

NOTE: Using “\” is very important before any special characters. 

2) Now add ` | spath` to above search.

| makeresults
| eval _raw="{\"pc\":{\"label\":\"PC\",\"count\":24,\"peak24\":12},\"ps3\":
{\"label\":\"PS3\",\"count\":51,\"peak24\":10},\"xbox\":
{\"label\":\"XBOX360\",\"count\":40,\"peak24\":11},\"xone\":
{\"label\":\"XBOXONE\",\"count\":105,\"peak24\":99},\"ps4\":
{\"label\":\"PS4\",\"count\":200,\"peak24\":80}}"
| spath

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.

| makeresults
|eval_raw="{\"pc\":{\"label\":\"PC\",\"count\":24,\"peak24\":12},\"ps3\":{\"label\":\"PS3\",\"count\":51,\"peak24\":10},\"xbox\":
{\"label\":\"XBOX360\",\"count\":40,\"peak24\":11},\"xone\":{\"label\":\"XBOXONE\",\"count\":105,\"peak24\":99},\"ps4\":
{\"label\":\"PS4\",\"count\":200,\"peak24\":80}}"
| spath
| untable _time Column Value

Result:

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. <<We’ll add another search tips over dynamic eval and paste link for that here later>>

| makeresults
|eval_raw="{\"pc\":{\"label\":\"PC\",\"count\":24,\"peak24\":12},\"ps3\":{\"label\":\"PS3\",\"count\":51,\"peak24\":10},\"xbox\":
{\"label\":\"XBOX360\",\"count\":40,\"peak24\":11},\"xone\":{\"label\":\"XBOXONE\",\"count\":105,\"peak24\":99},\"ps4\":
{\"label\":\"PS4\",\"count\":200,\"peak24\":80}}"
| spath
| untable _time Column Value
| eval mode=mvindex(split(Column,"."),0),Column=mvindex(split(Column,"."),1)
| eval {Column}=Value

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.

| makeresults
|eval_raw=”{\”pc\”:{\”label\”:\”PC\”,\”count\”:24,\”peak24\”:12},\”ps3\”:
{\”label\”:\”PS3\”,\”count\”:51,\”peak24\”:10},\”xbox\”:
{\”label\”:\”XBOX360\”,\”count\”:40,\”peak24\”:11},\”xone\”:
{\”label\”:\”XBOXONE\”,\”count\”:105,\”peak24\”:99},\”ps4\”:{\”label\”:\”PS4\”,\”count\”:200,\”peak24\”:80}}”
| spath
| untable _time Column Value
| eval mode=mvindex(split(Column,”.”),0),Column=mvindex(split(Column,”.”),1)
| eval {Column}=Value
| stats values(label) as label values(count) as count values(peak24) as peak24 by mode

Result:

You can use this technique in any JSON data similar to sample JSON event and display data in a tabular format.