How to develop a search to find what is NOT in a lookup table with another lookup comparison?

[mgrosholz]

I have two lookup tables and I want to search what is NOT in lookup2 but in lookup1.

I have tried:
... NOT [|inputlookup lookup2 ....]

So overall I have something like:

index=foo NOT [| inputlookup lookup2]
| lookup lookup1 fieldA
|... rest of search ...

But nothing works. Any tips?

[kamlesh_vaghela]

Hi mgrosholz,
Can you please try this one??

index=foo NOT [| inputlookup lookup2 | return fieldX fieldY fieldZ ] [| inputlookup fieldA | return fieldA ]
|... rest of search ..

Here, I have used return command. For more info please check below URL.

https://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Return

Thanks
Kamlesh

[mgrosholz]

More clarification:
lookup1 and lookup2 have different fields respectively.

The goal is to:
- Have events from index=foo...
- Show only events that match fieldA in lookup1...
- Output fieldB and fieldC from lookup1...
- Remove events (or not see) that match fieldX, fieldY, and fieldZ in lookup2...

[wenthold]

If you want to search index=foo for anything that matches lookup1, but remove any results from lookup2:

index=foo [ | inputlookup lookup1 | format ] | search NOT [| inputlookup lookup2 | format ]

If you're just trying to get results from index=foo, excluding anything from lookup2:

index=full NOT [ | inputlookup lookup2 | format ] | ....

[mgrosholz]

There is confusion with the title. The first lookup should not affect the second lookup.

I am looking at lookup1 and getting results. Then I am saying do not look at events that match lookup2.

[somesoni2]

Try like this (assuming the field name that is common in all is fieldA)

index=foo [| inputlookup lookup1.csv | search NOT [| inputlookup lookup2.csv | table fieldA]]
|...rest of the search

[mgrosholz]

It doesn't pull anything. Checked the lookup, it was empty. So it should spit out results.

[mgrosholz]

I have been using the lookup command for lookup1 and it works great. I can read, compare, and pull fields. But I can't figure out how to say ignore lookup2 events.

[somesoni2]

The subsearch | inputlookup lookup1.csv | search NOT [| inputlookup lookup2.csv | table fieldA] is suppose to give you events from lookup1 which are not in lookup2 (based on fieldA comparision). I would just run that first, validate the results and then add to your main search.

[mgrosholz]

No results with just the subsearch.