topic Re: How to get the difference between 2 queries? in Splunk Search

How to get the difference between 2 queries?

kiamco — Wed, 04 Apr 2018 19:58:20 GMT

This is the query that reports when a user last changed their password:

index=_audit "action=password change"

This is the query that reports the users in the system:

| rest /services/authentication/users splunk_server=local 
| fields title roles realname email
| rename title as userN
| rename realname as name

Should a join be needed between these 2 queries? But I know that join won't always have results (eg. outer-join) since not all users will have changed passwords recently

I need to merge that with a report that finds all the accounts, and whether their admins, and then report on the "difference" in the lists.

I want to result to give me a report of the accounts/users that haven't reset their password in 3 months (or 1 month for administrators)

Re: How to get the difference between 2 queries?

adonio — Wed, 04 Apr 2018 20:03:00 GMT

what exactly is the final result you desire?

Re: How to get the difference between 2 queries?

kiamco — Wed, 04 Apr 2018 20:05:31 GMT

A report of the accounts/users that haven't reset their password in 3 months (or 1 month for administrators)

Re: How to get the difference between 2 queries?

splunker12er — Thu, 05 Apr 2018 02:57:27 GMT

Try this,

index=_audit action="password change"|table timestamp,user,action,info|eval timestamp=strptime(timestamp,"%m-%d-%Y")|eval check_time=relative_time(now(),"-60d@d")|where timestamp>check_time|convert ctime(*time*)|join user[|rest /services/authentication/users|fields title,roles |rename title as user]

Re: How to get the difference between 2 queries?

elliotproebstel — Thu, 05 Apr 2018 14:21:22 GMT

I think this should give you what you're looking for:

index=_audit "action=password change" 
| stats latest(_time) AS _time BY user 
| append 
    [| rest /services/authentication/users splunk_server=local 
    | fields title roles realname email 
    | rename title as user 
    | rename realname as name ] 
| stats latest(_time) AS _time values(roles) AS roles values(name) AS name values(email) AS email BY user 
| where isnull(_time) OR (_time<relative_time(now(), "-30d@d") AND like(roles, "admin"))

The part before the append will find the latest time a user changed their password. The subsearch will gather the related user information for the whole user base. After that, a second stats call will find the latest _time value for each user (and also list their roles, name, and email). Because the subsearch returns data that does not contain _time values, the only users with non-null _time fields will be users who have changed their passwords sometime during the search time. So if you make the search time 3 months, users who've changed their passwords in the last 3 months will be removed by the | where isnull(_time), and the second half of this clause looks for users with "admin" in their list of roles and checks to see if their most recent password change time is earlier than 30 days prior to now.

Re: How to get the difference between 2 queries?

kiamco — Thu, 05 Apr 2018 18:24:12 GMT

I tried this out and you almost had it but I was able to fill in the blanks. Thanks that really helped a lot