Forum Discussion

philip-patrick's avatar
philip-patrick
Copper Contributor
Jan 08, 2019

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's avatar
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"]

 

 

https://portal.azure.com#@4b2462a4-bbee-495a-a0e1-f23ae524cc9c/blade/Microsoft_OperationsManagementSuite_Workspace/AnalyticsBlade/initiator/AnalyticsShareLinkToQuery/isQueryEditorVisible/true/scope/%7B%22resources%22%3A%5B%7B%22resourceId%22%3A%22%2Fsubscriptions%2F44e4eff8-1fcb-4a22-a7d6-992ac7286382%2FresourceGroups%2FSOC%2Fproviders%2FMicrosoft.OperationalInsights%2Fworkspaces%2FCyberSecurityDemo%22%7D%5D%7D/query/H4sIAAAAAAAAA42OQQrCMBBF94HcIXSloB0orrryBF5ApMR0aAfbTJlMq4KH1yJIERcu%2F%2FvvwwdwiUcJWLpWdUglQO3Vt%2BM5J4bAgtAg01BsaZh2zhq8KUr03WytDqhXlkuZVCg2G%2FdSo%2B%2BxovqDAkeliFGrwDX%2BwPNggceocq8o8bf%2FLhb22prjPvvjNcgy5SFN2cmah%2BuoJ3WFNU98IXQuBQEAAA%3D%3D/isQueryBase64Compressed/true/timespanInIsoFormat/P1D

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:

https://portal.azure.com#@4b2462a4-bbee-495a-a0e1-f23ae524cc9c/blade/Microsoft_OperationsManagementSuite_Workspace/AnalyticsBlade/initiator/AnalyticsShareLinkToQuery/isQueryEditorVisible/true/scope/%7B%22resources%22%3A%5B%7B%22resourceId%22%3A%22%2Fsubscriptions%2F44e4eff8-1fcb-4a22-a7d6-992ac7286382%2FresourceGroups%2FSOC%2Fproviders%2FMicrosoft.OperationalInsights%2Fworkspaces%2FCyberSecurityDemo%22%7D%5D%7D/query/H4sIAAAAAAAAA41QwWrCUBC8B%2FIPS%2FCQ0DTPlp6EgD0KUoQerZU02SarcTe8t2oLfnyThlSxPfT2dnZmduYZA072NscJVKqNmxhTZJpV%2B7eExORi0ZQo1NzfUnN4AN%2FDD0XLWd2xwifUo9jtxKklLmNoqZztcE3FD5QLKzGyrnMp8A%2B4E1zAe1b7uSYn1%2Fx%2BccGOfG85Df6R2tjLKcndIVj53gm6KlwAQGu3my0A0u9XOA2Wry%2FH1U0Qq%2FSnQtfUpEPdGAITxOMoijqXjRDDlrhIgZjRQuh7rSc8U8nEcyldP5%2Bgph0p3I19LwJhGNX4rsl1Y0hTGFkqK03mkmdKwr5nzAmOFVocorak2eKxKCw614VorGwwVxgk8Psr47Oi3fY%2BnfSc6gvhCDqNDAIAAA%3D%3D/isQueryBase64Compressed/true/timespanInIsoFormat/P1D

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's avatar
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

 

Resources