Hi,
I have a search that works just fine that shows a list of users in a lookup table that have not logged into Splunk in the last 7 days:
| inputlookup user_role_lookup.csv | rename userName AS user | table user | eval count=0 | join type=left user [search index=_audit action="login attempt" info=succeeded earliest=-7d@d | stats count by user] | where count=0
The lookup table is simply 'userName' and 'roles' with about 190 entries. Roles, of course, is not a value in the _audit logs. I want to be able to show if no one from a particular role logged into Splunk in the last 7 days but replacing 'user' with 'roles' in the query above doesn't give me what I need. If it matters, the field 'roles' is the actual roles we created in Splunk pulled out using the REST command that was put into a lookup table.
Any help is appreciated.
Try this...
| inputlookup user_role_lookup.csv
| rename userName AS user
| table user roles
| makemv roles
| mvexpand roles
| eval mycount=0
| join type=left user
[search index=_audit action="login attempt" info=succeeded earliest=-7d@d
| stats count as mycount by user]
| stats sum(mycount) as mycount by role
| where mycount=0
The above code assumes that roles is a multivalue field that has been flattened to go into the csv and that may contain more than one role, separated by spaces. If it is a single-value field, then the makemv
and mvexpand
are redundant but wont' break anything. If there is some other way the field is constructed, such as pipe-delimited values, then you'll have to adjust hte code as needed.
Hey @ktaitingfong, if they solved your problem, please don't forget to accept an answer! You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!
Try like this
This is the updated query for your original requirement (avoiding join)
index=_audit action="login attempt" info=succeeded earliest=-7d@d [| inputlookup user_role_lookup.csv | rename userName AS user | table user]
| stats count by user
| append [| inputlookup user_role_lookup.csv | rename userName AS user | table user roles| eval count=0]
| stats values(roles) as roles max(count) as count by user
| where count=0
Add this to above query for your 2nd requirement
above query | table roles | dedup roles
Thanks for the help. Unfortunately, this gives me the same results I received previously which is showing me a zero count for roles and users although I have a user in the same role that did login. i.e.: Bob and Mary both have the role 'power' and Bob is showing up in the list, but since since Mary is in the same group and has logged in, 'power' shouldn't be in the results.
Try this...
| inputlookup user_role_lookup.csv
| rename userName AS user
| table user roles
| makemv roles
| mvexpand roles
| eval mycount=0
| join type=left user
[search index=_audit action="login attempt" info=succeeded earliest=-7d@d
| stats count as mycount by user]
| stats sum(mycount) as mycount by role
| where mycount=0
The above code assumes that roles is a multivalue field that has been flattened to go into the csv and that may contain more than one role, separated by spaces. If it is a single-value field, then the makemv
and mvexpand
are redundant but wont' break anything. If there is some other way the field is constructed, such as pipe-delimited values, then you'll have to adjust hte code as needed.
Thanks,
Oddly this produces no results with and without the makekv and kvexpand and even with the where mycount=0 has been removed.
Thanks, this gets me a lot closer. I think the problem I have now is that the roles field is multivalued but I can work that out. Thanks for the help!
@ktaitingfong - are they separated by spaces, commas, or comma-space?
Just modify this line to break it up into an official multivalue field, and it should work.
| eval roles=mvdedup(split(roles," "))
Best wishes.
@ktaitingfong - sorry about the delay, Here's some debug steps.
First, run this...
index=_audit action="login attempt" info=succeeded earliest=-1h@h
| stats count as mycount by user
Verify that you have at least some records. If not, then find the actual field name and rename it before the stats
command.
Next, run this and verify that multiple values in roles
are separated by spaces.
| inputlookup user_role_lookup.csv
| head 10
Next, try this...
index=_audit action="login attempt" info=succeeded earliest=-1h@h
| stats count as mycount by user
| rename COMMENT as "add roles from lookup, build key and copy roles to mycount records"
| inputlookup append=t user_role_lookup.csv
| eval user=coalesce(user, userName)
| eval mycount=coalesce(mycount,0)
| stats values(roles) as roles max(mycount) as mycount by user
| rename COMMENT as "split up the roles into separate records, then sum connections for each role"
| eval roles=mvdedup(split(roles," "))
| mvexpand roles
| stats sum(mycount) as mycount by roles