Dashboard to list all disabled users which are due for deletion

[anil_ec21]

Dear Splunk Users,

I need your help in creating a dashboard to List of disabled users due for deletion.

Here is my logic. Need your assistance.

Query1: To get list of all deleted users
index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4726 Account_Domain=ABC Account_Name!="$" user!="$"
|rename _time as deleted_time, user as deleted_user
| table deleted_time deleted_user
| dedup deleted_time deleted_user
| outputlookup deletedusers.csv

Query2: To get list of all disabled users
index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4725 Account_Domain= ABC Account_Name!="$" user!="$"
|rename _time as disabled _time, user as disabled _user
| table disabled_time disabled_user
| dedup disabled_time disabled_user
| outlookup disabledusers.csv

Query3: To get list of all re-enabled users
index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4722 Account_Domain= ABC Account_Name!="$" AND (user="user in *disabledusers.csv" AND _time 'NOT OLDER THAN'< disabled_time)
|rename _time as enabled_time, user as enabled_user
| table enabled_time enabled_user
| dedup enabled_time enabled_user
| outputlookup reenabledusers.csv

Query4: To check disabled users are in deleted users list
index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4725 Account_Domain= ABC Account_Name!="$" (user!="$" AND user!="user in deletedusers.csv" AND user!="user in reenabledusers.csv" )
| rename _time as new_disabled_time, user as new_disabled_user
| table new_disabled_time new_disabled_user
| dedup new_disabled_time new_disabled_user
| outlookup new_disabledusers.csv

Query5: For Dashboard query
| inputlookup new_disabledusers.csv

| eval days_since = floor((now() - new_disabled_time) / 86400)
| search days_since>60
| table new_disabled_user days_since
| sort -days_since

How do I accomplish 'Query3' and 'Query4'? Also, all lookups to updated periodically with a scheduled report/search.

If you have an alternative solution, I'll be very much delighted.

Thanks in Advance.
Anil

[harsmarvania57 · Accepted solution]

Hi @anil_ec21,

For query 3 try below query, this query will check enabled_user in disabled_user.csv file and provide only users those are present in disabled_user.csv file.

index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4722 Account_Domain= ABC Account_Name!="*$" 
|rename _time as enabled_time, user as enabled_user
| table enabled_time enabled_user 
| dedup enabled_time enabled_user
| lookup disabledusers.csv disabled_user AS enabled_user OUTPUT disabled_user,disabled_time
| where isnotnull(disabled_user) AND enabled_time > disabled_time
| fields - disabled_user,disabled_time
| outputlookup reenabledusers.csv

For query 4 try below query.

index="wineventlog" sourcetype="WinEventLog:Security" EventCode=4725 Account_Domain= ABC Account_Name!="$" user!="$"
| rename _time as new_disabled_time, user as new_disabled_user
| table new_disabled_time new_disabled_user
| dedup new_disabled_time new_disabled_user
| lookup deletedusers.csv deleted_user AS new_disabled_user OUTPUT deleted_time,deleted_user
| lookup reenabledusers.csv enabled_user AS new_disabled_user OUTPUT enabled_time,enabled_user
| where isnotnull(enabled_user) AND isnotnull(deleted_user)
| fields - deleted_time,deleted_user,enabled_time,enabled_user
| outlookup new_disabledusers.csv

I hope this helps.

Thanks,
Harshil

[anil_ec21]

Thanks Harshil. It really helped with little modifications. And, sorry for responding late.