topic Re: Create Splunk Table from nested json output in Getting Data In

Create Splunk Table from nested json output

niddhi — Fri, 08 Jan 2021 01:15:20 GMT

Hi,

Can someone please help me create a Splunk Table from the below data:

{ "cd":[ { "cn":"cust-1", "s":"api", "ps":61, "fs":94, "es":142, "ud":[ { "un":"user-0", "ps":61, "fs":94, "es":142, "ad":[ { "at":"asset2", "ps":61, "fs":94, "es":142, "ttd":[ { "tt":null, "ps":61, "fs":94, "es":142 } ] } ] } ] }, { "cn":"cust-2", "s":"api", "ps":727, "fs":152, "es":26, "ud":[ { "un":"user-6", "ps":725, "fs":149, "es":21, "ad":[ { "at":"asset1", "ps":722, "fs":149, "es":20, "ttd":[ { "tt":null, "ps":722, "fs":149, "es":20 } ] }, { "at":"asset2", "ps":3, "fs":0, "es":1, "ttd":[ { "tt":null, "ps":3, "fs":0, "es":1 } ] } ] }, { "un":"user1", "ps":1, "fs":0, "es":0, "ad":[ { "at":"asset1", "ps":1, "fs":0, "es":0, "ttd":[ { "tt":null, "ps":1, "fs":0, "es":0 } ] } ] }, { "un":"user2", "ps":1, "fs":3, "es":5, "ad":[ { "at":"asset2", "ps":1, "fs":3, "es":5, "ttd":[ { "tt":null, "ps":1, "fs":3, "es":5 } ] } ] } ] }, { "cn":"cust-3", "s":"api", "ps":0, "fs":1, "es":0, "ud":[ { "un":"user-3", "ps":0, "fs":1, "es":0, "ad":[ { "at":"asset2", "ps":0, "fs":1, "es":0, "ttd":[ { "tt":null, "ps":0, "fs":1, "es":0 } ] } ] } ] }, { "cn":"cust-4", "s":"api", "ps":1, "fs":4, "es":22, "ud":[ { "un":"user-4", "ps":1, "fs":4, "es":22, "ad":[ { "at":"asset1", "ps":1, "fs":4, "es":22, "ttd":[ { "tt":null, "ps":1, "fs":4, "es":22 } ] } ] } ] } ] }

I want the output to be like:

cn	un	at	tt	ps	fs	es
cust-1	user-0	asset2	null	61	94	142
cust-2	user-6 user1 user2	asset1 asset2 asset1 asset2	null null null null	722 3 1 1	149 0 0 3	20 1 0 5
cust-3	user-3	asset2	null	0	1	0
cust4	user-4	asset1	null	1	4	22

Thanks in advance.

Re: Create Splunk Table from nested json output

ITWhisperer — Fri, 08 Jan 2021 08:24:54 GMT

| makeresults | eval _raw="{ \"cd\":[ { \"cn\":\"cust-1\", \"s\":\"api\", \"ps\":61, \"fs\":94, \"es\":142, \"ud\":[ { \"un\":\"user-0\", \"ps\":61, \"fs\":94, \"es\":142, \"ad\":[ { \"at\":\"asset2\", \"ps\":61, \"fs\":94, \"es\":142, \"ttd\":[ { \"tt\":null, \"ps\":61, \"fs\":94, \"es\":142 } ] } ] } ] }, { \"cn\":\"cust-2\", \"s\":\"api\", \"ps\":727, \"fs\":152, \"es\":26, \"ud\":[ { \"un\":\"user-6\", \"ps\":725, \"fs\":149, \"es\":21, \"ad\":[ { \"at\":\"asset1\", \"ps\":722, \"fs\":149, \"es\":20, \"ttd\":[ { \"tt\":null, \"ps\":722, \"fs\":149, \"es\":20 } ] }, { \"at\":\"asset2\", \"ps\":3, \"fs\":0, \"es\":1, \"ttd\":[ { \"tt\":null, \"ps\":3, \"fs\":0, \"es\":1 } ] } ] }, { \"un\":\"user1\", \"ps\":1, \"fs\":0, \"es\":0, \"ad\":[ { \"at\":\"asset1\", \"ps\":1, \"fs\":0, \"es\":0, \"ttd\":[ { \"tt\":null, \"ps\":1, \"fs\":0, \"es\":0 } ] } ] }, { \"un\":\"user2\", \"ps\":1, \"fs\":3, \"es\":5, \"ad\":[ { \"at\":\"asset2\", \"ps\":1, \"fs\":3, \"es\":5, \"ttd\":[ { \"tt\":null, \"ps\":1, \"fs\":3, \"es\":5 } ] } ] } ] }, { \"cn\":\"cust-3\", \"s\":\"api\", \"ps\":0, \"fs\":1, \"es\":0, \"ud\":[ { \"un\":\"user-3\", \"ps\":0, \"fs\":1, \"es\":0, \"ad\":[ { \"at\":\"asset2\", \"ps\":0, \"fs\":1, \"es\":0, \"ttd\":[ { \"tt\":null, \"ps\":0, \"fs\":1, \"es\":0 } ] } ] } ] }, { \"cn\":\"cust-4\", \"s\":\"api\", \"ps\":1, \"fs\":4, \"es\":22, \"ud\":[ { \"un\":\"user-4\", \"ps\":1, \"fs\":4, \"es\":22, \"ad\":[ { \"at\":\"asset1\", \"ps\":1, \"fs\":4, \"es\":22, \"ttd\":[ { \"tt\":null, \"ps\":1, \"fs\":4, \"es\":22 } ] } ] } ] } ] }" | spath path=cd{} output=cd | fields - _* | mvexpand cd | spath input=cd path=ud{} output=ud | rex field=cd "\"cn\":\"(?<cn>[^\"]+)" | fields - cd | mvexpand ud | spath input=ud path=ad{} output=ad | rex field=ud "\"un\":\"(?<un>[^\"]+)" | fields - ud | mvexpand ad | spath input=ad path=ttd{} output=ttd | rex field=ad "\"at\":\"(?<at>[^\"]+)" | fields - ad | spath input=ttd | fields - ttd | table cn un at tt ps fs es

Re: Create Splunk Table from nested json output

niddhi — Fri, 08 Jan 2021 19:26:46 GMT

Saved my day. This works perfectly. Thank you so much!!