Aug 24 2022 04:12 AM - edited Aug 24 2022 04:18 AM
Hi!
Assuming the following watchlist that contains IP ranges by start and end IPs:
startIP | endIP |
20.20.0.0 | 20.20.255.255 |
30.30.0.0 | 30.30.255.255 |
I am querying a table named "NetworkTraffic" with a column named "SourceIP" that contains a single IP address (e.g. 20.20.155.25).
My goal is to exclude records in which the source IP value from the column SourceIP is in one of the above watchlist ranges. Like a whitelist of IP ranges of sorts.
For example, if all the traffic in the table is from IPs in range 20.20.0.0 - 20.20.255.255, the query will return 0 results. If there is one record from an IP address 55.55.150.150 only it will be returned.
What is the best way to achieve that with keeping the query DRY in mind?
I tried to look for a solution online but no luck.. :(
Thanks in advance!
Ben
Aug 24 2022 10:26 PM - edited Aug 24 2022 10:27 PM
Hi Ben, here's one idea. You might use a function that converts your IP to int. Then it's easy to compare it:
.create-or-alter function ip2int(ip:string){
let y = split(ip,".");
let one = toint(y[0]);
let two = toint(y[1]);
let three = toint(y[2]);
let four = toint(y[3]);
let all = toint(strcat(one, two, three, four));
all
}
Now let's say you transform your blacklist a little bit, such that you don't have ranges (startIP, endIP) but single values. Then you use a simple leftanti join to exclude the values in the blacklist:
let network_traffic = datatable
(ip:string) [
"20.20.155.21",
"20.20.155.22",
"20.20.155.23",
"20.20.155.24",
"20.20.155.25"
];
let blacklist = datatable
(ip:string) [
"20.20.155.24",
"20.20.155.25"
];
network_traffic
| extend ip = ip2int(ip)
| join kind = leftanti
(
blacklist
| extend ip = ip2int(ip)
)
on ip
Output is:
ip |
202015523 |
202015522 |
202015521 |
Aug 25 2022 01:26 AM
Jul 31 2023 09:34 AM
@ben_loy Did you every solve your issue. I am trying the same thing. I would have thought maybee using the function ipv4_is_in_range would work but I'm probably messing up the parsing.