topic Re: Reading complexed nested Json in Splunk Search

Having trouble reading complexed nested Json

chrisboy68 — Wed, 16 Feb 2022 15:50:28 GMT

Hi, struggling trying to count objects in a big json doc. I'm on version 8.0.5, so function json_keys is not available.

{ "0": { "field1": "123" }, "1": { "field2": "123" }, "2": { "field3": "123" }, "3": { "field4": "123" }, "4": { "field5": "123" } }

This is a sample, I am able to get down to the path (startpath) with spath. What I'm trying to do is count the instances of the objects (0,1,2,3,4). I can't cleanly regex backwards as the real values names are not consistent. Thought I could do something like startpath{} and list them out , but the wildcards {} are not working anyway I try it. Thoughts, suggestions?

Thanks

Chris

Re: Reading complexed nested Json

ITWhisperer — Wed, 16 Feb 2022 14:44:32 GMT

Will this work for you?

| makeresults | eval _raw="{\"startpath\": { \"0\": { \"ID\": \"123\" }, \"1\": { \"ID\": \"123\" }, \"2\": { \"ID\": \"123\" }, \"3\": { \"ID\": \"123\" }, \"4\": { \"ID\": \"123\" }, \"4\": { \"ID\": \"123\" } } }" | spath | foreach startpath.*.ID [| eval startpath_<<MATCHSEG1>>_count=mvcount('<<FIELD>>')] | fields - startpath.*.*

Re: Reading complexed nested Json

chrisboy68 — Wed, 16 Feb 2022 14:57:22 GMT

sorry, i made the sample too easy. I updated my sample json. No, I need to count the instance of the object.

Re: Reading complexed nested Json

ITWhisperer — Wed, 16 Feb 2022 15:10:52 GMT

Simplifying doesn't always help - in this instance, your example is not valid JSON format. Please can you update the example with a valid, and possibly more representative example?

Re: Reading complexed nested Json

chrisboy68 — Wed, 16 Feb 2022 15:33:17 GMT

Ok I updated. Just know that the sample is deeply nested and I can get to this object starting with an initial spath.

Re: Reading complexed nested Json

ITWhisperer — Wed, 16 Feb 2022 15:40:18 GMT

| makeresults | eval _raw="{ \"0\": { \"field1\": \"123\" }, \"1\": { \"field2\": \"123\" }, \"2\": { \"field3\": \"123\" }, \"3\": { \"field4\": \"123\" }, \"4\": { \"field5\": \"123\" } }" | spath | foreach *.* [| eval startpath_<<MATCHSEG1>>_count=mvcount('<<FIELD>>')] | stats sum(startpath_*_count) as startpath_*_count

Re: Reading complexed nested Json

chrisboy68 — Wed, 16 Feb 2022 15:54:27 GMT

Neat. Trying to follow. I need to have the total of all, not each count. So, in my example, the total is 5.

Re: Reading complexed nested Json

ITWhisperer — Wed, 16 Feb 2022 16:00:44 GMT

| makeresults | eval _raw="{ \"0\": { \"field1\": \"123\" }, \"1\": { \"field2\": \"123\" }, \"2\": { \"field3\": \"123\" }, \"3\": { \"field4\": \"123\" }, \"4\": { \"field5\": \"123\" } }" | spath | foreach *.* [| eval startpath_count=if(isnull(startpath_count),mvcount('<<FIELD>>'),startpath_count+mvcount('<<FIELD>>'))] | stats sum(startpath_count) as startpath_count

Re: Reading complexed nested Json

chrisboy68 — Wed, 16 Feb 2022 16:36:23 GMT

I'm almost there. Now I need to count by each event, as this is totaling for every single event. Looks like I just need to add a group by in the stats. Thank you!