Kusto Geo IP Light Table

Description


This very small and efficient dataset gives geographical to IP lookup within Kusto, otherwise known as geolocation. Since the small size of this table has been a priority you may need to link the country code with the actual name.

Update 7 June 2023


The new inbuilt Kusto function geo-info-from-ip-address has just been released. Thank you Kusto team :)

// Geoip POC using inbuilt Kusto function
// Test randomly generated IP addresses
//*** Variables start
let NumberOfIPsToTest = 100;
//*** Variables end
let IPsTesting = materialize(
range Position from 1 to (NumberOfIPsToTest) step 1 //Generate x random IP Addresses for testing
| extend IpAddress = strcat(toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255))));
IPsTesting
| extend Geo = geo_info_from_ip_address(IpAddress)
| evaluate bag_unpack(Geo)
| order by Position asc

Source


From the always awesome IPdeny service. https://www.ipdeny.com/ipblocks/

Why should I use this data?


It has been specifically generated to be performant with ~800K download size and ~140MB memory usage in Kusto and as such it tends to operate very quickly. It returns the country code and not the full country, but this is easily fixed with joining the country codes file as in the examples below.

Updates


Daily at around 0300UTC. The source data may or may not be updated as regularly.


https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip

Schema - kusto-cidr-countries-simple


Column Name Data Type Notes
CIDRCountry string Two letter country code, “ZZ” in original data but appears to be duplicates of known countries, this is removed from this table
CIDR string
CIDRSource string Always IPDeny

Schema - kusto-iso-country-codes


Column Name Data Type Notes
CIDRCountry string Two letter country code
CIDRCountryName string Long hand country name

Base Kusto Table


externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip'] with (ignoreFirstRecord=true)

Base Kusto Function


let CIDRRanges = externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string)  ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip'] with (ignoreFirstRecord=true);

Self Contained Kusto


// Geoip POC
// Test randomly generated IP addresses
//*** Variables start
let NumberOfIPsToTest = 100;
//*** Variables end
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string) //Table containing CIDR to ISO Country codes
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip']
with (ignoreFirstRecord=true));
let CIDRCountries = (externaldata (CIDRCountry:string, CIDRCountryName:string) //ISO country codes to full names
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-iso-country-codes.csv.zip']
with (ignoreFirstRecord=true));
let IPsTesting = materialize(
range Position from 1 to (NumberOfIPsToTest) step 1 //Generate x random IP Addresses for testing
| extend IpAddress = strcat(toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255))));
IPsTesting
| evaluate ipv4_lookup (CIDRRanges, IpAddress, CIDR, return_unmatched=true)
| join kind=leftouter (CIDRCountries) on CIDRCountry
| project-away *1
// Which countries have the most IP addresses?
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string) //Table containing CIDR to ISO Country codes
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip']
with (ignoreFirstRecord=true));
let CIDRCountries = (externaldata (CIDRCountry:string, CIDRCountryName:string) //ISO country codes to full names
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-iso-country-codes.csv.zip']
with (ignoreFirstRecord=true));
CIDRRanges
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| where CIDRCountry !startswith 'RFC'
| summarize TotalIpAddress = sum(NumberOfIPs) by CIDRCountry
| join kind=leftouter (CIDRCountries) on CIDRCountry
| project-away CIDRCountry, CIDRCountry1
| order by TotalIpAddress desc

MDE Example


Coming soon.

Sentinel & Azure Log Analytics Example


Coming soon.