Geolocation query from IP address

Hi, Any idea if that's possible (and if yes - how) to add resolving of IP address to geolocation and any other IP information in a query in Log Analytics? For example, part of the message body I hav...

azure monitor

Query Language

CliveWatson

Former Employee

Aug 20, 2019

povlhp

Could you use the database/csv files online (like this example)

// source: https://datahub.io/core/geoip2-ipv4 
externaldata(Network:string, geoname_id:string, continent_code:string, continent_name:string, country_iso_code:string, country_name:string)
[@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"]

Results:

Network	geoname_id	continent_code	continent_name	country_iso_code	country_name
network	geoname_id	continent_code	continent_name	country_iso_code	country_name
41.74.160.0/20	49518	AF	Africa	RW	Rwanda

Then merge the data (Join)... This is just a sample but could give you the idea...

// source: https://datahub.io/core/geoip2-ipv4 
externaldata(Network:string, geoname_id:string, continent_code:string, continent_name:string, country_iso_code:string, country_name:string)
[@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"]
| extend   trimIP  = trim(@"[^\w]+",tostring(split(Network, "/",0)))
| join kind= inner (
    SigninLogs
    | limit 10
) on $left.country_iso_code == $right.Location
//| where trimIP == IPAddress
| project Location , country_iso_code, IPAddress , trimIP

Results:

Go to Log Analytics and Run Query

Location	country_iso_code	IPAddress	trimIP
DE	DE	80.187.85.39	217.224.0.0
DE	DE	80.187.85.39	217.212.225.0
DE	DE	80.187.85.39	217.199.192.0
DE	DE	80.187.85.39	217.199.64.0
DE	DE	80.187.85.39	217.198.240.0
DE	DE	80.187.85.39	217.198.140.0
DE	DE	80.187.85.39	217.198.128.0
DE	DE	80.187.85.39	217.197.80.0
DE	DE	80.187.85.39	217.195.32.0
DE	DE	80.187.85.39	217.195.0.0

SocInABox

Iron Contributor

Dec 08, 2021

CliveWatson ,

I have this kql query, which works, but I'm unable to project the SubNetName from the watchlist:
(CriticalNetworks has 2 fields - SubNet and SubNetName)
Thoughts?

let watchlist = _GetWatchlist('CriticalNetworks');

SecurityAlert

| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))

|mv-expand Entities

|extend Address_ = tostring(Entities.Address)

|where Address_ <> ""

| evaluate ipv4_lookup(watchlist, Address_, SubNet)

| summarize count() by Address_, SubNet