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
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
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
Thanks Harshil. It really helped with little modifications. And, sorry for responding late.