How to display people who have NOT changed their passwords in the last month?

[kiamco]

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.

[kiamco]

| 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

[David]

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

[martin_mueller · Accepted solution]

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.

[martin_mueller]

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.

[kiamco]

the list of user I haves is in a savedsearch "Splunk users", doesnt input lookup work with flies like csv?

[kiamco]

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)

[martin_mueller]

Make sure your lookup only contains a user column and no change column.