Splunk Search

Parse Nested JSON Array into Splunk Table

kripzadamas
Engager

I have the below JSON event with nested array in splunk -:

{
"items":
    [
        {
            "parts":
                [
                    {
                        "code":"1","var":"","pNum":"101","counter":1019
                    },
                    {
                        "code":"0","var":"","pNum":"102","counter":1029
                    }
                ],
            "se":"A1",
            "so":"111"
        },
        {
            "parts":
                [
                    {
                        "code":"1","var":"","pNum":"301","counter":3019
                    },
                    {
                        "code":"0","var":"","pNum":"302","counter":3029
                    }
                ],
            "se":"A3",
            "so":"333"
        },
        {
            "parts":
                [
                    {
                        "code":"0","var":"","pNum":"401","counter":4019
                    }
                ],
            "se":"A4",
            "so":"444"
        },
        {
            "parts":
                [
                    {
                        "code":"1","var":"","pNum":"501","counter":5019
                    }
                ],
            "se":"A5",
            "so":"555"
        }
    ],
"id":"x.9110790",
"cr":"x-273169"
}

I need to extract this JSON into the below Splunk table -:

expected_output_table

I tried to use spath as below but it is only giving wrong results given below -:

|makeresults | eval _raw="{ \"items\": [ { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"101\",\"counter\":1019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"102\",\"counter\":1029 } ], \"se\":\"A1\", \"so\":\"111\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"301\",\"counter\":3019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"302\",\"counter\":3029 } ], \"se\":\"A3\", \"so\":\"333\" }, { \"parts\": [ { \"code\":\"0\",\"var\":\"\",\"pNum\":\"401\",\"counter\":4019 } ], \"se\":\"A4\", \"so\":\"444\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"501\",\"counter\":5019 } ], \"se\":\"A5\", \"so\":\"555\" } ], \"id\":\"x.9110790\", \"cr\":\"x-273169\" }" |spath |rename items as * | table id, cr,items{}.*

splunk_actual_output

I am trying to parse the JSON type splunk logs for the first time. So please help with any hints to solve this.
Thank you

0 Karma

woodcock
Esteemed Legend

This is nasty but...

| makeresults 
| eval _raw="{
 \"items\":
     [
         {
             \"parts\":
                 [
                     {
                         \"code\":\"1\",\"var\":\"\",\"pNum\":\"101\",\"counter\":1019
                     },
                     {
                         \"code\":\"0\",\"var\":\"\",\"pNum\":\"102\",\"counter\":1029
                     }
                 ],
             \"se\":\"A1\",
             \"so\":\"111\"
             },
         {
             \"parts\":
                 [
                     {
                         \"code\":\"1\",\"var\":\"\",\"pNum\":\"301\",\"counter\":3019
                     },
                     {
                         \"code\":\"0\",\"var\":\"\",\"pNum\":\"302\",\"counter\":3029
                     }
                 ],
             \"se\":\"A3\",
             \"so\":\"333\"
             },
         {
             \"parts\":
                 [
                     {
                         \"code\":\"0\",\"var\":\"\",\"pNum\":\"401\",\"counter\":4019
                     }
                 ],
             \"se\":\"A4\",
             \"so\":\"444\"
             },
         {
             \"parts\":
                 [
                     {
                         \"code\":\"1\",\"var\":\"\",\"pNum\":\"501\",\"counter\":5019
                     }
                 ],
             \"se\":\"A5\",
             \"so\":\"555\"
             }
     ],
 \"id\":\"x.9110790\",
 \"cr\":\"x-273169\"
     }" 

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| rex "(?ms)\"id\":\"(?<id>[^\"]+).*?\"cr\":\"(?<cr>[^\"]+)" 
| rex max_match=0 "(?ms)[\r\n\s]+\"parts\":[\r\n\s]+(?<parts>.*?\")[\r\n\s]+}" 
| fields - _raw
| mvexpand parts
| rex field=parts "(?ms)\"se\":\"(?<se>[^\"]+).*?\"so\":\"(?<so>[^\"]+)"
| rex field=parts max_match=0 "(?ms)[\r\n\s]+{[\r\n\s]+(?<part>[^\r\n]+)"
| fields - parts
| mvexpand part
| rex field=part max_match=0 "\"(?<key>[^\"]+)\":\"?(?<val>[^\",]+)"
| eval _raw = mvzip(key, val, "=")
| fields - part key val
| kv
| table id cr se so code var pNum counter

