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!

Fastest way to demo Observability

I’ve been having a lot of fun learning about Kubernetes and Observability. I set myself an interesting ...

September Community Champions: A Shoutout to Our Contributors!

As we close the books on another fantastic month, we want to take a moment to celebrate the people who are the ...

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...