Help with splunk query to find login events that happen at a strange time per user

[brucewhaleham21]

Working on a splunk query to find login events that occur outside of the users' typical sign in times. I do not want to get an average of all users, just the upper and lower bounds of each individual and then determine if a login event is an outlier

index=o365 sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn | eventstats avg("_time") AS avg stdev("_time") AS stdev | foreach UserID eval lowerBound=(avg-stdev*exact(2)), upperBound=(avg+stdev*exact(2)) | eval isOutlier=if('_time' < lowerBound OR '_time' > upperBound, 1, 0) | search isOutlier=1

[gcusello]

Hi @brucewhaleham21 ,

some question to better understand your requirements:

• are lowerBound and upperBount different for each user or are always the same?
• did you consider holydays and weekends or only working hours?
• why did you calculate strdev for _time, isn't a fixed requisite?

Anyway, if you have a fixed value for each user you can put these values in the main search (if lowerBound is 8 and upperBound is 18):

index=o365 sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn time_hour<8 time_hour>18

If you have to consider also holydays and weekends, you have to prepare a lookup containing holydays and prepare a more complex search that you can find at https://community.splunk.com/t5/Splunk-Search/How-to-write-query-for-including-non-business-hours-an...

Ciao.

Giuseppe

[brucewhaleham21]

lowerBound and upperBound should be different for each user. I was hoping to get those values as an average over 30 day period but I guess I would only want to evaluate that on weekdays and not weekends. Having strdev calculated was just to assist with determining the upper and lower bound. Ideally, the upper and lower bound would be calculated for each user(over a 30 day period) and then every logon event would be evaluated according to those upper and lower bounds. The weekday vs weekend problem could be ignored and just time would be enough for me

[gcusello]

Hi @brucewhaleham21 ,

your requirement isn't so clear for me because working hours shoudl be a contractual requirement!

Anyway, you could consider the hours of upper and lower bound in this way:

index=o365 sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn 
| eval hour=strftime(_time,"%H")+strftime(_time,"%M")/60
| eventstats avg(hour) AS avg stdev(hour) AS stdev 
| eval lowerBound=(avg-stdev), upperBound=(avg+stdev) 
| eval isOutlier=if(hour<lowerBound OR hour> upperBound, 1, 0) 
| search isOutlier=1

Anyway, there are some errors in your search:

• "_time" '_time' are a string not a field,
• you cannot compare an epochtime with a day time,
• foreach has a different syntax,
• what's exacts? it isn't defined.

Ciao.

Giuseppe