Parse Nested JSON Array into Splunk Table

[kripzadamas]

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

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{}.*

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

[woodcock]

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]

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

[woodcock]

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

[to4kawa]

| 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

[woodcock]

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

[woodcock]

We need a fillup command.

[to4kawa]

OR filldown desc is fine also, too .

[woodcock]

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

[poete]

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