How do I edit my search to find the monthly average to compare against daily totals?

user12345a_2 — Wed, 08 Jun 2016 12:14:40 GMT

So I have the following search/report that I run daily:

index=os_linux NOT root tag=authentication NOT tag=failure | stats count by host, user, eventtype, src_ip | search eventtype=*authentication* | eval dest_count=host+":"+src_ip+"("+count+")" | stats values(dest_count) AS Daily by user, eventtype

which generates output similar to the following:

user        eventtype               Daily
----        ---------               -----
user1   sshd_authentication  system5.xyz.com:192.168.1.2(1)
user2    sshd_authentication     system12.xyz.com:192.168.1.42(2)
                                 system24.xyz.com:192.168.1.29(12)
user3   sshd_authentication  system15.xyz.com:192.168.1.24(7)

I want to modify this to add a column to give me the 30-day average for each user on the respective system. A sample output would be (or something similar):

    user        eventtype               Daily                          Avg
    ----        ---------               -----                         -----
    user1   sshd_authentication  system5.xyz.com:192.168.1.2(1)      5
    user2    sshd_authentication     system12.xyz.com:192.168.1.42(2)    3
                                     system24.xyz.com:192.168.1.29(42)   1
    user3   sshd_authentication  system15.xyz.com:192.168.1.24(7)    7

I'm having trouble getting this to work. Can anyone offer any suggestions on what the best way would be to accomplish this? Thank you.

Re: How do I edit my search to find the monthly average to compare against daily totals?

justinatpnnl — Thu, 09 Jun 2016 01:01:19 GMT

This one is a little tricky because you need to search a month of data in order to get the average, but still process daily results as well. Here was my stab at it, searching over the "Last 30 Days":

index=os_linux NOT root tag=authentication NOT tag=failure 
| bucket _time span=1d 
| stats count(eval(_time=relative_time(now(),"@d"))) as count, count as total by host, user, eventtype, src_ip 
| search eventtype=*authentication*
| eval dest_count=host+":"+src_ip+"("+count+")", average=total/30
| stats values(dest_count) as Daily, values(average) as Average by user, eventtype

By bucketing the _time in 1 day increments, we can grab just the count of today's events and the total count. Will this do the trick for you?

topic How do I edit my search to find the monthly average to compare against daily totals? in Splunk Search

How do I edit my search to find the monthly average to compare against daily totals?

Re: How do I edit my search to find the monthly average to compare against daily totals?