to4kawa
Ultra Champion

"(?ms)[\r\n\s]+{[\r\n\s]+(?<part>[^\r\n]+)"
I couldn't write this regex.

woodcock
Esteemed Legend

I am not too sure that I should have...

0 Karma

to4kawa
Ultra Champion
| makeresults 
| eval _raw="{ \"items\": [ { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"101\",\"counter\":1019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"102\",\"counter\":1029 } ], \"se\":\"A1\", \"so\":\"111\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"301\",\"counter\":3019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"302\",\"counter\":3029 } ], \"se\":\"A3\", \"so\":\"333\" }, { \"parts\": [ { \"code\":\"0\",\"var\":\"\",\"pNum\":\"401\",\"counter\":4019 } ], \"se\":\"A4\", \"so\":\"444\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"501\",\"counter\":5019 } ], \"se\":\"A5\", \"so\":\"555\" } ], \"id\":\"x.9110790\", \"cr\":\"x-273169\" }" 
| rex mode=sed "s/}, {/}# {/g" 
| eval raw=split(_raw,"#") 
| mvexpand raw 
| fields - _* 
| streamstats count(eval(match(raw,"\["))) as session 
| rex field=raw max_match=0 "(?<fieldnamevalue>\"\S+?\:[^ ,]+)" 
| fields fieldnamevalue 
| streamstats current=f count as session 
| mvexpand fieldnamevalue 
| rex field=fieldnamevalue "(?<fieldname>.*):(?<fieldvalue>.*)"
| foreach field* [eval <<FIELD>> = trim('<<FIELD>>' ,"\"")]
| xyseries session fieldname fieldvalue
| reverse
| filldown
| reverse
| eval cr="x-".(tonumber(mvindex(split(cr,"-"),1)) + session) ,id="x.".(tonumber(mvindex(split(id,"."),1)) + session)
| table id,cr,se,so,code,var,pNum,counter
0 Karma

woodcock
Esteemed Legend

Yours should have ended like this:

| rex field=fieldnamevalue "\"?(?<fieldname>[^:\"]+)\"?:\"?(?<fieldvalue>[^:\"]+)\"?" 
| eval {fieldname} = fieldvalue 
| fields - fieldnamevalue fieldname fieldvalue 
| stats values(*) AS * BY session 
| reverse 
| filldown 
| table id,cr,se,so,code,var,pNum,counter 
| reverse
0 Karma

woodcock
Esteemed Legend

We need a fillup command.

to4kawa
Ultra Champion

OR filldown desc is fine also, too .

0 Karma

woodcock
Esteemed Legend

I get about half of this just reading it. I need to run it and rip it apart.

0 Karma

poete
Builder

Hello @kripzadamas,

check the following, it meets your requirements. I got the idea from the documentation (botton of this page https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath).

|makeresults | eval _raw="{ \"items\": [ { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"101\",\"counter\":1019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"102\",\"counter\":1029 } ], \"se\":\"A1\", \"so\":\"111\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"301\",\"counter\":3019 }, { \"code\":\"0\",\"var\":\"\",\"pNum\":\"302\",\"counter\":3029 } ], \"se\":\"A3\", \"so\":\"333\" }, { \"parts\": [ { \"code\":\"0\",\"var\":\"\",\"pNum\":\"401\",\"counter\":4019 } ], \"se\":\"A4\", \"so\":\"444\" }, { \"parts\": [ { \"code\":\"1\",\"var\":\"\",\"pNum\":\"501\",\"counter\":5019 } ], \"se\":\"A5\", \"so\":\"555\" } ], \"id\":\"x.9110790\", \"cr\":\"x-273169\" }" 
| spath
| table _raw,cr,id
| spath path=items{}
| rename items{} as items
| mvexpand items
| spath input=items
| rename parts{}.code as code parts{}.counter as counter parts{}.pNum as pNum parts{}.var as var  
| eval x=mvzip(code,mvzip(counter,pNum))
| table cr,id,x,var,se,so
| mvexpand x
| eval x = split(x,",") | eval code=mvindex(x,0)| eval counter=mvindex(x,1)| eval pNum=mvindex(x,2)
| table id,cr,se,so,code,var,pNum,counter
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...