topic How can I exclude certain IP addresses from a query based on their presence in a lookup table? in Splunk Search

How can I exclude certain IP addresses from a query based on their presence in a lookup table?

balu1211 — Thu, 13 Apr 2023 10:21:59 GMT

Hi,
I'm looking for the search to exclude the ips present in the lookup table

ips comments

142.45.2.3 scanner
123.4.45.22 network

123.66.33.4 alert scanner

123.45.7.9 cisa scanner

I'm trying to exclude the ips with the name scanner in the comments section

Thanks

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

andrew_nelson — Thu, 13 Apr 2023 10:32:58 GMT

If the IP field in your data is ip.

If you need to exclude any type of scanner, try:

search index=abc ip=* | lookup iplookupfile.csv ips as ip OUTPUT comments |where !match(comments, "scanner")

If alert scanners/cisa scanner shouldnt be excluded, try:

search index=abc ip=* | lookup iplookupfile.csv ips as ip OUTPUT comments |where !match(comments, "^scanner")

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

balu1211 — Thu, 13 Apr 2023 11:36:04 GMT

@andrew_nelson ,

How would I be able to exclude src_ip and dest_ip combination?

Can we use *scanner* like this ?

|where !match(comments, "*scanner*")

Thanks.

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

andrew_nelson — Thu, 13 Apr 2023 13:10:00 GMT

The match( ) function is regex pattern matching, it'll match any strings that contain the value specified so you don't need to wildcard with it.

That can be a pain when you want an exact match which is why I provided the second option that uses "^scanner". This checks that the values starts with "scanner", although a better version would be "^scanner$", this would be an exact match.
^ : Start of string
$ : End of string

You can see the documentation here: Comparison and Conditional functions - Splunk Documentation
And for regex help, try somewhere like regex101: build, test, and debug regex

When you say a combo, do you mean if the src_ip OR the dest_ip is a scanner ?

<your search>
| lookup iplookupfile.csv ips as src_ip OUTPUT comments as src_ip_comments 
| lookup iplookupfile.csv ips as dest_ip OUTPUT comments as dest_ip_comments 
| where !match(src_ip_comments , "scanner") AND !match(dest_ip_comments , "scanner")

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

balu1211 — Thu, 13 Apr 2023 12:43:39 GMT

@andrew_nelson ,
yup src_ip and the dest_ip is a scanner !

IPs from that lookup table should not match src ip as well as dest ip............

Error in 'lookup' command: Cannot find the destination field 'src_ip_comments' in the lookup table 'addresses.csv'. is the an error

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

balu1211 — Thu, 13 Apr 2023 12:34:29 GMT

@andrew_nelson ,

How to exclude ip address to cidr in lookup table?

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

andrew_nelson — Thu, 13 Apr 2023 13:11:13 GMT

Made tweaks to the search above to fix the lookup error.

For CIDR lookup, you'll need to create a lookup definition and configure advanced settings for match type CIDR(ips)

Re: How can I exclude certain IP addresses from a query based on their presence in a lookup table?

woodcock — Fri, 14 Apr 2023 18:35:41 GMT

| tstats ,values(All_Traffic.dest_port) AS dest_port values(All_Traffic.dest_ip) AS dest_ip dc(All_Traffic.dest_ip) AS num_dest_ip dc(All_Traffic.dest_port) AS num_dest_port
FROM datamodel=Network_Traffic
WHERE index="firewall" AND sourcetype="traffic"
BY All_Traffic.src_ip
| rename All_Traffic.* AS *
| where (num_dest_ip > 350 AND num_dest_port > 800)
| lookup address.csv Ips AS src_ip OUTPUT comments AS src_comments
| where NOT match(src_comments, "(?i)scanner)")
| lookup address.csv Ips AS dest_ip OUTPUT comments AS dest_comments
| where NOT match(dest_comments, "(?i)scanner)")