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!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...