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     6
-150     7
-200     6
-250     7
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?
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
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
Wow, that's a tough query, thanks for this!
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]
Thanks for this. ES2=-150 and -250 is missing, how i can i group them too?
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?