avg of number of events

[pinzer]

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

[David]

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.