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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...