topic Re: How do I sum similarly named fields from nested JSON? in Splunk Search

How do I sum similarly named fields from nested JSON?

unclethan — Tue, 09 Aug 2016 23:52:42 GMT

I have JSON events with a sub list and want to sum similarly named fields for each event.

{ "id": "theid", "subdata": [ { "subname": "s1", "key1_foo": 10, "key1_bar": 12, "key2_foo": 100, "key2_bar": 101}, {"subname": "s2", "key1_foo ....

I would like to chart max(sum_of_key1*), max(sum_of_key2*) by the id
and also by subname.

Any info would be appreciated.

Thanks

Re: How do I sum similarly named fields from nested JSON?

jkat54 — Wed, 10 Aug 2016 01:18:50 GMT

This is my go to article for this use case:

https://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Spath#Example_3:_Extract_and_expand_JSON_events_with_multi-valued_fields

Re: How do I sum similarly named fields from nested JSON?

javiergn — Wed, 10 Aug 2016 10:54:55 GMT

Is this what you are looking for? If not please post a few samples and a more detailed JSON file.

your base search here
| spath input=yourJsonField
| stats sum(subdata*) as subdata* by id
| stats max(subdata*) as subdata*

Example with some data:

| makeresults
| eval _raw = "
{ \"id\": \"theid\", \"subdata\": [ 
  { \"subname\": \"s1\", \"key1_foo\": 10, \"key1_bar\": 12, \"key2_foo\": 100, \"key2_bar\": 101}, 
  {\"subname\": \"s2\", \"key1_foo\": 20, \"key1_bar\": 24, \"key2_foo\": 200, \"key2_bar\": 202}
]
}
"
| spath input=_raw
| stats sum(subdata*) as subdata* by id
| stats max(subdata*) as subdata*

Output:

Re: How do I sum similarly named fields from nested JSON?

unclethan — Tue, 29 Sep 2020 10:34:50 GMT

Sorry I wasn't clear.
Your example gives a result per key1_* field, I need a sum over all the key1_fields, named key1, and the same for key2.

Re: How do I sum similarly named fields from nested JSON?

sundareshr — Wed, 10 Aug 2016 20:25:45 GMT

If you have your data indexed as JSON, and all fields are extracted, this shoud work

| rename subdata{}.* as *
| stats sum(key1_*) as sum_key1_* sum(key2_*) as sum_key2_*

Re: How do I sum similarly named fields from nested JSON?

unclethan — Wed, 10 Aug 2016 20:31:31 GMT

as I mentioned above, the summation is meant to be across similar keys within the json object, not per-key, across events.

Re: How do I sum similarly named fields from nested JSON?

jkat54 — Wed, 10 Aug 2016 22:05:43 GMT

Replace the ... with the root searches:

...
| spath 
| rename subdata{}.key1* as key1*
| stats sum(key1*) as key1* 
| addtotals key1* 
| append 
 [ 
 search ... 
 | spath 
 | rename subdata{}.key2* as key2*
 | stats sum(key2*) as key2* 
 | addtotals key2* 
 ] 
| transpose