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 th...
See more...
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