Lookup Table Comparison with field and to return field value that is not in the lookup

[peetchow]

All,

I know there are a lot of postings with answers on lookup tables but I am still stuck.  I have not splunked in a few years and i hit a wall even when looking back at some of my old saved strings.

I have a csv file that has 2 columns.  One that contains IPAddress and the other that has SubnetMasks

I am searching in my logs for IPAdresses that i want to compare with the IPAddresses that are in the lookup csv file.  if the IPAddresses are not found ... then display them in a table.

MY query is as follows:

index=blah  field3="*" | fields field3 field4 | dedup field3 | rename field3 as Source_IP | lookup ip_whitelist IPAddress AS Source_IP | eval InWhitelist="Yes" | table Source_IP IPAddress field4 InWhitelist | where InWhitelist="Yes" | sort -Source_IP

• where field3 is the field with the IP Addresses (extracted from delimited extractions)
• where field4 is the field that has the hostname

This spits out a nice table but i notice IPs that are not in my whitelist are showing up.

What is wrong here !? 

Your help is greatly appreciated ! 

Thanks

P

[peetchow]

Sadly it did not work, any IP i put in for field3 (whether in the whitelist or not) displayed in a table with value of "Yes" for InWhitelist 

Also field4 that holds the hostname does not carry over to the table 

[saravanan90]

This may help...

Below will search in the lookup and pull the results when the ip is not available in lookup.

| makeresults | eval field3="192.168.1.6", field4="hostname" | fields field3 field4 | dedup field3 | rename field3 as Source_IP | lookup ip_whitelist  IPAddresses AS Source_IP | eval InWhitelist=if(isnull(SubnetMasks),"Yes","No") | table Source_IP field4 InWhitelist SubnetMasks | where InWhitelist="Yes"