So I don't have trouble displaying people who have changed their password but I don't know how to display users who did not change their passcode. What I have in mind is to create two reports, one would be a list of usernames than the other would have user names who have changed their passwords.
That's halfway there already. Take your list of all usernames, and deduct those that have recently changed their password. In pseudo-SPL that could look something like this:
search for password changes | stats latest(_time) as change by user | inputlookup append=t all_users | stats first(change) as change by user | where isnull(change)
That would return all users in your list that did not change their password in the selected time range.
the list of user I haves is in a savedsearch "Splunk users", doesnt input lookup work with flies like csv?
I took your advise and created a lookup but when I tried this query only gave me the complete list of usernames
index=audit action="password change" | stats latest(time) as change by user | inputlookup append=t splunk_users.csv | stats first(change) as change by user | where isnull(change)
Inputlookup works with lookup files, yeah.
For a one-time run you can use
... | append [savedsearch name-of-the-saved-search or something like that], for frequent runs you should consider writing the results of your all-users-search into a lookup file and update that every now and then.
Version 2.1 of Splunk Security Essentials (released next week) will include this use case, based on LDAP data, as well.
| inputlookup UC_active_directory_search.csv | convert timeformat="%Y-%m-%dT%H:%M:%S.%6QZ" mktime(pwdLastSet) mktime(lastLogonTimestamp) | convert timeformat="%Y%m%d%H%M%S.0Z" mktime(whenCreated) | where pwdLastSet < relative_time(now(), "-120d") AND lastLogonTimestamp > relative_time(now(), "-30d") | convert ctime(lastLogonTimestamp) ctime(whenCreated) ctime(pwdLastSet)
Based on using LDAPSearch to query AD:
| ldapsearch search="(&(objectclass=user)(!(objectClass=computer)))" attrs="sAMAccountName,pwdLastSet,lastLogonTimestamp,whenCreated,badPwdCount,logonCount" domain=yourDomain | fields - _raw host _time
| rest /services/authentication/users splunk_server=local |fields title, roles, email | rename title as user | eval roles=if(roles="admin","admin","") | eval action="need to reset password" |join type=left user [|search index=_audit (action="password change" NOT user=index-manager) |join user [| rest /services/authentication/users splunk_server=local |fields title, roles, email | rename title as user | eval roles=if(roles="admin","admin","") ] |eval timestamp= strptime(timestamp,"%m-%d-%Y") |eval check_time=if(roles="admin",relative_time(timestamp,"+30d@d"), relative_time(timestamp,"+90d@d")) |eval action=if(action="password change",if(check_time < now(),"need to reset password","password change successful"), "need to reset password") |convert ctime(*time*) ] |table user,email,roles, timestamp, check_time, action
This is my final query it doesn't need a lookup and its not dependent on a lookup that I have to manually update every month or so