Splunk Search

Appending Lookup Tables

pfabrizi
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 itoc_users.csv | inputlookup append=true itoc_pjf.csv | rename user_name as Account_Name | 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

DalJeanis
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")

diogofgm
SplunkTrust
SplunkTrust

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, some karma would be appreciated.
0 Karma

DalJeanis
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

pfabrizi
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 itoc_user.csv and\or itoc_pjf.csv.

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

Thanks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...