Splunk Search

How to get the difference between 2 queries?

kiamco
Path Finder

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)

Tags (4)
0 Karma
1 Solution

splunker12er
Motivator

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]

View solution in original post

elliotproebstel
Champion

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.

0 Karma

splunker12er
Motivator

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]

kiamco
Path Finder

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

0 Karma

adonio
Ultra Champion

what exactly is the final result you desire?

0 Karma

kiamco
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...