Splunk Search

How to perform aggregations on multiple occurrences of a field inside a single event

hsharma20
Engager

Hi,
I have data something like this:

Events in splunk search are as follows

 04:30            [timestamp] [text] type = a count = 10
                  [timestamp] [text] type = a count = 30

04:31           [timestamp] [text] type = a count = 30 
                [timestamp] [text] type = a count = 20

when I run the following query
"index = test source=".log" "type = a" count
| stats avg(count)

I get (10 + 30)/2 = 20
But I need to get (10+30+30+20) /4 = 45 (irrespective of events)

Can anyone help me on this ?
Thanks

Tags (1)

DalJeanis
SplunkTrust
SplunkTrust

Stats works just fine if the values are in a multivalue field by that name. This probably means that your search time extractions haven't been properly set up to deal with the fact that there can be more than one.

Try this ..

 your base search
| rex max_match=0 "type =\s+(?<mytype>\S+)\s+count = (?<mycount>\d+)"
| eventstats avg(mycount) as overal_average

That will give you the overall average of all lines - which in this case is 22.5.

Here's a run-anywhere sample that shows that

| makeresults count=2
| streamstats count as recno
| eval _raw=if(recno=1,
     " blah blah type = a count = 10 blah blah type = a count = 30", 
     " blah blah type = a count = 30   blah blah type = a count = 20")
| rex max_match=0 "type =\s+(?<mytype>\S+)\s+count = (?<mycount>\d+)"
| eventstats avg(mycount) as overall_average

Now, if you want to FIRST sum up all the counts per record, then average the records, then you need to do those steps in order.

| makeresults count=2 
| streamstats count as recno
| eval _raw=if(recno=1,
     " blah blah type = a count = 10 blah blah type = a count = 30", 
     " blah blah type = a count = 30   blah blah type = a count = 20")
| rex max_match=0 "type =\s+(?<mytype>\S+)\s+count = (?<mycount>\d+)"

| streamstats count as recno
| eventstats sum(mycount) as line_sum by recno
| eventstats avg(line_sum) as overall_average

Once you are satisfied that your system is calculating correctly, then change the eventstats to stats and all the detail lines will go away.

0 Karma

hsharma20
Engager

Thank you very much @DalJeanis, Now I am able to get all the values. But from what I see is that the event is returning event list than a table like stats.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...