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

Occasional Contributor

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

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

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
Hi,
Thanks for that. That is an interesting approach to solve the problem.