topic Re: How to merge and group multiple key-value pairs, count the values, and table the results? in Splunk Search

How to merge and group multiple key-value pairs, count the values, and table the results?

mkrauss1 — Mon, 22 Sep 2014 20:06:08 GMT

I try hard to group multiple key/values from a single record, then count the values and print them in a table.
Say i have this:

EC1=1 ES1=-100 EC2=1 ES2=-150
EC1=1 ES1=-100 EC2=1 ES2=-150
EC1=2 ES1=-100 EC2=2 ES2=-150
EC1=2 ES1=-100 EC2=3 ES2=-150
EC1=1 ES1=-200 EC2=1 ES2=-250
EC1=2 ES1=-200 EC2=2 ES2=-250
EC1=2 ES1=-200 EC2=2 ES2=-250
EC1=2 ES1=-200 EC2=3 ES2=-250

Means:

ES1/2 are status codes

EC2/1 are counter values

Now i'd like to group them into summaries, ES (ErrorStatus) and EC (Error Count).
Trying to get:

Status Count
-100&nbsp&nbsp&nbsp&nbsp&nbsp6
-150&nbsp&nbsp&nbsp&nbsp&nbsp7
-200&nbsp&nbsp&nbsp&nbsp&nbsp6
-250&nbsp&nbsp&nbsp&nbsp&nbsp7

I'd try to start with the stats and count function

| stats count as Total count(ES1) as ESS1 count sum(EC1) as ECC1 count(ES2) as ESS2 count sum(EC2) as ECC2 by ES1,ES2

But that doesnt take me any further, any ideas?

Re: How to merge and group multiple key-value pairs, count the values, and table the results?

ShaneNewman — Mon, 22 Sep 2014 21:26:17 GMT

The easiest way to do this is with a subsearch:

index=your_index sourcetype=your_sourcetype | stats sum(EC1) as count by ES1 | rename ES1 AS Status | append [ search index=your_index sourcetype=your_sourcetype | stats sum(EC2) as count by ES2 | rename ES2 AS Status]

Re: How to merge and group multiple key-value pairs, count the values, and table the results?

mkrauss1 — Mon, 22 Sep 2014 21:33:51 GMT

Thanks for this. ES2=-150 and -250 is missing, how i can i group them too?

Re: How to merge and group multiple key-value pairs, count the values, and table the results?

somesoni2 — Mon, 22 Sep 2014 22:42:20 GMT

Try this

 you base search | rex max_match=0 "EC\d+=(?<EC>[^ ]+)" | rex max_match=0 "ES\d+=(?<Status>[^ ]+)" | table EC Status | eval temp=mvzip(EC,Status ,"#") | table temp | mvexpand temp | rex field=temp "(?<EC>.*)#(?<Status>.*)" | fields - temp | stats sum(EC) as Count by Status

Re: How to merge and group multiple key-value pairs, count the values, and table the results?

ShaneNewman — Mon, 22 Sep 2014 22:50:52 GMT

That is strange, they are showing up in my search... Do you see ES2 values being extracted on the right hand side under interesting fields?

Re: How to merge and group multiple key-value pairs, count the values, and table the results?

mkrauss1 — Tue, 23 Sep 2014 10:32:49 GMT

Wow, that's a tough query, thanks for this!