Compare Usernames from Search with Lookup (csv)

[franz__]

So I read a few answer from here, but I could't ge this to work.

My Seach:

search.... | dedup user | table user

and I want to to compare this with usernames from a csv. I've uploaded a lookup "test.csv" file one colum named username. I want an Output that shows all Usernames that are not in the csv

.... | dedup username | search NOT username [inputlookup test.csv] | table username

Shows only the Usernames that are in the List. Can someone help to show all User that are not in the Lookup?

[woodcock · Accepted solution]

Use the lookup capability, NOT a subsearch:

First this:

.... | dedup username | lookup test.csv username OUTPUT username AS foundInLookup

Then either this for "is in file":

| where isnotnull(foundInLookup)

Or this for "is NOT in file":

| where isnull(foundInLookup)

[franz__]

I had to add the Columname but then it worked, thanks!

... | dedup username | lookup test.csv username OUTPUT username AS foundInLookup | where isnull(foundInLookup) | table username

[woodcock]

Right, fixed in my answer, too.

[gcusello]

hi franz__,
try something like this

your_search
| eval username=upper(username)
| stats count by username 
| append [| inputlookup test.csv | eval count=0, username=upper(username) | fields username count]
| stats sum(count) AS Total by username

usernames > 0 are present both in table and in lookup
usernames = 0 are only in lookup

Bye.
Giuseppe

[DalJeanis]

This mod gets the users not in the list

your_search
 | eval username=upper(username)
 | stats count by username 
 | append [| inputlookup test.csv  | fields username| username=upper(username), count=0, flag="okay user"]
 | stats sum(count) AS Total, values(flag) as flag by username
 | where isnull(flag)

[franz__]

hey, thats for the quick comment. The test.csv contains a List of "OK" Users, I want to filter all Users that are not in that List.

[gcusello]

hi franz__,
did you inserted in the subsearch also count=0?
because in this way all lookup usernames have at least count=0 and in the sum(count) are present.
Bye.
Giuseppe

[franz__]

Yes I copied your Query. Both

earliest=-24h host="*" sudo:session | rex "pam_unix(sudo:session): session opened for user root by (?[[:alnum:]_.]+)" | dedup user | table user

earliest=-24h host="*" sudo:session | rex "pam_unix(sudo:session): session opened for user root by (?[[:alnum:]_.]+)" | eval username=upper(username)
| stats count by username
| append [| inputlookup test.csv | eval count=0, username=upper(username) | fields username count]
| stats sum(count) AS Total by username

Both shows the same Users, your shows the ammont of Logins (nice the stats count command), but the inputlookup / append commands seems to do nothing.

Thanks

[gcusello]

what's the output of

| inputlookup test.csv | eval count=0, username=upper(username) | fields username count

?
username field name is written in the same way both in search and in lookup?
Bye.
Giuseppe