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
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
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_*
as I mentioned above, the summation is meant to be across similar keys within the json object, not per-key, across events.
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:
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.
This is my go to article for this use case: