How to get an alert if a user doesn't have a specific event in a 90 day timeframe?

[Virpee]

We have a use case where we need to have an alert emailed if a user (under the field User) does not have an event of Activity="logged on" within the past 90 days within a specific sourcetype.

We have tried
index=index sourcetype=sourcetype Activity="logged on"
| chart count over Activity by User limit=0

But we can't seem to be able to filter to only specify a count of 0 over the past 90 days

Any ideas or leads as to what would get us in the right direction?

[bowesmana · Accepted solution]

Naturally if there are no events, you will never get a count of 0 for a user./ The standard way of looking for things that have NOT happened in Splunk is to do this type of logic

index=index sourcetype=sourcetype Activity="logged on"
| stats count over by User 
| append [
  | inputlookup list_of_ALL_users.csv
  | fields User
  | eval count=0
]
| stats max(count) as count by User
| where count=0

You need to have the complete list of users maintained somewhere to know what users you are expecting to see, so this first counts the users that have logged on through data, then adds all the users from the CSV file and then looks for those users who have a count of 0

Note that looking back over 90 days may be an expensive search if you are running it regularly - if that is an issue, consider doing a daily summary of users who have logged on and then your search will be much faster.

[ITWhisperer]

Splunk is good for finding things that have happened (in the logs), it is not that good at finding things that haven't happened (or at least there is no evidence in the logs that they have). What you could try is looking back over the last 100 days for example, and finding when the last event occurred for each user and seeing if that is over 90 days old.

Alternatively, if you know all the users you are interested in, count the number of events for those users over the last 90 days and add it to zero for each of the users, and if it is still zero, there isn't any activity in the index for those users.