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?
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)
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)
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
Right, fixed in my answer, too.
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
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)
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.
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
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
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