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

[milesbrennan]

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.

[pradeep577]

I added DC=*

[spodda01da]

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 ?

[jseddon]

THANK YOU for this! Very helpful!