Splunk Search

Why is Lookup command not working?

SplunkExplorer
Contributor

Hi Splunkers, I have an issue with a search that use a lookup. I know here on community there are a lots of post on this argument, but event reading them I'm still in struck.
My search must simple match this: verify if, from firewall logs, the destination ip match against the address contained on a lookup file and traffic is accepted/permitted.

The search is:

 

 

index=* sourcetype=cp_log direction=outbound action="Accept" 
| lookup tor_node tor_node_address as dst_ip output exclude 
| where isnull(exclude) 
| stats count by src_ip, dst_ip

 

 

Where:

  • the table name is tor_node
  • this table has 2 columns: tor_node_address, which contains ip address, and exclude, inserted to esclude temporary some IPs from matching if needed.

So, the query logic is: check if, in the events, the dst_ip field values match the lookup field named tor_node address ones and, with the use of isnull command, those IP are not marked with exclusion.

How I want to check if the value in the lookup must be excluded from matching or not?
If the cell of exclude column is empty, the row must be included in check; if it is populated, not.
So, if the exclude column is full empty, all data of tor_node_address must be matched by dst_ip.
That means that, if no dst_ip match tor_node_address, the search resut must be empty.

Here a graphical view of my lookup:


SplunkExplorer_0-1687446941150.png

As you can see, the table has been created with https://splunkbase.splunk.com/app/1724 

I ensured that, after saving file, the lookup is well populated with inputlookup command:

SplunkExplorer_1-1687447088747.png

I changed correctly lookup file permissions:

SplunkExplorer_2-1687447170289.pngSplunkExplorer_3-1687447190984.png

I created the related lookup definition and set correctly its permission:

SplunkExplorer_4-1687447278055.pngSplunkExplorer_5-1687447292699.png

And set no particular advanced settings, except avoid case sensitive match:

SplunkExplorer_6-1687447361542.png

So, what's the problem?
The search dos not perform the check.
Even if no dst_ip match the tor_node_address one, the search result is not empty.
If I launch it, I can see the same results gained with:

 

 

index=* sourcetype=cp_log direction=outbound action="Accept" 
| stats count by src_ip, dst_ip

 

 


It seems like the lookup command is totally ignored.

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

This is confusing.  The two screenshots revealing the content of the lookup shows exactly all null values in exclude field.  How can isnull(exclude) not output every event?  If there is a non-null value for exclude, maybe you should populate with that.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

This is confusing.  The two screenshots revealing the content of the lookup shows exactly all null values in exclude field.  How can isnull(exclude) not output every event?  If there is a non-null value for exclude, maybe you should populate with that.

SplunkExplorer
Contributor

You are right, I confuse the logic. Thanks.

Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...