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

[vincenp2]

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?

[cmerriman]

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.

[MonkeyK]

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]

[MuS]

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 · Accepted solution]

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"