Splunk Search

Compare search results to CSV values

Sasquatchatmars
Communicator

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

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

Sasquatchatmars
Communicator

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Communicator

Hi @gcusello ,

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

Thank you! 

Sasquatchatmars

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.