Splunk Search

How can I perform a count and then average that count?

rcarmack1
Engager

I am investigating failed logins. I need to perform a count of all failed logins by user, take an average of that count, and then only show results that are higher than the average.

index=windows fail*
| stats earliest(_time) as "Earliest Failure", latest(_time) as "Latest Failure", count by user 
| *Need to Average the count here*
| convert ctime("Earliest Failure") ctime("Latest Failure")
| where count > *Average*
| table user "Earliest Failure" "Latest Failure" count 
| sort -count

Thanks in advance!

0 Karma
1 Solution

kmaron
Motivator

try just adding an eventstats

 index=windows fail*
 | stats earliest(_time) as "Earliest Failure", latest(_time) as "Latest Failure", count by user 
 | eventstats avg(count) as "Average"
 | convert ctime("Earliest Failure") ctime("Latest Failure")
 | where count > Average
 | table user "Earliest Failure" "Latest Failure" count 
 | sort -count

View solution in original post

0 Karma

kmaron
Motivator

try just adding an eventstats

 index=windows fail*
 | stats earliest(_time) as "Earliest Failure", latest(_time) as "Latest Failure", count by user 
 | eventstats avg(count) as "Average"
 | convert ctime("Earliest Failure") ctime("Latest Failure")
 | where count > Average
 | table user "Earliest Failure" "Latest Failure" count 
 | sort -count
0 Karma

rcarmack1
Engager

Perfect! Thank you!

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