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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...