Home

Geolocation query from IP address

%3CLINGO-SUB%20id%3D%22lingo-sub-310242%22%20slang%3D%22en-US%22%3EGeolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310242%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EAny%20idea%20if%20that's%20possible%20(and%20if%20yes%20-%20how)%20to%20add%20resolving%20of%20IP%20address%20to%20geolocation%26nbsp%3Band%26nbsp%3Bany%20other%20IP%20information%20in%20a%20query%20in%20Log%20Analytics%3F%20For%20example%2C%20part%20of%20the%20message%20body%20I%20have%20in%20custom%20log%20is%20IP%20address%2C%20I%20would%20like%20to%20add%20a%20column%20(e.g.%20-%20extend)%20that%20resolves%20this%20IP%20address%20to%20its%20location%20in%20the%20world.%20Alternatively%2C%20if%20there%20was%20an%20option%20to%20call%20a%26nbsp%3Brest%20service%20during%20query%2C%20I%20could%20call%20something%20like%20ipstack%2C%20and%20receive%20the%20required%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20of%20simple%20query%3A%3C%2FP%3E%3CPRE%3EMyEvents%3CBR%20%2F%3E%7C%20extend%20IPAddress%20%3D%20extractjson(%22%24.request.ipaddress%22%2C%20Message)%3CBR%20%2F%3E%7C%20extend%20Country%20%3D%20extractgeo(%22%24.country%22%2C%20IPAddress)%3C%2FPRE%3E%3CP%3EHopefully%20that%20was%20clear%20enough%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%20In%20PowerBI%20this%20can%20be%20achieved%20with%26nbsp%3B%3C%2FP%3E%3CPRE%3EJson.Document(Web.Contents(%22rest%20service%20url%22)....%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-310242%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352402%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352402%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20doesn't%20seem%20to%20be%20possible%2C%20but%20there%20might%20be%20a%20workaround.%20There%20are%20databases%20available%20for%20download%20that%20have%20the%20location%20of%20certain%20IP%20ranges.%20With%20a%20function%20that%20contains%20the%20database%20as%20a%20lookup%20table%2C%20it%20might%20be%20possible%20to%20compute%20the%20IP%20range%20on%20the%20fly%20when%20viewing%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20ever%20complete%20it%2C%20I%20will%20update%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310300%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310300%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EUnfortunately%20this%20is%20not%20possible%20as%20far%20as%20I%20know.%20May%20be%20the%20only%20workaround%20is%20to%20have%20some%20workflow%20that%20queries%20your%20data%20once%20every%20hour%2C%20finds%20the%20new%20IPs%20from%20your%20Log%20Analytics%20data%2C%20use%20those%20IPs%20to%20call%20external%20service%20to%20get%20the%20location%2C%20log%20back%20the%20location%20data%20in%20a%20separate%20table%20so%20it%20will%20be%20available%20for%20use%20when%20you%20use%20Log%20Analytics%20query.%20Of%20course%20the%20downside%20of%20this%20workaround%20is%20also%20that%20you%20will%20not%20be%20able%20to%20have%20the%20location%20data%20right%20away.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-658679%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-658679%22%20slang%3D%22en-US%22%3Edid%20you%20manage%20to%20solve%20this%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698357%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698357%22%20slang%3D%22en-US%22%3EDid%20you%20solve%20this%20-%20I'm%20very%20interested%20in%20getting%20this%20functionality%20%3A)%3C%2Fimg%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698861%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F162153%22%20target%3D%22_blank%22%3E%40Morten%20Waltorp%20Knudsen%3C%2FA%3E%26nbsp%3BI%20actually%20never%20got%20around%20to%20it...%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810728%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810728%22%20slang%3D%22en-US%22%3EI%20also%20want%20%2F%20need%20this.%3CBR%20%2F%3EOf%20course%20you%20learn%20to%20spot%20chinese%20IP%20adresses%20over%20time%2C%20but%20would%20be%20easier%20if%20we%20just%20had%20a%20function%20to%20do%20it.%20The%20lookup%20is%20in%20the%20Azure%20AD%20login%20log.%20So%20Microsoft%20do%20have%20the%20IP-%26gt%3BLocation%20database%20available.%20They%20just%20need%20to%20fint%20somebody%20who%20wants%20to%20take%20responsibility.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812476%22%20slang%3D%22en-US%22%3ERe%3A%20Geolocation%20query%20from%20IP%20address%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394517%22%20target%3D%22_blank%22%3E%40povlhp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20use%20the%20database%2Fcsv%20files%20online%20(like%20this%20example)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%20source%3A%20%3CA%20href%3D%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%3C%2FA%3E%20%0Aexternaldata(Network%3Astring%2C%20geoname_id%3Astring%2C%20continent_code%3Astring%2C%20continent_name%3Astring%2C%20country_iso_code%3Astring%2C%20country_name%3Astring)%0A%5B%40%22%3CA%20href%3D%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%3C%2FA%3E%22%5D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%23%404b2462a4-bbee-495a-a0e1-f23ae524cc9c%2Fblade%2FMicrosoft_OperationsManagementSuite_Workspace%2FAnalyticsBlade%2Finitiator%2FAnalyticsShareLinkToQuery%2FisQueryEditorVisible%2Ftrue%2Fscope%2F%257B%2522resources%2522%253A%255B%257B%2522resourceId%2522%253A%2522%252Fsubscriptions%252F44e4eff8-1fcb-4a22-a7d6-992ac7286382%252FresourceGroups%252FSOC%252Fproviders%252FMicrosoft.OperationalInsights%252Fworkspaces%252FCyberSecurityDemo%2522%257D%255D%257D%2Fquery%2FH4sIAAAAAAAAA42OQQrCMBBF94HcIXSloB0orrryBF5ApMR0aAfbTJlMq4KH1yJIERcu%252F%252FvvwwdwiUcJWLpWdUglQO3Vt%252BM5J4bAgtAg01BsaZh2zhq8KUr03WytDqhXlkuZVCg2G%252FdSo%252B%252BxovqDAkeliFGrwDX%252BwPNggceocq8o8bf%252FLhb22prjPvvjNcgy5SFN2cmah%252BuoJ3WFNU98IXQuBQEAAA%253D%253D%2FisQueryBase64Compressed%2Ftrue%2FtimespanInIsoFormat%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EResults%3A%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ENetwork%3C%2FTH%3E%0A%3CTH%3Egeoname_id%3C%2FTH%3E%0A%3CTH%3Econtinent_code%3C%2FTH%3E%0A%3CTH%3Econtinent_name%3C%2FTH%3E%0A%3CTH%3Ecountry_iso_code%3C%2FTH%3E%0A%3CTH%3Ecountry_name%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3Enetwork%3C%2FTD%3E%0A%3CTD%3Egeoname_id%3C%2FTD%3E%0A%3CTD%3Econtinent_code%3C%2FTD%3E%0A%3CTD%3Econtinent_name%3C%2FTD%3E%0A%3CTD%3Ecountry_iso_code%3C%2FTD%3E%0A%3CTD%3Ecountry_name%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E41.74.160.0%2F20%3C%2FTD%3E%0A%3CTD%3E49518%3C%2FTD%3E%0A%3CTD%3EAF%3C%2FTD%3E%0A%3CTD%3EAfrica%3C%2FTD%3E%0A%3CTD%3ERW%3C%2FTD%3E%0A%3CTD%3ERwanda%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20merge%20the%20data%20(Join)...%20This%20is%20just%20a%20sample%20but%20could%20give%20you%20the%20idea...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%2F%2F%20source%3A%20%3CA%20href%3D%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%3C%2FA%3E%20%0Aexternaldata(Network%3Astring%2C%20geoname_id%3Astring%2C%20continent_code%3Astring%2C%20continent_name%3Astring%2C%20country_iso_code%3Astring%2C%20country_name%3Astring)%0A%5B%40%22%3CA%20href%3D%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%3C%2FA%3E%22%5D%0A%7C%20extend%20%20%20trimIP%20%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2Ctostring(split(Network%2C%20%22%2F%22%2C0)))%0A%7C%20join%20kind%3D%20inner%20(%0A%20%20%20%20SigninLogs%0A%20%20%20%20%7C%20limit%2010%0A)%20on%20%24left.country_iso_code%20%3D%3D%20%24right.Location%0A%2F%2F%7C%20where%20trimIP%20%3D%3D%20IPAddress%0A%7C%20project%20Location%20%2C%20country_iso_code%2C%20IPAddress%20%2C%20trimIP%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EResults%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.azure.com%23%404b2462a4-bbee-495a-a0e1-f23ae524cc9c%2Fblade%2FMicrosoft_OperationsManagementSuite_Workspace%2FAnalyticsBlade%2Finitiator%2FAnalyticsShareLinkToQuery%2FisQueryEditorVisible%2Ftrue%2Fscope%2F%257B%2522resources%2522%253A%255B%257B%2522resourceId%2522%253A%2522%252Fsubscriptions%252F44e4eff8-1fcb-4a22-a7d6-992ac7286382%252FresourceGroups%252FSOC%252Fproviders%252FMicrosoft.OperationalInsights%252Fworkspaces%252FCyberSecurityDemo%2522%257D%255D%257D%2Fquery%2FH4sIAAAAAAAAA41QwWrCUBC8B%252FIPS%252FCQ0DTPlp6EgD0KUoQerZU02SarcTe8t2oLfnyThlSxPfT2dnZmduYZA072NscJVKqNmxhTZJpV%252B7eExORi0ZQo1NzfUnN4AN%252FDD0XLWd2xwifUo9jtxKklLmNoqZztcE3FD5QLKzGyrnMp8A%252B4E1zAe1b7uSYn1%252Fx%252BccGOfG85Df6R2tjLKcndIVj53gm6KlwAQGu3my0A0u9XOA2Wry%252FH1U0Qq%252FSnQtfUpEPdGAITxOMoijqXjRDDlrhIgZjRQuh7rSc8U8nEcyldP5%252Bgph0p3I19LwJhGNX4rsl1Y0hTGFkqK03mkmdKwr5nzAmOFVocorak2eKxKCw614VorGwwVxgk8Psr47Oi3fY%252BnfSc6gvhCDqNDAIAAA%253D%253D%2FisQueryBase64Compressed%2Ftrue%2FtimespanInIsoFormat%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ELocation%3C%2FTH%3E%0A%3CTH%3Ecountry_iso_code%3C%2FTH%3E%0A%3CTH%3EIPAddress%3C%2FTH%3E%0A%3CTH%3EtrimIP%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.224.0.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.212.225.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.199.192.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.199.64.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.198.240.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.198.140.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.198.128.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.197.80.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.195.32.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3EDE%3C%2FTD%3E%0A%3CTD%3E80.187.85.39%3C%2FTD%3E%0A%3CTD%3E217.195.0.0%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
philip-patrick
New Contributor

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 have in custom log is IP address, I would like to add a column (e.g. - extend) that resolves this IP address to its location in the world. Alternatively, if there was an option to call a rest service during query, I could call something like ipstack, and receive the required information.

 

An example of simple query:

MyEvents
| extend IPAddress = extractjson("$.request.ipaddress", Message)
| extend Country = extractgeo("$.country", IPAddress)

Hopefully that was clear enough :)

Thanks!

 

P.S. In PowerBI this can be achieved with 

Json.Document(Web.Contents("rest service url")....

 

7 Replies

Hi,

Unfortunately this is not possible as far as I know. May be the only workaround is to have some workflow that queries your data once every hour, finds the new IPs from your Log Analytics data, use those IPs to call external service to get the location, log back the location data in a separate table so it will be available for use when you use Log Analytics query. Of course the downside of this workaround is also that you will not be able to have the location data right away.

Hi,

 

it doesn't seem to be possible, but there might be a workaround. There are databases available for download that have the location of certain IP ranges. With a function that contains the database as a lookup table, it might be possible to compute the IP range on the fly when viewing the data.

 

If I ever complete it, I will update you.

did you manage to solve this ?
Did you solve this - I'm very interested in getting this functionality :)

@Morten Waltorp Knudsen I actually never got around to it... Sorry!

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.

@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