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!
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
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
Thanks! This was a helpful and straightforward answer.