Splunk Search

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

peetchow
Loves-to-Learn Lots

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

Labels (1)
0 Karma

peetchow
Loves-to-Learn Lots

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 

 

0 Karma

saravanan90
Contributor

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"

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...