Why is Lookup command not working?

SplunkExplorer — Thu, 22 Jun 2023 17:55:13 GMT

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:

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:

I changed correctly lookup file permissions:

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

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

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.

Re: Lookup command not working

yuanliu — Thu, 22 Jun 2023 17:24:05 GMT

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.

Re: Lookup command not working

SplunkExplorer — Thu, 22 Jun 2023 19:21:01 GMT

You are right, I confuse the logic. Thanks.

topic Why is Lookup command not working? in Splunk Search

Why is Lookup command not working?

Re: Lookup command not working

Re: Lookup command not working