- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How can I exclude certain IP addresses from a query based on their presence in a lookup table?
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
![woodcock woodcock](https://community.splunk.com/legacyfs/online/avatars/1493.jpg)
| 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)")
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How would I be able to exclude src_ip and dest_ip combination?
Can we use *scanner* like this ?
|where !match(comments, "*scanner*")
Thanks.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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")
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@andrew_nelson ,
How to exclude ip address to cidr in lookup table?
![](/skins/images/53C7C94B4DD15F7CACC6D77B9B4D55BF/responsive_peak/images/icon_anonymous_message.png)