Splunk Search

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

mkrauss1
Explorer

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?

1 Solution

somesoni2
Revered Legend

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

View solution in original post

somesoni2
Revered Legend

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

mkrauss1
Explorer

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

0 Karma

ShaneNewman
Motivator

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]
0 Karma

mkrauss1
Explorer

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

0 Karma

ShaneNewman
Motivator

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?

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...