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
Ultra Champion
| 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
Ultra Champion
| 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

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
Ultra Champion

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
Get Updates on the Splunk Community!

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...