KQL(Sentinel) - Exclude IP ranges from a watchlist in query results.

Occasional Contributor



Assuming the following watchlist that contains IP ranges by start and end IPs:

startIP   endIP


I am querying a table named "NetworkTraffic" with a column named "SourceIP" that contains a single IP address (e.g.


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 -, the query will return 0 results.  If there is one record from an IP address 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!



2 Replies

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





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







let blacklist = datatable

(ip:string) [





| extend ip = ip2int(ip)

| join kind = leftanti



    | extend ip = ip2int(ip)


    on ip

Output is: 

Thanks for that. That is an interesting approach to solve the problem.