How do I track unused logons in Windows?

[kamranrahmad]

I'm trying to get a query for the last login that occured over 30 days ago. Intent being to disable/delete unused accounts on Windows. Thanks in advance.

[jonuwz]

If you're using AD, you'll need to dump a list of all the logins otherwise you'll only be able to find users who have not logged in in the last 30 days, providing splunk has seen them ever log in.

Assuming you are using active directory, its far simpler to get this info from AD itself.

dsquery * -filter "(&(objectCategory=CN=Person,CN=Schema,CN=Configuration,DC=acme,DC=com)(lastLogonTimestamp<=129930132306842957))" -limit 0 -attr sAMaccountname lastLogonTimestamp

the lastlogon field is the number of nanoseconds since 1600, I think, so you need to calculate that value - 30 days ago and plug it into your search

edit changed lastlogon to lastlogontimestamp at jeffs suggestion

[jonuwz]

Cheers Jeff, good to know, even if I now have to go and doublecheck a whole bunch of reports for accuracy ...

[jeff]

jonuwz is right here... my answer above assumes a user logged in sometime between 40 and 30 days ago- it won't report any users that didn't log in at all or outside of that time. You could dump all AD users to a csv and use inputlookup to load them.

Note that lastLogon isn't replicated, so to be accurate you would need to get this value from every Domain Controller in your environment. You are better off querying lastLogonTimestamp. It is replicated, but is only updated on any given object if its value is over seven days (I think) old at the time of successful authentication.

[jeff]

You'll probably find that this search will be expensive in Splunk without summary indexing enabled. Generate a query to search the Windows security logs for successful logons and schedule to run every x minutes or hours, adding the data to the summary indexing. Depending on what fields you choose to push to the summary index, you may just want to record one logon per user over the timespan... something like:

sourcetype=sourcetype=WinEventLog:Security EventCode=4648   | rex field=_raw "(?si)New Logon:.*Account Name:\W(?{user}[^ \r\n]+)" | stats max(_time) as lasttime by user

(note:* {user} above should really be <user>... markdown wasn't cooperating with me for some reason.)*

scheduled to run over an hour and added to a summary index hourly. Then...

index=summary_ad_logons earliest=-40d@d latest=-30d@d NOT [search index=summary earliest=-30d@d latest=now | dedup user | fields user]

Now, having said all of this, I'm pretty sure you can get this out of the native Microsoft plugins by looking at a given user's lastLogonTimestamp attribute. The Windows Server 2008 AD Administrative Center plugins has specific pre-bulit queries just for this purpose.