Count of users in a month who cross a threshold of usage?

[MichaelCohen829]

Hello Splunk Community,

I am trying to answer this question: How many users have logged into the system on at least 4 separate days during the last 30 days?

So far I’ve generated this search:

sourcetype="LogFile" ActionType="Login” earliest=-30d@d | chart dc(StartTime) as xcount by UserId | where xcount>4 | stats count as "Users >= 4 logins"

The issue that I’m facing is that I cannot determine how to count Logins by day. My current Search simply counts number of Logins although those logins may have all occurred on the same day, but I want to find out how many have logged in on at least 4 separate days.

Our StartTime data element is in the following format: dd/mm/yy H:M:S. I can truncate the StartTime down to dd-mm-yy with the following eval statement: eval StartTime=strptime(StartTime, "%Y-%m-%dT%H:%M:%S")|eval StartTime=strftime(StartTime,"%d-%b-%Y"). However, I cannot figure out how get the login counts associated with unique days. i.e: I have not yet been successful in moving the truncated date into my Search.

Thank you,

Mike

[martin_mueller · Accepted solution]

You can count by day like this:

sourcetype=logfile actiontype=login | eval start_epoch = strptime(StartTime, "time format goes here") | bin span=1d start_epoch | stats dc(start_epoch) as xcount by UserID | where xcount>4 | stats count as "Users >= 4 logins"

Note, if StartTime is also stored in _time then you can skip the eval and use _time instead.

[MichaelCohen829]

Thank you Martin! This answer was very helpful