topic Re: Grouping and counting items based on values in the list in Splunk Search

Grouping and counting items based on values in the list?

ghostrider — Fri, 14 Oct 2022 14:57:39 GMT

I have below JSON event where there are errors present in a field which is a list. I want to extract the values in the list and group them with another field which is part of an object of the same event. After grouping I want to count them like below output. I am using below query but not getting the expected output. Any help on this will be highly appreciated.

Sample JSON Event1

{ "errorList": ["There is an ErrorA", "There is some other ErrorB", "Ohh another ErrorC"], "Details": { "type": "ABC" } }

Sample JSON Event2

{ "errorList": ["There is some other ErrorB", "Ohh another ErrorC"], "Details": { "type": "XYZ" } }

Expected Output

Type Error Count ABC There is some other ErrorB 3 ABC There is an ErrorA 4 XYZ Ohh another ErrorC 2

Query I am trying

BASE_SEARCH | rex field=MESSAGE "(?<JSON>\{.*\})" | spath input=JSON | rename Details{}.type as "Type" | rename errorList{} as "Error" | stats count as Count by "Type" "Error" | table Type, Error , Count

Re: Grouping and counting items based on values in the list

johnhuang — Fri, 14 Oct 2022 14:58:20 GMT

You just need to expand the multivalue field into its own events.

BASE_SEARCH
| rex field=MESSAGE "(?<JSON>\{.*\})"
| spath input=JSON
| rename Details{}.type as "Type"
| rename errorList{} as "Error"
| mvexpand Error
| stats count as Count by "Type" "Error"
| table Type, Error , Count

Re: Grouping and counting items based on values in the list

ghostrider — Fri, 14 Oct 2022 19:25:25 GMT

Thanks!!! I ran the above query but it shows no result. Do I need to change the order of mvexpand or need to add some other method altogether?

Re: Grouping and counting items based on values in the list?

yuanliu — Sat, 15 Oct 2022 01:26:13 GMT

Your data doesn't have a field named Details{}.type because Details is not an array. Use Details.type will solve your problem.

BASE_SEARCH | rex field=MESSAGE "(?<JSON>\{.*\})" | spath input=JSON | rename Details.type as "Type" | rename errorList{} as "Error" | stats count as Count by "Type" "Error" | table Type, Error , Count

With the sample JSON events you give, the output is

Type	Error	Count
ABC	Ohh another ErrorC	1
ABC	There is an ErrorA	1
ABC	There is some other ErrorB	1
XYZ	Ohh another ErrorC	1
XYZ	There is some other ErrorB	1

The output table you listed is not from those two datapoints. If you want to tally by Error alone, the search should be

BASE_SEARCH | rex field=MESSAGE "(?<JSON>\{.*\})" | spath input=JSON | rename Details.type as "Type" | rename errorList{} as "Error" | stats values(Type) as Type count as Count by "Error" | table Type, Error , Count

With this, you'll get something like

Type	Error	Count
ABC XYZ	Ohh another ErrorC	2
ABC	There is an ErrorA	1
ABC XYZ	There is some other ErrorB	2