SPL: How to calculate the average user events per unique user, per day over a 14 day period (exclude weekends)?

[mjuestel2]

All,

I need some help on a problem I am trying to solve.

Problem: I need to calculate the average user events per unique user, per day over a 14 day period (excluding weekends).

Basically, we have users logging into a system and I want to see if a threshold of say 10% or more is reached that is outside of the norm for a particular user.

The output would then list the username who is in violation of the above.

Thanks for any guidance...

[gcusello]

Hi @mjuestel2,

at first, are youspeaking of windows events or what else?

in other words, did you identified the login condition? e.g. for windows it's EventCode=4624.

Then you have to identify the user field (e.g. in windows is Account_name).

If yes, you have to run something like this:

index=wineventlog EventCode=4624 NOT (time_wday="saturday" OR time_wday="sunday") earliest=-14d@d latest=now
| stats count BY Account_name

 If in your events there isn't the field time_wday, you have to extract it and run something like this:

index=wineventlog EventCode=4624 earliest=-14d@d latest=now
| eval time_wday=strftime(_time,"%A")
|search NOT (time_wday="saturday" OR time_wday="sunday") 
| stats count BY Account_name

Ciao.

Giuseppe

[mjuestel2]

I'm not using Windows events - but I was able to get your query to run.

Mine looks more like:

index=stuff sourcetype=more:stuff  InterestingField=authorized earliest=-14d&d latest=now
| eval time_wday=strftime(_time,"%A")
|search NOT (time_wday="saturday" OR time_wday="sunday")
| stats count BY User_Name


Results are:

User_Name                          Count 

User1                                      600
User2                                         55
User3                                    4321

etc.

If we use User1 where a count of 600 is returned - we can assume over the last 14 days (excluding weekends) - the user logged-on 60 times per day.

I want to trigger an event if that user now exceeds 10% of his daily count. So... 66 events per day or more.

My thoughts are I can show those users based on the following:

| where count>=1.1*dailyAverage

I'm just missing the dailyAverage part.

[gcusello]

Hi @mjuestel2,

please try something like this:

index=stuff sourcetype=more:stuff  InterestingField=authorized earliest=-14d&d latest=now
| eval time_wday=strftime(_time,"%A"), time_day=strftime(_time,"%Y-%m-%d")
| search NOT (time_wday="saturday" OR time_wday="sunday")
| eventstats count AS total BY user
| stats values(total) as total count BY User_Name time_day
| eval dailyAverage=count/total*100
| eval check=if(count>=1.1*dailyAverage,"OK","NOK")

Ciao.

Giuseppe