Splunk Search

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

KingUs80
New Member

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.

Labels (1)
0 Karma

KingUs80
New Member

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

0 Karma

dtburrows3
Builder

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

dtburrows3_0-1702489107723.png

 




0 Karma
Get Updates on the Splunk Community!

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...