Security
Highlighted

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

Path Finder

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.

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Path Finder

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)

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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
0 Karma
Highlighted

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

Path Finder
| 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

0 Karma