• Home
  • The Song
  • The Avatar
  • The Cat
  • Contact the Cat

Gyp the Cat dot Com

Kusto Geolocation IP Lookup
Uncategorized

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.

Related

Written by gyp - November 7, 2022 - 5040 Views

No Comment

Please Post Your Comments & Reviews
Cancel reply

Your email address will not be published. Required fields are marked *

Previous Post
Next Post

Latest Posts

  • How to Convert CSV to Parquet Easily with Python on Linux Shell
  • Kusto Geolocation IP Lookup
  • Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
  • HTTP to HTTPS Redirect on Azure CDN
  • Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway
  • Black Ops 3 NAT Type Strict & PS4 NAT Type 3 with pfSense Fixed!
  • Sorry for the lack of posts
  • How to Block Internet Access with Group Policy (GPO)
  • Enforcing Microsoft Office 365 and Azure Tennancy with McAfee Web Gateway (MWG)
  • Scanning Subnet for Issuing Certificate Authority with OpenSSL

Top Posts & Pages

  • How to Block Internet Access with Group Policy (GPO)
    How to Block Internet Access with Group Policy (GPO)
  • How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
    How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
  • Kusto Geolocation IP Lookup
    Kusto Geolocation IP Lookup
  • Tinyproxy A Quick and Easy Proxy Server on Ubuntu
    Tinyproxy A Quick and Easy Proxy Server on Ubuntu
  • Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
    Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
  • How to DNSPerf on Ubuntu 14.04 with Installation and Quick Start
    How to DNSPerf on Ubuntu 14.04 with Installation and Quick Start
  • How to Add Different Disclaimers using alterMIME and Postfix based on Domain
    How to Add Different Disclaimers using alterMIME and Postfix based on Domain
  • Blocking Countries on Nginx without the GeoIP Module
    Blocking Countries on Nginx without the GeoIP Module
  • How to Enable Squid Anonymous Stealth Mode
    How to Enable Squid Anonymous Stealth Mode
  • Configuring Suite B, VPN-A and VPN-B in IPSec with Strongswan
    Configuring Suite B, VPN-A and VPN-B in IPSec with Strongswan

Tags

apache2 azure azure log analytics blops business centos cheating cissp cloudflare cryptography dns game google gyp internet iphone ipsec isc linux mac marketing microsoft mw2 mx mysql nginx pfsense postfix proxy ps3 qualification radius revision security seo smtp socks squid ssh strongswan tinyproxy ubuntu windows 2012 wordpress xdecrypt.com
Gyp the Cat dot Com

Some rights retained Gyp the Cat Dot Com