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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...