Kusto Geolocation IP Lookup

As far as I know Kusto (or KQL) does not have geolocation to IP address functionality built in.  I know that geolocation is often fraught and is never perfect however I have found that it can be another useful resource to leverage when looking through large amounts of data.

About a year ago I created a publicly available geolocation external table for Kusto and have been using it personally and shared details with some others too.  I am happy now to share this with the world 🙂

The following should work on just about every implementation of Kusto be it Microsoft Defender for Endpoint, Azure Sentinel, and Azure Data Explorer to name a few.

I have created two separate yet similar external tables for Kusto:

1 – A small high performant “simple” and light dataset from data from IPDeny.com which only gives the CIDR and Country

2 – A larger more complex dataset from MaxMind GeoLite 2 which gives CIDR, country, continent and a few other details.

Base Table Light

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

Base Table Complex

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));

Example With Specified IP Addresses

This query takes a list of IP addresses and gives you the geographical location of them.

// Given some IP address what is their geographic location?
let IPAddressesOfInterest = datatable(IpAddress:string) [
'208.65.153.238',
'8.8.8.8',
'8.8.4.4',
'123.123.123.123'
];
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));
IPAddressesOfInterest
| evaluate ipv4_lookup(CIDRRanges, IpAddress, CIDR, return_unmatched=true)

Example With Storage Account Web Logs

Given a static website running on an Azure Storage Account what connections have we seen from specified countries?

// Given web logs for a storage account what connections have we seen from specefic countries?
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));
StorageBlobLogs
| where OperationName == 'GetWebContent'
| where toint(StatusCode) between (200 .. 299)
| evaluate ipv4_lookup(CIDRRanges, CallerIpAddress, CIDR, return_unmatched=true)
| where CIDRCountryName in ('United States', 'Finland')

Example With Microsoft Defender for Endpoint

With MDE what connections are we seeing to and from specific countries?

// Given a country what connections can we see to and from them?
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
| summarize by RemoteIP
| evaluate ipv4_lookup(CIDRRanges, RemoteIP, CIDR, return_unmatched=true)
| where CIDRCountry in ('MX', 'AU')
| join kind=leftouter (DeviceNetworkEvents) on RemoteIP

Examples

There are a few other examples on the specific pages for “light” geolocation table and the fuller IP lookup table too.