Splunk Search

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

ktaitingfong
Explorer

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.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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.

View solution in original post

lfedak_splunk
Splunk Employee
Splunk Employee

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!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

ktaitingfong
Explorer

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.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

ktaitingfong
Explorer

Thanks,

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

0 Karma

ktaitingfong
Explorer

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!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@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
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...