Splunk Search

Can someone help with a sample search to find Stale Active Users in Active Directory?

Bobmc
Observer

Hello, I'm a bit new to Splunk and I'm trying to run a query that shows me users in Active directory that are still enabled but haven't logged in for past 30 days.  I've tried searching through varies post but none seem to be exactly what I'm looking for.  I may have overlooked it so If someone can point me in the right direction or provide a sample query to get me started I'd be very grateful.

Thanks,
Bob

Labels (1)
0 Karma

Bobmc
Observer

Thanks @andrew_nelson ..unfortunantly though we do not have this Add on installed and due to our environment I'm not able to install this anytime soon.  We've recently moved our Splunk instance to a cloud provided and I'm doubtful it will be done before my deadline to provide this data to management.  Is there a query, without this addon that will work?

Thanks,
Bob

0 Karma

andrew_nelson
Communicator

Without a direct connection to AD and without knowing your environment, I can't provide another definitive way to get you the data that you need from Splunk.

You may be able to pull AD with a PowerShell script into a csv and upload to Splunk. A PS forum would be better suited to assisting with that part though. 
I'm not sure the AD export option will format some fields like lastLogon and userAccountControl so there would be a few extra steps.

0 Karma

Bobmc
Observer

My AD boxes are reporting to Splunk, however it just the event log data.  So I can pull data on Event IDs such as 4624 (successful login) but formatting that so it only shows accounts that haven't done that for a period of time and do not have the Event ID 4725 (account was disabled) associated to it is my issue.   I hope that helps make some sense.. 

Thanks
Bob

0 Karma

yuanliu
SplunkTrust
SplunkTrust
Event IDs such as 4624 (successful login) but formatting that so it only shows accounts that haven't done that for a period of time and do not have the Event ID 4725 (account was disabled) associated to it is my issue.   I hope that helps make some sense.. 

This makes a lot more sense than asking about AD user behavior because it describes a sequence of events that are already in Splunk, and criteria in terms of data (as opposed to user behavior).  It would be even better if you give sample data (anonymized), data structure, sample search you have tried, output from such and why the output does not meet your requirements.  After all, this is a Splunk forum, not AD forum.

As someone who has never seen Windows eventlog in Splunk, I wonder how would you determine that a user even exists if he or she hasn't had activity for a long time? (Assuming your search period is finite.)

With this question in mind, the following uses earliest=0 (all time) to signify that it should contain long enough period of time in order to identify users who haven't had activity for a very long time - all without a second source for list of users.

 

source=EventLog EventID IN (4624, 4625) earliest=0
| stats max(_time) as lasttime by AccountID EventID
| stats values(EventID) max(lasttime) as _time by AccountID
| where 'values(EventID)' != 4625 AND _time < relative_time(now(), "-30d")

 

(Again, I have no idea what identifies your Windows source, what are field names, and so on so the entire thing is made up.)  Assuming event sequence like the following

AccountIDEventID_time
joe46242022-05-01
jane46242022-05-20
joe46252022-06-01
jane46242022-07-01
jason46242022-08-20
suze46242022-09-01

the above search should give

AccountIDvalues(EventID)_time
jane46242022-07-01 00:00:00

 

Of course search earliest=0 is very expensive.  So, if there's some periodic machine generated event for inactive accounts, the task would be easier.

A possible reduction of cost (if you have to search earliest=0) is with tstats, e.g.,

 

| tstats max(_time) as lasttime where EventID IN (4624, 4625) earliest=0 by AccountID EventID
| stats values(EventID) max(lasttime) as _time by AccountID
| where 'values(EventID)' != 4625 AND _time < relative_time(now(), "-30d")

 

 

andrew_nelson
Communicator

As someone who has never seen Windows eventlog in Splunk, I wonder how would you determine that a user even exists if he or she hasn't had activity for a long time? (Assuming your search period is finite.)

Yeah this is exactly the reason I was going down the Add-On for AD and script routes to get the full list of AD users. 

Through the WinEventLog, Splunk can tell you what happened and when, but it can't tell you an account exists if it doesn't have any events related to that account.

A full inventory of user accounts is needed to determine what accounts aren't being used.

0 Karma

andrew_nelson
Communicator

If you have the Splunk Add-On for Active Directory installed and configured, this should be straight forward enough.

Something like this should work:
| localop |ldapsearch domain=default search="(&(objectClass=user)(!(objectClass=computer)))" attrs="samaccountname,lastLogonTimestamp,userAccountControl"
| table sAMAccountName, lastLogonTimestamp, userAccountControl
| search userAccountControl!="ACCOUNTDISABLE"
| eval lastLogin=strptime(lastLogonTimestamp, "%Y-%m-%dT%H:%M:%S.%fZ"), threshold=relative_time(now(), "-30d")
| where lastLogin < threshold

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...