How to exclude traffic with src_ip or dest_ip using lookup file

[imst27]

Hi,

I’m building a search on the Network_Traffic datamodel to detect high outbound flows (>1 GB).
I need to exclude a list of known backup / AWS ranges, and the exclusion should apply if either src_ip or dest_ip matches any of them.

Right now my query looks like this (shortened for readability):

| tstats `security_content_summariesonly`
    sum(All_Traffic.bytes_out) as bytes_out
  from datamodel=Network_Traffic
  where All_Traffic.action=allowed
  by All_Traffic.src_ip All_Traffic.dest_ip _time span=1d
| `drop_dm_object_name("All_Traffic")`
| where bytes_out > 1073741824
| where NOT (
    cidrmatch("<internal_backup_subnet>/24", src_ip) OR cidrmatch("<internal_backup_subnet>/24", dest_ip)
 OR cidrmatch("<external_backup_subnet>/24", src_ip) OR cidrmatch("<external_backup_subnet>/24", dest_ip)
 … etc …
)
| table _time src_ip src_port dest_ip dest_port transport app vlan bytes_gb dvc rule action user dest_interface src_interface direction src_zone dest_zone

This works, but the WHERE NOT block is huge (I’ve got 40+ CIDRs).

I tried to utilized inputlookup and lookup file but failed, the lookup file backup-subnet-test.csv structure are as follow

subnet	comment
<subnet>/28	comment1
<subnet>/20	comment2
<subnet>/32	comment3

Question

• Is it possible to use a lookup file for this case? I want to use the lookup file (backup-subnet-test.csv) to exclude traffic if either src_ip or dest_ip matches a subnet in the lookup.
• Or is there a cleaner way to apply the CIDR lookup once against both fields?

Thanks a lot in advance!

[PrewinThomas]

@imst27 

Yes the lookup approach is much better. Its better to simplify giant cidrmatch() block into two clean lookup calls, one for src_ip and one for dest_ip.

Eg:

| lookup backup_subnets subnet as src_ip OUTPUT comment as src_match
| lookup backup_subnets subnet as dest_ip OUTPUT comment as dest_match
| where isnull(src_match) AND isnull(dest_match)
| table _time src_ip dest_ip src_match dest_match

Regards,
Prewin
If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!