Splunk Search

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

mgrosholz
Path Finder

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?

Tags (2)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

mgrosholz
Path Finder

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

0 Karma

wenthold
Communicator

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

mgrosholz
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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
0 Karma

mgrosholz
Path Finder

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

0 Karma

mgrosholz
Path Finder

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.

0 Karma

somesoni2
Revered Legend

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.

0 Karma

mgrosholz
Path Finder

No results with just the subsearch.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...