Forum Discussion
Geolocation query from IP address
Of course you learn to spot chinese IP adresses over time, but would be easier if we just had a function to do it. The lookup is in the Azure AD login log. So Microsoft do have the IP->Location database available. They just need to fint somebody who wants to take responsibility.
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 |
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
Great example. Would like do something similar where there isn't an existing location and match on IP to CIDR. Do you know if there is a function that match IP to CIDR and how you could use it in your Join example?
Please take a look at the Compare and is_match options we now have (since this post was written), for ipv4 ad 6:
https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ipv4-comparefunction
CliveWatson and all....
Can someone tell me how to add a count to this result?
i.e. I need: IP, country_name and count.I know I could NOT summarize and do the count at the end, but that would kill the query and it would time out, so the summarize has to be done earlier on as shown below.
let geoData =
materialize (externaldata(network:string,geoname_id:string,continent_code:string,continent_name:string,
country_iso_code:string,country_name:string,is_anonymous_proxy:string,is_satellite_provider:string)
[@"https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv"] with
(ignoreFirstRecord=true, format="csv"));
let lookup = toscalar( geoData | summarize list_CIDR=make_set(network) );
SecurityAlert
| where TimeGenerated > ago(12h)
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
|mv-expand Entities
|extend IP = tostring(Entities.Address)
|extend countrycode = Entities.Location.CountryCode
| summarize by IP // if you don't summarize, this query will likely time out.
| mv-apply list_CIDR=lookup to typeof(string) on
(
where ipv4_is_match (IP, list_CIDR) //== false
)
//| join kind=rightouter (AzureActivity | where TimeGenerated > ago(12h)) on IP
| join kind=leftouter
(
geoData
) on $left.list_CIDR == $right.network
|project IP, country_name
| where country_name <> "United States"
