Dynamically generate user login failures

[oanyanetu]

Hello.

I am trying to compute a user's login failure rate so I can pit that against their daily failure rate to see if they're staying within their expected behavior. 

The problem is that a user may not login everyday so something like

...  | eval av=(FailedLogin/7)

won't work if a user does not login everyday.

Is there a way to say

|eval _counter="0"
|eval if(FailedLoginForThisDay!="0") counter = counter + 1
|span -1d
|eval avg = (FailedLoginTotal/counter)
|eval if(avg < FailedLoginForThisDay) stats count by user, ipaddr, etc

where I am saying iterate over a set of days and determine if a user had a failed login. If so increment the counter, do it in 1 day buckets (is there a better way?) then finally take my counter, find my true average for failed logins and if they are above their average print out their username, ip, and other stats.

[t_shreya]

Hi,
I think the following query may help.

...| bin span=1d _time 
| eventstats count as FailedLoginForThisDay by user,_time 
| eventstats avg(FailedLoginForThisDay) as avg_failed_logins by user
| where avg_failed_logins < FailedLoginForThisDay
| table user, ipaddr, ...

where,

bin span=1d _time

 would help divide the data into 1 day buckets,

eventstats count as FailedLoginForThisDay by user,_time

 would calculate the number of failed logins per day and user

eventstats avg(FailedLoginForThisDay) as avg_failed_logins by user

 would calculate the average failed logins per user

Thanks.