topic Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results in Getting Data In

Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

rip_leroi — Thu, 28 Feb 2019 19:46:00 GMT

I'm brand new to Splunk and I'm having difficulty getting a query to return the results I'm looking for. I've checked the knowledge base and I see references to using transforms.conf and props.conf, but I'm in an Enterprise environment, and I don't have access to change those files.

I have a .csv lookup I've created that contains a column for CIDR ranges and a column to indicate whether the CIDR range should be whitelisted from search results. I'm trying run a query for any SMB that is incoming from the internet. My query is listed below.

index=netfw sourcetype=cisco:asa dest_port=445 NOT Cisco_ASA_action=teardown NOT transport=icmp
| lookup smbwhitelist.csv dest_ip OUTPUTNEW iswhitelist as whitelist
| search NOT whitelist IN ("yes")

This returns all traffic including all the CIDR ranges that I'm trying to whitelist and exclude from the search results. If I remove the NOT from the search, then I receive no results. However, if I manually paste in the CIDR ranges I get results, so I'm missing something.

Any pointers in the right direction would be greatly appreciated.

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

integratorz — Fri, 01 Mar 2019 01:02:16 GMT

Hey @rip_leroi,

Please try this search and let me know how it works.

 index=netfw sourcetype=cisco:asa dest_port=445 Cisco_ASA_action!=teardown transport!=icmp
 | lookup smbwhitelist.csv dest_ip OUTPUT iswhitelist as whitelist
 | search whitelist!="yes"

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

HiroshiSatoh — Fri, 01 Mar 2019 01:14:16 GMT

Since it is 0, the value is different, or the field does not exist.
Check the value set for ”whitelist ”.

As a result

 index=netfw sourcetype=cisco:asa dest_port=445 NOT Cisco_ASA_action=teardown NOT transport=icmp
 | lookup smbwhitelist.csv dest_ip OUTPUTNEW iswhitelist as whitelist

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

rip_leroi — Fri, 01 Mar 2019 22:24:56 GMT

I appreciate the comment. This returns all results including the CIDR ranges that I'm looking to exclude.

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

rip_leroi — Fri, 01 Mar 2019 22:25:29 GMT

This doesn't return any results but thank you for the suggestion.

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

HiroshiSatoh — Mon, 04 Mar 2019 06:43:07 GMT

If the result is not returned so far, it is not a problem of the subsequent condition.

Re: Using lookup table for whitelisting CIDR ranges in SPL and getting zero results

rip_leroi — Tue, 29 Sep 2020 23:33:14 GMT

You were correct. I removed the NOT Cisco_ASA_action=teardown NOT transport=icmp and started seeing results. Apparently, my whitelist excluded every alert and there were no other matching record. I appreciate you taking the time to help.