Splunk Search

How to search in two lookup files?

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

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

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

Engager

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

0 Karma

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

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

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

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

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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!