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.


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

Schema


Column Name Data Type Notes
CIDRCountry string
CIDR string
CIDRCountryName string
CIDRContinent string
CIDRContinentName string
CIDRSource string

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