Splunk Search

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

New Member

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

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

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

New Member

Perfect! Thank you!

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!