Getting Data In

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

topherbirth
Explorer

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

Thanks!

0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust
| 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

View solution in original post

to4kawa
SplunkTrust
SplunkTrust
| 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

View solution in original post

topherbirth
Explorer

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.

0 Karma

to4kawa
SplunkTrust
SplunkTrust

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.

0 Karma

topherbirth
Explorer

Good deal! Thanks for the info and help.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!