topic Re: How would you create a table from json array with a nested a array in each object in Getting Data In

How would you create a table from json array with a nested a array in each object

topherbirth — Tue, 21 Apr 2020 23:53:36 GMT

I have tried quite a few different ways to capture data within a json object and return it as separate events, but my break-down always occurs when attempting to access the data in the nested arrays.

{
    "Items": [
        {
            "CN": "AccountName",
            "CV": "AccountOne",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropTwo",
                    "PV": "3"
                }
            ]
        },
        {
            "CN": "AccountName",
            "CV": "AccountOne",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                }
            ]
        },
        {
            "CN": "AccountName",
            "CV": "AccountTwo",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        },
        {
            "CN": "PersonName",
            "CV": "Bob",
            "Props": [
                {
                    "PN": "PropOne",
                    "PV": "5"
                },
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        },
        {
            "CN": "PersonName",
            "CV": "Bob",
            "Props": [
                {
                    "PN": "PropThree",
                    "PV": "8"
                }
            ]
        }
    ]
}

Prepared version for testing:

{ \"Items\": [ { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropTwo\", \"PV\": \"3\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountTwo\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropThree\", \"PV\": \"8\" } ] } ] }

I would want this to result in something like:

Thanks!

Re: How would you create a table from json array with a nested a array in each object

to4kawa — Wed, 22 Apr 2020 01:19:45 GMT

| makeresults
| eval _raw="{ \"Items\": [ { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropTwo\", \"PV\": \"3\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountOne\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" } ] }, { \"CN\": \"AccountName\", \"CV\": \"AccountTwo\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropOne\", \"PV\": \"5\" }, { \"PN\": \"PropThree\", \"PV\": \"8\" } ] }, { \"CN\": \"PersonName\", \"CV\": \"Bob\", \"Props\": [ { \"PN\": \"PropThree\", \"PV\": \"8\" } ] } ] }"
| spath path=Items{} output=Items
| stats count by Items
| spath input=Items path=Props{} output=Props
| mvexpand Props
| spath input=Props
| spath input=Items
| fields - Items count Props*
| eval names=CN."_".CV
| stats sum(PV) as PV by names PN
| xyseries names PN PV
| rex field=names "AccountName_(?<AccountName>\w+)|PersonName_(?<PersonName>\w+)"
| table PersonName AccountName PropOne PropTwo PropThree

Re: How would you create a table from json array with a nested a array in each object

topherbirth — Wed, 22 Apr 2020 02:04:52 GMT

That works. Thanks!

Any chance we could get an explanation for how it works? I would like to learn from it, but it's hard for me to tell exactly whats happening.

Re: How would you create a table from json array with a nested a array in each object

to4kawa — Wed, 22 Apr 2020 02:29:12 GMT

Try line by line and check result.

spath session:

see reference: spath
first stats count by is to divide 5 Items
mvexpand is to divide Props

xyseries session:

see reference: xyseries
xyseries can have many args. but it is hard to understand and handle over 4 args.
so, let' make only three fields(args). | eval names=CN."_".CV this is trick.
aggregate by | stats sum(PV) as PV by names PN
rex is to extract fields by REGEX.

Is this enough? happy Splunking.

Re: How would you create a table from json array with a nested a array in each object

topherbirth — Wed, 22 Apr 2020 03:25:26 GMT

Good deal! Thanks for the info and help.