Compare search results to current data

[sfefcu]

I have a search that looks at AD data to determine if a user was disabled more than 6 months ago.  My intention was for the search to only produce results if the user was disabled more than 6 months ago, AND the user is still present in AD.  The the results would remind us to remove those users if they haven't already been removed.  

The problem is that the results include users that were already deleted from AD.  I would ideally like to take the results and compare them to the latest import of data from AD (which happens once a day).  If the user is not present in the latest import, then we can assume the user was already removed, and that user should not be in the results.  

Thanks for your help.  Feel free to suggest any optimizations to my search.

index="msad" sourcetype="msad:users" Enabled!=""
| streamstats current=f last(Enabled) as new_Enabled last(_time) as time_of_change by SamAccountName
| where Enabled!=new_Enabled
| convert ctime(time_of_change) as time_of_change 
| rename Enabled as old_Enabled
| dedup SamAccountName
| where new_Enabled="False" AND strptime(time_of_change, "%m/%d/%Y %H:%M:%S")<relative_time(now(),"-180d@d") 
| table time_of_change, SamAccountName, old_Enabled , new_Enabled, DistinguishedName, AccountExpirationDate

[tscroggins · Accepted solution]

@sfefcu 

It may be as simple as adding a subsearch to your base search to constrain the results to existing users:

index="msad" sourcetype="msad:users" Enabled!="" 
    [ search index="msad" sourcetype="msad:users" earliest=-1d latest=now 
    | table SamAccountName ] 
| streamstats current=f last(Enabled) as new_Enabled last(_time) as time_of_change by SamAccountName 
| where Enabled!=new_Enabled 
| convert ctime(time_of_change) as time_of_change 
| rename Enabled as old_Enabled 
| dedup SamAccountName 
| where new_Enabled="False" AND strptime(time_of_change, "%m/%d/%Y %H:%M:%S")<relative_time(now(),"-180d@d") 
| table time_of_change, SamAccountName, old_Enabled , new_Enabled, DistinguishedName, AccountExpirationDate

In a past life as an AD admin, I would perform this task using LDAP queries to find disabled accounts with a lastLogon value more than six months ago. Our policies were driven by user activity (last login), though, and not administrator activity (last account modification). I.e. Disable after 30 days of inactivity and delete after 180 days of inactivity. To keep it in Splunk:

| ldapsearch search= 
    [| makeresults
        ``` 10000000: units in 100 nanosecond intervals ```
        ``` 11644473600: seconds between 1601-01-01 and 1970-01-01 ``` 
    | eval lastLogon=round(10000000 * (11644473600 + relative_time(now(), "-6mon")), 0) 
    | eval search="\"(&(objectCategory=person)(sAMAccountName=*)(userAccountControl:1.2.840.113556.1.4.803:=2)(lastLogon<".lastLogon."))\""]

Note that lastLogon is not indexed by default. I'm also dating myself by using a filter that pre-dates objectClass being indexed by default.

You can use e.g. https://www.epochconverter.com/ldap to verify the date arithmetic.

[sfefcu]

Thanks @tscroggins, so simple, yet it eluded me.  Your solution solved it.