Kusto query, comparing array of CIDR ranges to an IP

Question

I am trying to do something in Kusto similar to this post: Filter IPs if they are in list of ranges but using the IP ranges from a publicly available list to compare to some logs.

Here's what I have tried, I believe the issue relates to me not knowing how to reference the "network" property of the external data.

I get a "Query could not be parsed" error. Apologies for the formatting, I'm not sure how to make it respect line breaks.

let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv'];
let testIP = datatable (myip: string) ['4.28.114.50','4.59.176.50']; //Random IPs in Canada
testIP
| mv-apply tmpIP = IP_Data.network to typeof(string) on ( 
    where ipv4_is_in_range(myip, tmpIP
    )
| project-away tmpIP

user_6336479 · Answer

This answers the OP question directly, however there is a great solution for this scenario, based on the ipv4_lookup plugin.

See new answer

For both options -
Since the CSV has header, so I added with (ignoreFirstRecord = true) to the external_data

Option 1

testIP is defined as array (and not a single column table).
The base table is IP_Data but the mv-apply is done on testIP array. This enables you to access values from both IP_Data and testIP

let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv'] with (ignoreFirstRecord = true);
let testIP = dynamic(['4.28.114.50','4.59.176.50']); //Random IPs in Canada
IP_Data
| mv-apply testIP = testIP to typeof(string) on (where ipv4_is_in_range(testIP, network))

network	geoname_id	continent_code	continent_name	country_iso_code	country_name	is_anonymous_proxy	is_satellite_provider	testIP
4.28.114.0/24	6251999	NA	North America	CA	Canada	false	false	4.28.114.50
4.59.176.0/24	6251999	NA	North America	CA	Canada	false	false	4.59.176.50

Fiddle

Option 2

Cross join both tables (using a dummy column) and then filter the results

let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv'] with (ignoreFirstRecord = true);
let testIP = datatable (myip: string) ['4.28.114.50','4.59.176.50']; //Random IPs in Canada
testIP | extend dummy = 1
| join kind=inner (IP_Data | extend dummy = 1) on dummy
| where ipv4_is_in_range(myip, network)
| project-away dummy*

myip	network	geoname_id	continent_code	continent_name	country_iso_code	country_name	is_anonymous_proxy	is_satellite_provider
4.28.114.50	4.28.114.0/24	6251999	NA	North America	CA	Canada	false	false
4.59.176.50	4.59.176.0/24	6251999	NA	North America	CA	Canada	false	false

Fiddle

user_6336479 · Answer

New answer

Demo for 1M IPs, based on the ipv4_lookup plugin

let geoip2_ipv4 = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv'] with (ignoreFirstRecord = true)
| extend continent_name = coalesce(continent_name, '--- Missing ---');
let ips = materialize(range i from 1 to 1000000 step 1 | extend ip = format_ipv4(tolong(rand() * pow(2,32))));
ips
| evaluate ipv4_lookup(geoip2_ipv4, ip, network, return_unmatched = true)
| summarize count() by continent_name

continent_name	count_
North America	399059
Asia	201902
Europe	173566
South America	33795
Oceania	13384
Africa	17569
--- Missing ---	226
160499

Fiddle

Kusto query, comparing array of CIDR ranges to an IP

2 Answers