Lookup table for matching and non matching counts

[WyldeRhoads]

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!

[woodcock · Accepted solution]

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]

Thanks! This was a helpful and straightforward answer.