Parse Nested JSON Array into Splunk Table

Question

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 would like 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

score 0 · Answer 1 · answered Aug 02 '19 at 07:38

@Kripz

Can you please try this search?

| 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\" }" |kv | spath path=items{} output=items | mvexpand items | fields cr id items | eval raw=items | kv | rename parts{}.* as Parts* | eval temp=mvzip(mvzip(Parts_code,Parts_counter),Parts_pNum) | mvexpand temp | eval Parts_code=mvindex(split(temp,","),0) ,Parts_counter=mvindex(split(temp,","),1),Parts_pNum=mvindex(split(temp,","),2) | table id cr se so Parts_code Parts_var Parts_counter Parts_pNum

Check my blog for same: https://www.crestdatasys.com/blogs/how-to-extract-complex-field-from-nested-json-events-using-splunk-spl/

Parse Nested JSON Array into Splunk Table

1 Answers1