Seeking Advice: Crafting a Splunk Query for Identifying Dormant Systems from Windows Event Logs (EventCode=4624)

[KingUs80]

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.

[KingUs80]

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

[dtburrows3]

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