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.

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>>

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.