Splunk Search

How to create table using nested json?

bharat149
Explorer

Hi All I have a nested JSON in my log event. On that basis, I have to create a dynamic table.

{status: FINISHED
   data: [ 
     { 
       duration123
       status: A
     }
     { 
       duration456
       statusB
     }
     { 
       duration678
       status:C
     }

   ]
}

I need to create the table for this nested one

Table Structure

statusABC
Finished123456 678

Also, I have one more req. If in the future we get more values in the sub-part of JSON then can we add a column for that also

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Assuming that nested JSON is the raw event, you can use spath.

| rename status AS STATUS ``` cope with name collision ```
| spath path=duration{}
| mvexpand duration{}
| spath input=duration{}
| chart values(duration) over STATUS by status

 

Tags (2)
0 Karma

bharat149
Explorer

Hi, I have updated the duration name since it appears twice. 

index=**** some search query | 
| rename status as STATUS |  spath path=data{} | mvexpand data{} | spath input=duration{} | chart values(duration) over STATUS by status

 

After running this query I am getting no result

Screenshot 2022-08-21 at 12.05.24 AM.png

0 Karma

yuanliu
SplunkTrust
SplunkTrust

index=**** some search query | 
| rename status as STATUS |  spath path=data{} | mvexpand data{} | spath input=duration{} | chart values(duration) over STATUS by status

Sorry I didn't observe your sample data correctly.  You are correct that the path to the array is data{}, not duration{}.  As succh, in the next spath, input should also be data{}, not duration{}.

| rename status AS STATUS
| spath path=data{}
| mvexpand data{}
| spath input=data{}
| chart values(duration) over STATUS by status

(As a side: If you are posting sample JSON, make sure the format is conformant.  I made the mistake when trying to correct the format.)

0 Karma

donelliot
Path Finder

I think a kvstore with json should do be what you want.. you can use collections.conf or the lookup file editor to define the non-json elements and type, and one for the array - then I would call them out explicitly in the transforms.conf file so you can play nicely with them using lookup and inputlookup>>>

The structure you settle on will depend on how you analyze this going forward, but I'd be tempted to start with this (you can view it in https://jsongrid.com/json-grid)

{
"status": "finished",
"duration_array": [
{
"status": "A",
"duration": 123
},
{
"status": "B",
"duration": 456
},
{
"status": "C",
"duration": 678
}
]
}

To use kvstore, you can define using the collections.conf, and lookup editor or the lookup file editor iin cloud

I prefer using the structure I have suggested as you can easily insert new status values,

donelliot_0-1660917487214.png

donelliot_1-1660917487282.png


becomes

donelliot_2-1660917540583.png


You should be able to refer to the data elements and do whatever you want - unless i'm missing the point

0 Karma

bharat149
Explorer

I want splunk query for this

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In December, the Splunk Threat Research Team had 1 release of new security content via the Enterprise Security ...

Why am I not seeing the finding in Splunk Enterprise Security Analyst Queue?

(This is the first of a series of 2 blogs). Splunk Enterprise Security is a fantastic tool that offers robust ...

Index This | What are the 12 Days of Splunk-mas?

December 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...