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

[Shakira1]

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:

mycontiation | search NOT [ inputlookup mylookup.csv ] |rename 1V as IP |fields IP]
| search NOT [|inputlookup mylookup.csv  |rename 2v as IP |fields IP]
| search NOT[|inputlookup mylookup.csv |rename 3v as IP |fields IP]
| search NOT [|inputlookup mylookup.csv |rename 4v  as IP |fields IP]
| search NOT [|inputlookup mylookup.csv |rename 5v as IP |fields IP] | table IP 

its not working. anyone? 

thanks!

[ITWhisperer]

Try something like this

mycontiation | search NOT [ | inputlookup mylookup.csv 
| eval IP=null()
| foreach *v
    [| eval IP=if(isnull(IP),'<<FIELD>>',mvappend(IP,'<<FIELD>>'))]
| fields IP
| mvexpand IP
| dedup IP]
| table IP 

[Shakira1]

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

[ITWhisperer]

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

[Shakira1]

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:

my search
| search NOT
[ inputlookup mylookupcsv
| rename V1 as IP
| rename v2 as IP
| rename V3 as IP
| rename V4 as IP
| rename V5 as IP
| eval IP=null()
| foreach *
[| eval sourceIPAddress=if(isnull(IP),"V1",mvappend(IP,V1)),
| eval sourceIPAddress=if(isnull(IP),"V2",mvappend(IP,V2))]
| fields IP
| mvexpand IP
| dedup IP]
| table IP

this is what you mean?

[ITWhisperer]

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

my search
| search NOT [ 
  | inputlookup mylookup.csv 
  | eval IP=null()
  | foreach V*
    [| eval IP=if(isnull(IP),'<<FIELD>>',mvappend(IP,'<<FIELD>>'))]
  | fields IP
  | mvexpand IP
  | dedup IP]
| table IP 

[Shakira1]

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

[ITWhisperer]

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

[Shakira1]

OK 

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 

[ITWhisperer]

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.

| inputlookup mylookup.csv 
| eval IP=null()
| foreach V*
    [| eval IP=if(isnull(IP),'<<FIELD>>',mvappend(IP,'<<FIELD>>'))]
| fields IP

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)

| inputlookup mylookup.csv 
| eval IP=null()
| foreach V*
    [| eval IP=if(isnull(IP),'<<FIELD>>',mvappend(IP,'<<FIELD>>'))]
| fields IP
| mvexpand IP
| dedup IP

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

my search
| search NOT [ 
  | inputlookup mylookup.csv 
  | eval IP=null()
  | foreach V*
    [| eval IP=if(isnull(IP),'<<FIELD>>',mvappend(IP,'<<FIELD>>'))]
  | fields IP
  | mvexpand IP
  | dedup IP]
| table IP 

Is this not what you want?

[Shakira1]

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

[ITWhisperer]

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

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

mycontiation 
| search NOT [| inputlookup mylookup.csv |rename V1 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V2 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V3 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V4 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V5 as IP |fields IP]
| table IP 

What does job inspector say when you execute the search?

[Shakira1]

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 

[PickleRick]

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?

[Shakira1]

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

[PickleRick]

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

mycontiation 
| search NOT [| inputlookup mylookup.csv |rename V1 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V2 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V3 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V4 as IP |fields IP]
| search NOT [| inputlookup mylookup.csv |rename V5 as IP |fields IP]
| table IP 

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

[Shakira1]

OK, thank you so much for the help!

[PickleRick]

Couldn't you do that just with format?

[Shakira1]

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

[Shakira1]

no 😞 I'v tried and no success.

[ITWhisperer]

Did you try my solution?