Splunk Search

Lookup table for matching and non matching counts

WyldeRhoads
Engager

I have a lookup table CSV file that has 50 usernames in a single column, and I want to sum the results count of the usernames in the list and the results count of usernames that are not in the list.

Example CSV column w header:

username
bob
joe
sue

Lets say running a splunk search generates 10 username results and count of logs per username:

index=foo source=barUsers
| stats count by username

jan 1
sally 2
herb 3
john 1
sue 2
bill 3
bob 1
jason 2
dan 3
kathy 1

The result I'm looking for would be something like:

userMatchCount = 3
nonUserMatchCount = 16

I've tried things like

index=foo 
| join username
[
| inputlookup usernameList.csv
]

But i'm trying to gather the sum of counts on usernames that also do not match the list, using some sort of eval command.

Thank you in advance!

0 Karma
1 Solution

woodcock
Esteemed Legend

On the Search Head, upload the CSV as a Lookup file and then create a new Lookup table referencing the file name and then do this:

index=foo source=barUsers
| stats count by username
| lookup username OUTPUT username AS matched
| eval matched=if(isnull(matched), "nonUserMatchCount", "userMatchCount")
| stats sum(count) BY matched

View solution in original post

woodcock
Esteemed Legend

On the Search Head, upload the CSV as a Lookup file and then create a new Lookup table referencing the file name and then do this:

index=foo source=barUsers
| stats count by username
| lookup username OUTPUT username AS matched
| eval matched=if(isnull(matched), "nonUserMatchCount", "userMatchCount")
| stats sum(count) BY matched

WyldeRhoads
Engager

Thanks! This was a helpful and straightforward answer.

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...