Dashboards & Visualizations

Timechart for created/disabled users

jypysplunk
New Member

Hi, I'm looking to create a timechart of Active directory users created & disabled, so we can have an overview of new & leaving employee over the time.
I'm trying to do this using the index wineventlog (from the Splunk App for Windows Infrastructure). I came up with this query:

index=wineventlog earliest=-180d@d user!="*\$" user!="\$*" (EventCode=4720 OR EventCode=4725) | eval action=if(EventCode=4725,"disabled",action) | eval action=upper(action) | rename action as Action | dedup user,Action| timechart count by Action span=1mon

I've changed the query to get a table with the users and the associated action.

index=wineventlog earliest=-180d@d user!="*\$" user!="\$*" (EventCode=4720 OR EventCode=4725) | eval action=if(EventCode=4725,"disabled",action) | eval action=upper(action) | rename action as Action | dedup user,Action| table user,Action,_time

Due to our new user process, some of them have a created event and then a disabled with some seconds/minutes between them like

User Action Time
user.name1 Disabled 2017-01-27 06:19:32
user.name1 Created 2017-01-27 06:18:26
user.name2 Disabled 2017-01-25 03:21:31
user.name3 Disabled 2017-01-23 03:12:35
user.name3 Created 2017-01-23 03:11:43
user.name4 Disabled 2017-01-23 02:18:26

I would like to exclude the Disabled event when a user has been created on the same day. I've tried with dedup but I couldn't make it work as it keep the first occurence (that would always be disabled)
This would be the desired result:

User Action Time
user.name1 Created 2017-01-27 06:18:26
user.name2 Disabled 2017-01-25 03:21:31
user.name3 Created 2017-01-23 03:11:43
user.name4 Disabled 2017-01-23 02:18:26

Thanks for your help

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="user.name1 Disabled 2017-01-27 06:19:32
user.name1 Created 2017-01-27 06:18:26
user.name2 Disabled 2017-01-25 03:21:31
user.name3 Disabled 2017-01-23 03:12:35
user.name3 Created 2017-01-23 03:11:43
user.name4 Disabled 2017-01-23 02:18:26"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<User>\S+)\s+(?<Action>\S+)\s+(?<Time>.*)$"

| rename COMMENT AS "Everything above creates test event data; everything below is your solution"

| eval _time = strptime(Time, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| bin _time span=1d
| eventstats count(eval(Action="Created")) AS createds BY User _time
| search Action="Created" OR createds=0

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

|makeresults | eval raw="user.name1 Disabled 2017-01-27 06:19:32
user.name1 Created 2017-01-27 06:18:26
user.name2 Disabled 2017-01-25 03:21:31
user.name3 Disabled 2017-01-23 03:12:35
user.name3 Created 2017-01-23 03:11:43
user.name4 Disabled 2017-01-23 02:18:26"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<User>\S+)\s+(?<Action>\S+)\s+(?<Time>.*)$"

| rename COMMENT AS "Everything above creates test event data; everything below is your solution"

| eval _time = strptime(Time, "%Y-%m-%d %H:%M:%S")
| sort 0 - _time
| bin _time span=1d
| eventstats count(eval(Action="Created")) AS createds BY User _time
| search Action="Created" OR createds=0
0 Karma

jypysplunk
New Member

Perfect! Thanks for your quick answer.

0 Karma

woodcock
Esteemed Legend

If it worked for you, be sure to click Accept on the answer to close the question.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...