Splunk Search

How to search in two lookup files?

isedrof
Engager

Hey everybody,

I'm making a comparison between two files: one uploaded as an index and the second as a lookup file. This is my search:

| inputlookup lookup_file.csv | search NOT [ search index=* source="index_file.csv" | dedup user_name| table user_name ]

What I want to do, is to launch a search in two lookups files instead of one.
Thank you in advance to any one who may be able to give me some ideas.

0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi isedrof,

Just guessing here, but try this updated search:

| inputlookup lookup_file.csv | search NOT [ search index=* source="index_file.csv" | dedup user_name| table user_name ] search NOT [|inputlookup lookup_file2.csv | fields user_name| dedup user_name]

cheers, MuS

View solution in original post

koshyk
Super Champion

So essentially you are trying to remove "intersection" of two datasets.
Multiple ways to do it
- Option 1 (Using NOT) . Ensure your first Search contains "user_name" field , otherwise rename fields to match

  index=* source="index_file.csv" NOT [|inputlookup  lookup_file.csv | fields user_name| dedup  user_name] 
  • Option 2 (Using join)

    index=* source="index_file.csv" | join type=left user_name [ | inputlookup lookup_file.csv | fields user_name | dedup user_name | eval DummyColumn="Table2"| fields user_name, DummyColumn] | search NOT DummyColumn=*

  • Option 3 (Using SET)

    | set diff [index=* source="index_file.csv" | fields user_name| dedup user_name] [| inputlookup lookup_file.csv | fields user_name | dedup user_name]
    More Details in this post

0 Karma

isedrof
Engager

whene i try the first query i got : Regex: invalid UTF-8 string

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi isedrof,

Just guessing here, but try this updated search:

| inputlookup lookup_file.csv | search NOT [ search index=* source="index_file.csv" | dedup user_name| table user_name ] search NOT [|inputlookup lookup_file2.csv | fields user_name| dedup user_name]

cheers, MuS

isedrof
Engager

Hi,
it doesn't work, do you remember u helped me before
http://answers.splunk.com/answers/265963/how-to-compare-two-log-files.html#answer-265979
and now what i want to add is another lookup table to eliminate the other users who exist in the second list.
Thanks.

0 Karma

MuS
SplunkTrust
SplunkTrust

Yes, I know 😉

Okay try something like this:

| inputlookup lookup_file.csv | inputlookup append=t theotherlookupfile |  search NOT [ search index=* source="index_file.csv" | dedup user_name| table user_name ]
0 Karma

isedrof
Engager

I guess it works now this is what i did:

| inputlookup lookup_file.csv | inputlookup append=t theotherlookupfile | search NOT [ search index=* source="index_file.csv" | dedup user_name| table user_name ] search NOT [|inputlookup lookup_file2.csv | fields user_name| dedup user_name]

Thanks guys.

0 Karma

isedrof
Engager

| inputlookup lookup_file1.csv | search NOT [ search index=* source="file_Source.csv"| table user_name ] | search NOT ( ( user_name="a" ) OR ( user_name="b" )OR ( user_name="c" )OR ( user_name="d" )OR ( user_name="e" )OR ( user_name="f" ))
This is my query, instead of putting all the users_name like this, i thought to put them in a file and launch the search.
all what u've proposed did not work for me.
thank you for ur help.

0 Karma

isedrof
Engager

i'm sorry, but i'll bother you more.
look i have one source file file_source.csv and two lookups file : lookup_file1.csv and lookup_file1.csv
what i want to is to pick up all the users that exist in lookup_file1.csv and does not exist in file_source.csv and lookup_file1.csv , Maybe the idea i have is not correct but I would be happy to receive any ideas or propositions in order to resolve this issue.

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 ...