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.
Perma Link
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 | Data provider, in this case 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.