topic Re: Compare Usernames from Search with Lookup (csv) in Getting Data In

Compare Usernames from Search with Lookup (csv)

franz__ — Mon, 24 Jul 2017 09:47:58 GMT

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?

Re: Compare Usernames from Search with Lookup (csv)

gcusello — Mon, 24 Jul 2017 12:31:59 GMT

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

Re: Compare Usernames from Search with Lookup (csv)

franz__ — Mon, 24 Jul 2017 12:41:40 GMT

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.

Re: Compare Usernames from Search with Lookup (csv)

gcusello — Mon, 24 Jul 2017 13:00:09 GMT

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

Re: Compare Usernames from Search with Lookup (csv)

franz__ — Tue, 29 Sep 2020 15:02:58 GMT

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

Re: Compare Usernames from Search with Lookup (csv)

DalJeanis — Mon, 24 Jul 2017 14:43:08 GMT

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)

Re: Compare Usernames from Search with Lookup (csv)

gcusello — Mon, 24 Jul 2017 15:53:53 GMT

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

Re: Compare Usernames from Search with Lookup (csv)

woodcock — Mon, 24 Jul 2017 21:52:49 GMT

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)

Re: Compare Usernames from Search with Lookup (csv)

franz__ — Tue, 25 Jul 2017 06:34:10 GMT

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

Re: Compare Usernames from Search with Lookup (csv)

woodcock — Mon, 01 Jul 2019 17:15:56 GMT

Right, fixed in my answer, too.