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?
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"
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.
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]
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
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"