Splunk Search

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

mjuestel2
Path Finder

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...

Labels (2)
Tags (2)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

mjuestel2
Path Finder

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.

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...