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
povlhp's avatar
povlhp
Copper Contributor
Aug 19, 2019
I also want / need this.
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.
  • 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

       

    • Secureskydev's avatar
      Secureskydev
      Copper Contributor
      Jun 25, 2020

      CliveWatson 

      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? 

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jun 25, 2020

        Secureskydev 

         

        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

Resources