Splunk Search

avg of number of events

pinzer
Path Finder

Hi all, i need to count the event of today and compare with the average of the last month daily count by dest. I'm using a query like this that separate the ip's and now i have to show the average of count by the same dest

eventtype="searchIPS1" DestinationIP!="N/A" Severity="Medium" |eval dest=case(DestinationIP=="1.1.1.1", "sshDMZ", DestinationIP=="1.1.1.2", "sshDMZ", (DestinationIP!="1.1.1.1" AND DestinationIP!="1.1.1.2"), "Others") | stats last(count) as today_count avg(count) as avg_count

for example:

dest | today_count | avg_count
sshDMZ | 8 | 5,67
others | 7 | 9,89

thanks to all who can help me

Tags (2)
0 Karma

David
Splunk Employee
Splunk Employee

I spent quite a while finding how to do this myself. I think the following would do what you need:

eventtype="searchIPS1" DestinationIP!="N/A" Severity="Medium" earliest=-30d@d latest=@d
  | eval dest=case(DestinationIP=="1.1.1.1", "sshDMZ", DestinationIP=="1.1.1.2", "sshDMZ", 
        (DestinationIP!="1.1.1.1" AND DestinationIP!="1.1.1.2"), "Others")
  | bin _time span=1d
  | stats max(count) as PerDay by _time dest
  | stats avg(PerDay) as MonthlyAverage by dest
  | fields MonthlyAverage dest
  | join type=outer dest 
     [search eventtype="searchIPS1" DestinationIP!="N/A" Severity="Medium" earliest=@d latest=now 
     |eval dest=case(DestinationIP=="1.1.1.1", "sshDMZ", DestinationIP=="1.1.1.2", "sshDMZ", 
          (DestinationIP!="1.1.1.1" AND DestinationIP!="1.1.1.2"), "Others") 
     | bin _time span=1d 
     | stats last(count) as Today by dest 
     | fields Today dest
     ]

Or generically:

#YourSearchHere# earliest=-30d@d latest=@d 
| bin _time span=1d 
| stats #PerDayStats# as PerDay by _time #SplittingField#  
| stats avg(PerDay) as MonthlyAverage by #SplittingField#  
| fields MonthlyAverage #SplittingField#  
| join type=outer #SplittingField# 
    [
     search #YourSearchHere# earliest=@d latest=now 
     | bin _time span=1d 
     | stats #Today'sStats# as Today by #SplittingField# 
     | fields Today #SplittingField#
    ]

Essentially, the above does your search for the Monthly Average first, leaves only the two relevant fields (dest and MonthlyAverage), then joins it to a second search for Today's values, based on the dest.

There are a couple of other ways to do it that have come up in Splunk Answers, so one of them may be better, but give the above a shot.

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 ...