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!

Earn a $35 Gift Card for Answering our Splunk Admins & App Developer Survey

Survey for Splunk Admins and App Developers is open now! | Earn a $35 gift card!      Hello there,  Splunk ...

Continuing Innovation & New Integrations Unlock Full Stack Observability For Your ...

You’ve probably heard the latest about AppDynamics joining the Splunk Observability portfolio, deepening our ...

Monitoring Amazon Elastic Kubernetes Service (EKS)

As we’ve seen, integrating Kubernetes environments with Splunk Observability Cloud is a quick and easy way to ...