Security

Is there a search to identify roles that haven't been used in x-days?

vincenp2
New Member

I want to do some housekeeping and remove any roles that have not been used for a long time (> 90 days) - is there a query I can use to find roles not used for more than 90 days - or when they were last used?

Tags (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

This is most of it; tailor to suit:

index=_audit sourcetype=audittrail user=* action=log* 
| rename info as status 
| replace succeeded with success in status 
| replace failed with failure in status 
| replace "login attempt" with login in action 
| stats count max(_time) AS _time BY user action status sourcetype
| appendpipe [
    |rest/services/authentication/users splunk_server=local 
    | table title roles realname
    | rename title as user
    | rename realname as Name
    | eval sourcetype="ROLES"]
| stats values(*) AS * values(_time) AS _time BY user
| fillnull count _time value="0"
| fillnull user value="N/A"
| stats max(_time) AS _time latest(user) AS user BY roles
| eval _time=if(_time=0, "N/A", _time)
| eval secondsSinceLastSeen=now()-_time
| eval timeSinceLastSeen=tostring(secondsSinceLastSeen, "duration")
| fillnull value="N/A"

View solution in original post

cmerriman
Super Champion

you could try something like this:

index=_audit user=* action="login attempt" sourcetype=audittrail earliest=-180d@d
|stats max(_time) as latest_date by user
|join user type=left  [ | rest /services/authentication/users | table title roles | rename title as user ]
|where latest_date<relative_time(now(),"-90d@d")
|fieldformat latest_date=strftime(latest_date,"%Y-%m-%d %H:%M:%S")

It should give any user that has not logged in for the last 90 days and bring in any role information using the join.

0 Karma

MonkeyK
Builder

I don't have access to all of that audit stuff to test with, but here is how I break down your problem:
1) you have a bunch of roles. Some may have never been used some have bee used at various times by users who are members
2) you have a history of user activity
3) you want all roles for which there is no activity in the past X days, or all roles minus those that have been used.

So we start with activity in the past X days (I'll use MuS's first clause for this), but we only care about the users (who have roles) themselves, so here are the user/roles used in the last 120 days

index=_audit user=*  action="login attempt" info="succeeded" earliest=-120d@d  
| join role_user [| rest /services/authentication/users splunk_server=local 
  | fields title roles  
  | rename title as user ]

and we just want their roles

index=_audit user=*  action="login attempt" info="succeeded" earliest=-120d@d  
| join role_user [| rest /services/authentication/users splunk_server=local 
  | fields title roles  
  | rename title as user ]
| stats count by role

So now we have all of the roles used in the last X days, and all we have to do is remove those from a complete list of roles

| rest /services/authentication/users splunk_server=local 
| stats values(title) as unused_role_users by roles 
| where NOT [|index=_audit user=*  action="login attempt" info="succeeded" earliest=-120d@d  
  | join user [| rest /services/authentication/users splunk_server=local 
    | fields title roles 
    | rename title as user ]
  | stats count by roles | fields + roles]
0 Karma

MuS
SplunkTrust
SplunkTrust

Hi vincenp2,

you can try this run everywhere search to get the required results:

index=_audit user=*  action="login attempt" info="succeeded" earliest=-120d@d 
| fields user 
| join user 
    [ | rest /services/authentication/users splunk_server=local 
    | fields title roles realname 
    | rename title as user ] 
| fields user roles realname 
| stats last(_time) AS _time by user, roles, realname 
| where _time < relative_time(now(), "-90d@d")
| eval lastSeenInSecs = round(now() - _time, 0)
| eval "Last seen in (Days+HH:MM:SS)" = tostring(lastSeenInSecs, "duration")

The join here is actually no problem, because the REST search is a generating command and only returns a few results.

Hope this helps ...

cheers, MuS

woodcock
Esteemed Legend

This is most of it; tailor to suit:

index=_audit sourcetype=audittrail user=* action=log* 
| rename info as status 
| replace succeeded with success in status 
| replace failed with failure in status 
| replace "login attempt" with login in action 
| stats count max(_time) AS _time BY user action status sourcetype
| appendpipe [
    |rest/services/authentication/users splunk_server=local 
    | table title roles realname
    | rename title as user
    | rename realname as Name
    | eval sourcetype="ROLES"]
| stats values(*) AS * values(_time) AS _time BY user
| fillnull count _time value="0"
| fillnull user value="N/A"
| stats max(_time) AS _time latest(user) AS user BY roles
| eval _time=if(_time=0, "N/A", _time)
| eval secondsSinceLastSeen=now()-_time
| eval timeSinceLastSeen=tostring(secondsSinceLastSeen, "duration")
| fillnull value="N/A"
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...