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