Detecting Spikes/Anomalies in Failed Logins - over time

[cyberdiver]

My goal is to calculate a score of confidence based on how anomalous the amount of failed logins is compared to activity over a 30 day period.  Then, I want to sort those scores in a table showing the users, maybe the relative time of spike, and the average number of failed logins at that time.  That way I can tune thresholds and what not.

This is what I've tried up until now.  Even some "pseudocode" would help here.  I understand that the way these commands output with the pipes might be the problem too.

|from datamodel:Authentication.Authentication  
| search action="failure" 
| timechart span=1h count as num_failures by user  
| stats avg(num_failures) as avg_fail_num 
| trendline sma5(avg_fail_num) as moving_avg_failures
| eval score = (avg_fail_num/(moving_avg))  
| table user, avg_fail_num, moving_avg, score  
| sort – score 

The score variable is supposed to increase the larger the fail_num is compared to the moving_avg -- which should show me a confidence score on spikes.  This should help me quantify it also for more analysis opportunities.

Also, I should clarify that I want this to detect users who specifically have activity not like their normal activity, and also when failed logins go over a certain number.  In other words, not the outliers in the big picture of failed logins but rather when a user is acting weird and there is a huge increase in failed logins for that specific user.

I want to be able to apply this query's structure to other situations.

Here are some of my other iterations/attempts at trying to do this (all with separate issues):

Using bin to get average per hour:

|from datamodel:Authentication.Authentication   
| search action="failure"  
| bin _time span=1h  
| stats count as fail_num by user, _time 
| stats avg(fail_num) as avg_fail_num by user  
| trendline sma24(avg_fail_num) as moving_avg 
| eval moving_avg=moving_avg*3 
| eval score = (avg_fail_num/(moving_avg))  
| table user, _time, fail_num, avg_fail_num, moving_avg, score  
| sort – score 

Making a time variable to separate hours:

|from datamodel:Authentication.Authentication   
| search action="failure" 
| regex user="^([^\s]*)$"    
| eval date_hour=strftime(_time,"%H") 
| stats count as fail_num by user, date_hour 
| stats avg(fail_num) as avg_fail_num by user, date_hour 
| trendline sma24(avg_fail_num) as moving_avg 
| eval moving_avg=moving_avg*1 
| eval score = (avg_fail_num/(moving_avg))  
| table user, date_hour, fail_num, avg_fail_num, moving_avg, score  
| sort – score