Compare search results to CSV values

[Sasquatchatmars]

Hi all! 

I have been trying to compare a search with a CSV lookup table. So far no luck... The list contains only 1 column with usernames. For example:

       username
      user_Apha
      user_Beta
      user_Charlie
      user_Delta

 Now this list is used to verify if users who are not in the company still logged in (the list is updated daily) but I can't seem to make it work. This is the search I have so far 

index="wineventlog" source="WinEventLog:Security" action=success EventCode=4624 OR 4768
| lookup disabled_account_list username OUTPUT username AS Disabled_User
| where user = username
| table Time username

I assume that it is completely wrong but I am out of ideas about how to correct it. 

Thank you very much,

Sasquatchatmars

[gcusello · Accepted solution]

Hi @Sasquatchatmars,

let me understand: in the index you habe the field user, instead in the lookup you have the field username and you want to chack that all the user values are in the lookup in the username field, is it correct?

if yes, please, try this search:

index="wineventlog" source="WinEventLog:Security" action=success EventCode=4624 OR EventCode=4768 NOT [ | inputlookup disabled_account_list | rename username as user | field user ]
| table _time user

Ciao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello,

That is correct. But I only want to see which user logged in that shouldn't have. So if the user in the index corresponds to the username in the lookup file, I want a table that shows only that one. With the search that you gave in your first reply I see all every event of every user in the index. Not only the matched ones.

Thank you,

Sasquatchatmars

[gcusello]

Hi @Sasquatchatmars,

with my search you have the users in the index that aren't also in the LDAP.

If you want  to take the ones that are in LDAP don't use the NOT operatore before the subsearch.

Ciao.

Giuseppe

[Sasquatchatmars]

Hi @gcusello ,

This worked perfectly! Thank you very much! Just FYI in your search you wrote "field" instead of "fields". 

Thank you! 

Sasquatchatmars