LookUp Functionalility

[pfabrizi]

I have this search using a lookup table with a list of Windows sAMAccountName accounts, I am trying to use the search to see if the user in the event is also in my lookup table. This works but I am still trying to understand it.

index=wineventlog eventcode4624 [ | inputlookup table.csv | rename user_name as Account_Name | eval user=Account_Name]

user_name is column in my lookuptable, Account_Name and user are fields in the event.

I tried another search where I was looking against another event code and it doesn't work. This event has fields User and User_Name,
when I tried this search it doesn't work?

index=wineventlog eventcode4624 [ | inputlookup table.csv | rename user_name as User_Name | eval User=User_Name]

[woodcock]

Whenever you are debugging a subsearch, run it as a real search and tack on | format to the end to see what it is really generating.
You need to use this:

index=wineventlog eventcode4624 [ | inputlookup table.csv | rename user_name as User ]

Or perhaps this:

index=wineventlog eventcode4624 [ | inputlookup table.csv | rename user_name as User_Name ]

But definitely not both.

[DalJeanis · Accepted solution]

Okay, here's the problem. I know that it's a lookup table, but you are not really doing a lookup, you are doing a subsearch. And that is 100% fine and will work great, once you understand how it works.

Your subsearch is returning too much. All that stuff in square brackets is a subsearch, it is run and/or calculated at the very first, and it returns some words or values that will be run as part of the main search.

Your subsearch - the part in the square brackets [] - runs first. When it gets done, at the end of the square brackets, the system generates an implicit format command, to change the values into a very useful set or ANDs and ORs. You can look up more details here - https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Format
- but I'm going to describe it for you below, with examples.

To be specific, this ...

  [  a bunch of stuff | table user] 

... will return this ...

( ( user="value1" ) OR ( user="value2" ) OR .... ( user="lastvalue" ) )

...whereas this ...

  [  a bunch of stuff | table user foo] 

...will return this ...

( ( user="value1"  AND foo="foovalue1" ) OR ( user="value2" AND foo="foovalue2" ) OR .... 
  ( user="lastvalue" AND foo="lastfoovalue" ) )

...and if you had twelve fields in the events that hit the end of the subsearch, then you'd have the values on each event ANDed together, then ORed with each other line like this...

( ( field1="value" AND field2="value"  AND field2="value" AND... field12="lastvalue" ) OR ...
( ( field1="value" AND field2="value"  AND field2="value" AND... field12="lastvalue" ) OR ...
... ))

So, you have to make sure that, at the end of the square braces from your lookup, you are returning just the field(s) that you want to limit the main search by.

---

NOW, we MAY have to do one more thing. If the values in your lookup table are always in for the Account_Name and user fields, then you are good with |table Account_Name user

On the other hand, if those values could be in EITHER the Account Name or user fields, then we need to make the format command explicit, so we can override that default "AND" to an "OR" while we are at it.

So at the end of the subsearch, you can use this ...

  | table Account_Name user 
  ]

...to generate this...

( (  Account_Name="value1" AND user="value1"  ) OR ... )

...or you can use this...

  | table Account_Name user
  | format "(" "(" "OR" ")" "OR" ")"
  ]

...to generate this ...

( (  Account_Name="value1" OR user="value1"  ) OR ... )

[indiechixor]

I just wanted to thank you for your help! I was attempting a similar query (compare threat ip lookup table against live data) and the results were not coming up due to lack of the 'formatting'.

[pfabrizi]

Thank You, I think I have a better understanding of how these work and I do realize I had to much in the sub-search.

Appreciate the help, I am trying to replace our current SIEM with SPLUNK with no training.

[DalJeanis]

@pfabrizi - if your question has been answered, please pick the answer that helped you the most and "accept" it as the solution to your problem. You can also up-vote any other answers that you found useful or helpful.

Down-voting is usually reserved around here for answers that are problematic, counterproductive or just plain wrong... and many of us either just comment for future readers explaining the error, or at least give the poster a chance to fix it before downvoting.

Also, your replies to posters should generally be placed as comments on their answers -- or on the comments you are responding to--- as opposed to posting them in an answer. (We will move this one for you.) The exception is if you solved the problem yourself in a different way from the other answers, and are posting your final solution... in which case you can mark your own answer as "accepted" so the question will show as complete.

[pfabrizi]

sorry, second query is:
index=wineventlog eventcode 1281 [ | inputlookup table.csv | rename user_name as User_Name | eval User=User_Name]

[lfedak_splunk]

Hey @pfabrizi are you able to edit your post to swap the searches?