LDAP/AD Report on User Accounts, Last Login Date and Day Count

Path Finder

This is not a question, but information sharing about using the "Splunk Supporting Add-on for Active Directory", to generate a report on all AD user accounts in your Windows domain, their creation, logon, and last logon dates, whether the account is still active, and how many days since the account was last logged in.

We generate the report every month to check for old accounts which have not been deactivated properly when employee or service accounts are no longer needed / used. Good security check to review accounts which have been inactive and possible candidates to deactivate, to minimise potential exploitation.

| ldapsearch domain="default" search="(&(samAccountType=805306368))" attrs="sAMAccountName, cn, co, st, whenCreated, accountExpires, lastLogonTimestamp, title, physicalDeliveryOfficeName, manager, userAccountControl, distinguishedName"
| rex field=distinguishedName "(?:.*?=){2}(?<FullOU>.*),DC=YOUR,DC=DOMAIN,DC=HERE"
| rex field=lastLogonTimestamp "(?<lastTime>.+)\\."
| eval OU=split(FullOU,","), OU0=mvindex(OU,0), OU1=mvindex(OU,1), OU2=mvindex(OU,2), OU3=mvindex(OU,3), OU4=mvindex(OU,4), OU5=mvindex(OU,5), OU6=mvindex(OU,6), OU7=mvindex(OU,7), OU8=mvindex(OU,8)
| eval OU=toString(OU8) + "," + toString(OU7) + "," + toString(OU6) + "," + toString(OU5) + "," + toString(OU4) + "," + toString(OU3) + "," + toString(OU2) + "," + toString(OU1) + "," + toString(OU0)
| eval OU=replace(OU, "Null", "")
| eval OU=replace(OU, ",,", "")
| eval OU=replace(OU, "^,", "")
| eval OU=replace(OU, "DN=", "")
| eval OU=replace(OU, "CN=", "")
| eval OU=replace(OU, "OU=", "")
| eval OU=replace(OU, ",", " / ")
| eval startDate=strftime(strptime(whenCreated,"%Y%m%d%H%M"), "%Y/%m/%d %H:%M")
| eval endDate=strftime(strptime(accountExpires,"%Y-%m-%dT%H:%M:%S%Z"), "%Y/%m/%d %H:%M")
| eval lastDate=strftime(strptime(lastTime,"%Y-%m-%dT%H:%M:%S"), "%Y/%m/%d %H:%M")
| eval Days=floor((now()-strptime(lastDate,"%Y/%m/%d %H:%M"))/(3600*24))
| rex field=userAccountControl "(?<userAccountControl_parsed>[^,]+)"
| eval userAccountControl=lower(replace(mvjoin(userAccountControl_parsed, "|"), " ", "_"))
| eval status=case(
   match(userAccountControl, "accountdisable") , "disabled",
   1==1, "active"
| table sAMAccountName, cn, title, physicalDeliveryOfficeName, st, co, startDate, endDate, lastDate, Days, status, OU, manager

Use "limit=50" or baseou=" in the ldapsearch query (first line) while you're testing, so you minimise the amount of entries returned.

Also, change the "DC=YOUR,DC=DOMAIN,DC=HERE" section in the query to match your own LDAP DC string.

If your Active Directory has more than 9 OUs/Folders in depth, you'll need to add some additional mvindex / toString statements.

Path Finder

I added DC=*

0 Karma

Path Finder

Thank you, this is very helpful. I have 6 DC in my environment and only 1 is configured in Splunk for LDAP. Considering the inconsistency of Last login time across the DCs, is there a way to include other DCs as well ?

0 Karma


THANK YOU for this! Very helpful!

0 Karma
Get Updates on the Splunk Community!

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...