topic Re: How to develop a search to find what is NOT in a lookup table with another lookup comparison? in Splunk Search

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

mgrosholz — Thu, 11 May 2017 15:19:07 GMT

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?

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

somesoni2 — Thu, 11 May 2017 16:22:41 GMT

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

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

mgrosholz — Thu, 11 May 2017 18:09:54 GMT

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

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

mgrosholz — Thu, 11 May 2017 18:10:57 GMT

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.

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

mgrosholz — Thu, 11 May 2017 18:38:35 GMT

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.

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

somesoni2 — Thu, 11 May 2017 18:41:58 GMT

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.

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

wenthold — Thu, 11 May 2017 19:20:59 GMT

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

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

mgrosholz — Thu, 11 May 2017 20:08:23 GMT

No results with just the subsearch.

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

mgrosholz — Sun, 14 May 2017 15:51:20 GMT

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

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

kamlesh_vaghela — Tue, 16 May 2017 13:10:36 GMT

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