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

[balu1211]

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

[woodcock]

| 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)")

[andrew_nelson]

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")

[balu1211]

@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. 

[andrew_nelson]

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")

[balu1211]

@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 

[andrew_nelson]

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)

[balu1211]

@andrew_nelson ,

How to exclude  ip address to cidr in lookup table?