Dashboards & Visualizations

Dashboard to list all disabled users which are due for deletion

anil_ec21
Explorer

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

0 Karma
1 Solution

harsmarvania57
SplunkTrust
SplunkTrust

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

View solution in original post

harsmarvania57
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...