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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...