Kusto Geo IP Table

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

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

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

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

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