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

[Bobmc]

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

[Bobmc]

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

[andrew_nelson]

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.

[Bobmc]

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

[yuanliu]

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

AccountID	EventID	_time
joe	4624	2022-05-01
jane	4624	2022-05-20
joe	4625	2022-06-01
jane	4624	2022-07-01
jason	4624	2022-08-20
suze	4624	2022-09-01

the above search should give

AccountID	values(EventID)	_time
jane	4624	2022-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]

---

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.

[andrew_nelson]

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