Kusto Geo IP Table

Description

This is a full Geo IP lookup for use in Kusto, it is a larger dataset and is supplied via Maxmind.

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

// Comparing inbuilt Kusto geo functions and external records what is the delta?
// Note: This just helps clarify the difficulties in using such threat intelligence
// Note: If CIDR blocks have been split these won't necessarily be picked up
externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip'] with (ignoreFirstRecord=true)
| extend IndicativeIpAddress = tostring(split(CIDR, '/')[0])
| extend CountryName = geo_info_from_ip_address(IndicativeIpAddress)['country']
| where CIDRCountryName !in ('IETF', '') and CountryName !in ('') and CIDRCountryName != CountryName
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| extend Countries = bag_pack("Countries", array_sort_asc(pack_array(CIDRCountryName, CountryName)))
| summarize NumberOfIPs = sum(NumberOfIPs) by tostring(Countries)
| render piechart

Source

This product includes GeoLite2 data created by MaxMind, available from https://www.maxmind.com

Why should I use this data?

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

Schema

Base Kusto Table

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

Base Kusto Function

let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.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, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.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)
| order by Position asc

// Which continents have the most IP addresses?
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip']
with (ignoreFirstRecord=true));
CIDRRanges
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| where CIDRCountryName !startswith 'RFC'
| summarize TotalIpAddress = sum(NumberOfIPs) by CIDRContinentName
| order by TotalIpAddress desc

Microsoft 365 Defender Example

// Which countries are our web connections going to?
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip'] with (ignoreFirstRecord=true));
DeviceNetworkEvents
| where floor(Timestamp, 1h) > ago(25h)
| where RemoteIPType == 'Public'
| where ActionType == 'ConnectionSuccess'
| where RemotePort in (80, 443)
| where RemoteIP has '.' //Check for IPv4
| summarize count() by RemoteIP, floor(Timestamp, 1h)
| evaluate ipv4_lookup(CIDRRanges, RemoteIP, CIDR, return_unmatched=true)
| extend CIDRCountryName = iif(isempty(CIDRCountryName), 'Unknown', CIDRCountryName)
| summarize sum(count_) by CIDRCountryName, Timestamp
| render timechart 

Sentinel & Azure Log Analytics Example

// For Azure CDN Traffic which Continents account for the bandwidth being used
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip'] with (ignoreFirstRecord=true));
AzureDiagnostics
| where Category == 'AzureCdnAccessLog'
| summarize sum(toint(responseBytes_s)) by clientIp_s
| evaluate ipv4_lookup(CIDRRanges, clientIp_s, CIDR, return_unmatched=true)
| summarize sum(sum_responseBytes_s) by CIDRContinentName
| render piechart