I'm currently working on crafting a Splunk Query to identify systems that have been inactive for a specified duration (which can vary based on user requirements). My intention is to utilize "Windows event logs" as the data source, focusing on EventCode=4624. Primarily, I'll be manipulating the default field "_time" as there isn't another relevant field available. I'd appreciate any guidance or suggestions you might have in this regard.
@dtburrows3 Thank you very much for your assistance. The query works perfectly without:
| where 'days_since_last_login'>14
I tried to play with the number of days after > , but it is still failing (returning no events). Other than that, everything works well.
Since you are referencing EventCode=4624 you are looking to use lack of login activity to determine if a system is inactive?
If this is what you are trying to do I think this SPL may do it (provided you have a static threshold to use for time since the last login from a user)
index=<windows_index> sourcetype=WinEventLog signature_id="4624"
| fields + _time, dest, signature_id, user, signature
| stats
values(signature) as signature,
latest(_time) as last_login_epoch
by dest, user
| eval
seconds_since_last_login=now()-'last_login_epoch',
days_since_last_login=round(('seconds_since_last_login'/(60*60*24)), 2),
duration_since_last_login=tostring(seconds_since_last_login, "duration")
``` user exclusion list ```
``` if this list is large then storing results in a lookup or macro may make the most sense ```
```
Example SPL for exclusion using lookup:
| lookup windows_user_exclusion_list user OUTPUT user as exclusion_user
| where isnull(exclusion_user)
| fields - exclusion_user
```
```
Example SPL for exclusion using hardcoded list of users:
| search NOT user IN ("user_1", "user_2", "user_3", ..., "user_n")
```
| eventstats
min(seconds_since_last_login) as latest_login_on_host_by_user_in_seconds
by dest
| eval
last_login_user=if(
'seconds_since_last_login'=='latest_login_on_host_by_user_in_seconds',
'user',
null()
)
| stats
max(last_login_epoch) as latest_login_epoch,
min(latest_login_on_host_by_user_in_seconds) as latest_login_on_host_by_user_in_seconds,
values(last_login_user) as last_login_user
by dest
| eval
days_since_last_login=round(('latest_login_on_host_by_user_in_seconds'/(60*60*24)), 2),
duration_since_last_login=tostring('latest_login_on_host_by_user_in_seconds', "duration")
| convert
ctime(latest_login_epoch) as latest_login_by_user_timestamp
| fields dest, last_login_user, latest_login_by_user_timestamp, days_since_last_login, duration_since_last_login
``` This where clause can be tuned to desired threshold ```
| where 'days_since_last_login'>14
output will look something like this