Splunk Search
Highlighted

Appending Lookup Tables

Path Finder

I have multiple look up tables built from ldapsearches, these tables hold different user that might have access. I need to be able to look in multiple tables to see if a user has generated the event. I am running this query and all I get is the user from the first lookup table.

index="wineventlog" host="todresa3" [ | inputlookup itocusers.csv | inputlookup append=true itocpjf.csv | rename username as AccountName | eval user=Account_Name ] | table user

I am trying to if any user from lookup table1 or table2 or table 3 has generated the event.

Thanks!

0 Karma
Highlighted

Re: Appending Lookup Tables

Motivator

Try this:

index="wineventlog" host="todresa3" | inputlookup append=true itoc_users.csv | append [| inputlookup append=true itoc_pjf.csv | rename user_name as Account_Name | eval user=Account_Name ] | table user
------------
Hope I was able to help you. If so, an upvote would be appreciated.
0 Karma
Highlighted

Re: Appending Lookup Tables

SplunkTrust
SplunkTrust

He's not looking for the data from the lookup tables, he's looking for events that match the data in the table. That's why the lookups are inside the brackets in the first part of his search.

0 Karma
Highlighted

Re: Appending Lookup Tables

Path Finder

so what I am looking for is:

search all the windows events from my server, for any user that is in the lookup tables itocuser.csv and\or itocpjf.csv.

These lookup tables have different user accounts based on different sources.

Thanks!

0 Karma
Highlighted

Re: Appending Lookup Tables

SplunkTrust
SplunkTrust
index="wineventlog" host="todresa3" 
      [ | inputlookup itoc_users.csv 
        | inputlookup append=true itoc_pjf.csv 
        | inputlookup append=true itoc_table3.csv 
        | rename user_name as user 
        | table user ]

The above assumes that you have three lookup tables, each of which is a list of user names in the field user_name.

If some of the tables have the value in Account_Name instead, then do this...

  [ | inputlookup itoc_users.csv 
    | inputlookup append=true itoc_pjf.csv 
    | inputlookup append=true itoc_table3.csv 
    | eval user = coalesce(user_name, Account_Name)
    | table user  ]

Here's what the subsearch code inside the square braces [] does -

  1. Use inputlookup to read in the first table.
  2. Use inputlookup to add the data in the second and third tables.
  3. Use rename to change the user_name field to user.
  4. Use table to eliminate all other fields than user.

Once it has done that, it hits the end of the square brackets. That means that the implicit "format" command at the end takes effect, and the data returned from the subsearch is reformatted into looking like this:

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

This wording is then returned and used as a filter on the main search, so that only events where the user field has a value in the list will be returned.

Notice, if you had not eliminated all other fields, the results would have been like this...

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

If you want to know WHICH lookup table it was found in, then you can run the resulting events through something like this...

      | lookup itoc_users.csv   user_name as user OUTPUT user_name as namefound1
      | lookup itoc_pjf.csv     user_name as user OUTPUT user_name as namefound2
      | lookup itoc_table3.csv  user_name as user OUTPUT user_name as namefound3
      | eval tablefound = case(isnotnull(namefound1),"itoc_users",  isnotnull(namefound2),"itoc_pjf",
            isnotnull(namefound3), "itoc_table3",  true(), "unknown")