topic Re: How to create table using nested json? in Splunk Search

How to create table using nested json?

bharat149 — Sat, 20 Aug 2022 18:32:26 GMT

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

{status: FINISHED
   data: [
     {
       duration: 123
       status: A
     }
     {
       duration: 456
       status: B
     }
     {
       duration: 678
       status:C
     }
   ]}

I need to create the table for this nested one

Table Structure :

status	A	B	C
Finished	123	456	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

Re: Create table using nested json

donelliot — Fri, 19 Aug 2022 13:59:40 GMT

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,

becomes

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

Re: Create table using nested json

bharat149 — Sat, 20 Aug 2022 07:03:54 GMT

I want splunk query for this

Re: How to create table using nested json?

yuanliu — Sat, 20 Aug 2022 07:45:53 GMT

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

Re: How to create table using nested json?

bharat149 — Sat, 20 Aug 2022 18:35:44 GMT

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

Re: How to create table using nested json?

yuanliu — Sat, 20 Aug 2022 19:34:23 GMT

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