topic Re: Counting a value out of a lookup table that does not exist in the logs in Splunk Search

Counting a value out of a lookup table that does not exist in the logs

ktaitingfong — Tue, 29 Sep 2020 15:53:55 GMT

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:

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.

Re: Counting a value out of a lookup table that does not exist in the logs

DalJeanis — Thu, 21 Sep 2017 00:07:05 GMT

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.

Re: Counting a value out of a lookup table that does not exist in the logs

somesoni2 — Thu, 21 Sep 2017 00:12:56 GMT

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

Re: Counting a value out of a lookup table that does not exist in the logs

ktaitingfong — Thu, 21 Sep 2017 17:02:36 GMT

Thanks,

Oddly this produces no results with and without the makekv and kvexpand and even with the where mycount=0 has been removed.

Re: Counting a value out of a lookup table that does not exist in the logs

ktaitingfong — Thu, 21 Sep 2017 17:09:41 GMT

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.

Re: Counting a value out of a lookup table that does not exist in the logs

lfedak_splunk — Thu, 21 Sep 2017 23:11:30 GMT

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!

Re: Counting a value out of a lookup table that does not exist in the logs

DalJeanis — Fri, 06 Oct 2017 20:16:15 GMT

@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

Re: Counting a value out of a lookup table that does not exist in the logs

ktaitingfong — Tue, 10 Oct 2017 15:57:04 GMT

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!

Re: Counting a value out of a lookup table that does not exist in the logs

DalJeanis — Tue, 10 Oct 2017 16:39:03 GMT

@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.