topic Re: compare multiple value in lookup to single value in index in Splunk Search

How to compare multiple value in lookup to single value in index?

Shakira1 — Mon, 04 Apr 2022 16:32:46 GMT

HI all,

I have lookup table with 5 colon that contains IPs

I want to create a search that exclude the IPs from my results, the issue is that I have 5 values and all of them should be match to 1 single value

example:

its not working. anyone?

thanks!

Re: compare multiple value in lookup to single value in index

ITWhisperer — Mon, 04 Apr 2022 16:07:06 GMT

Try something like this

Re: compare multiple value in lookup to single value in index

PickleRick — Mon, 04 Apr 2022 16:36:52 GMT

Couldn't you do that just with format?

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 06:42:09 GMT

no 😞 I'v tried and no success.

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 07:17:05 GMT

maybe I did wrong but I tried to do it in few ways:

mysearch
| search NOT
[mylookup.csv
| format]
| dedup IP
| table IP

OR:

mysearch
| search NOT
[ inputlookup IP.csv

| fields IP1,IP2,IP3,IP4, IP_from_my_search

| format]
| dedup IP
| table IP

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 07:31:29 GMT

Did you try my solution?

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 07:34:24 GMT

but what is IP? I need to rename the lookup fields to "IP"? as I did?

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 07:42:20 GMT

I assumed from your search that IP was a field in your existing events that you were trying to match (and exclude events which matched any of the values in 1v, 2v, etc from your lookup file).

If this is not the case, please can you explain what your search was trying to do

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 07:54:17 GMT

I have one field from my search that should be much to one if the fields in the lookups

I tried to do your solution:

this is what you mean?

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 08:04:48 GMT

How is this the same as my solution?

Assuming your search returns events with a field called IP and your lookup file contains rows with fields called V1, V2 etc (this has changed from 1V in your original post) - if this is not true, please provide an accurate description of your scenario

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 08:12:24 GMT

This is not entirely true, I have 4 more fields, How do I compare them from my lookup to my search? this is what I missed..

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 08:16:00 GMT

Exactly which fields do you have in your lookup and exactly which field (or fields) in your search are you trying to match against? You can use real field names, just don't include real field values

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 08:19:21 GMT

so, IP - is the field from my search (not in the lookup)

V1,V2,V3,V4,V5 is from mylookup (which is also IPs with a diff names) - they should be match to IP fields

after we compare it - I want to exclude it from my search and show only the results that not contains the IPs from my lookup

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 08:38:32 GMT

So, to simplify it, you want something like this

my search | search NOT (IP=1.1.1.1 OR IP=2.2.2.2) | table IP

Now the IP addresses (1.1.1.1 and 2.2.2.2 in this example) are held in lookup file in fields called V1, V2 etc.

In order to get them in a field called IP, we first have to create a multivalue field called IP with each of them in from each row of the lookup file.

We can then use mvexpand to put them in separate events, which we then dedup (in case there are any duplicates in the whole file)

We now have a set of events with a singe field call IP with one event for each unique ip address in the lookup file. We can then plug this list into the main search (using NOT to exclude events with these ip addresses in the matching IP field

Is this not what you want?

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 08:56:26 GMT

by I have 5 IPs fields in my lookup which contains more then 1000 IPs

so I cant do the one condition and this is the reason I used lookup....

Re: compare multiple value in lookup to single value in index

ITWhisperer — Tue, 05 Apr 2022 09:06:31 GMT

OK that was an important piece of information you could have mentioned earlier!

What was not working about your original option (corrected typos?)

What does job inspector say when you execute the search?

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 09:11:51 GMT

the result are missing.

I know for sure that one of the IP should show up and its not shown, I dont know what I missed

Re: compare multiple value in lookup to single value in index

PickleRick — Tue, 05 Apr 2022 09:18:28 GMT

OK, can you show us for reference some part of your logs, and few rows of your lookup?

And what should be the result from those logs and lookup?

Re: compare multiple value in lookup to single value in index

Shakira1 — Tue, 05 Apr 2022 09:25:19 GMT

my lookup is like that:

public_ip ,other_ip, more_ip

1.1.1.1, 2.2.2.2, 3.3.3.3

4.4.4.4,5.5.5.5,6.6.6.6 etc.....

from my search

IP=9.9.9.9

that's it.

when I'm looking the IP 9.9.9.9 I see its match to myseatch (my condition) and its not appear on my lookup - so I should get it into my result

I hope its more clear now

Re: compare multiple value in lookup to single value in index

PickleRick — Tue, 05 Apr 2022 09:34:47 GMT

OK. So as long as your 9.9.9.9 IP is not included in the lookup in any of the fields, it should be shown as a valid result. But if you had this IP in your lookup in any of the fields, it should be filtered out, right?

That's what @ITWhisperer 's solution does (as long as the field names are OK with your actual names in your splunk).

If you're not getting a particular IP address in your results which you believe should be there, verify if it really is so.

Check your

mycontiation IP=<your_ip>

and

| inputlookup mylookup.csv | where V1=<your_ip> OR V2=<your_ip> OR ...