KQL question

%3CLINGO-SUB%20id%3D%22lingo-sub-1479969%22%20slang%3D%22en-US%22%3EKQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EAzureActivity%20%7C%20summarize%20LastActivity%20%3D%20max(TimeGenerated)%20by%20ResourceProvider%2C%20ResourceGroup%20%7C%20join%20kind%20%3D%20innerunique(%20AzureActivity%20%7C%20summarize%20Operations%20%3D%20count()%20by%20ResourceGroup%2C%20ResourceProvider)%20on%20ResourceGroup%2C%20ResourceProvider%20%7Cproject%20ResourceProvider%2C%20ResourceGroup%2C%20Operations%2C%20LastActivity%20%7Csort%20by%20Operations%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20above%20KQL%20is%20used%20to%20print%204%20columns%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20need%20to%20print%20the%20fifth%20column%20as%20well%20that%20highlights%20the%20percentage%20of%20operations%20per%20Resource%20Group%20and%20Resource%20provider.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThere%20have%20to%205%20columns%20in%20the%20result%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EResource%20Provider%2C%20Resource%20Group%2CNumber%20of%20Operations%20(Activities)%2C%20Last%20activity%20time%2C%20Percentage%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECan%20someone%20help%20me%20with%20this%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1479969%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1480808%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1480808%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F693835%22%20target%3D%22_blank%22%3E%40uditk14%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ethere%20may%20be%20a%20better%20solution%2C%20but%20this%20approach%20should%20work%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Elet%20TotalOperations%20%3D%20todouble(toscalar(AzureActivity%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()))%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CSPAN%3EAzureActivity%20%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20LastActivity%20%3D%20max(TimeGenerated)%2C%20Operations%20%3D%20%3C%2FSPAN%3E%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ResourceProvider%2C%20ResourceGroup%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Percentage%20%3D%20round(todouble(Operations)%20%2F%20TotalOperations%20*%20%3C%2FSPAN%3E%3CSPAN%3E100%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ResourceProvider%2C%20ResourceGroup%2C%20Operations%2C%20Percentage%2C%20LastActivity%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Operations%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1488245%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3B-%20Thanks%20a%20lot%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20one%20more%20query%20with%20the%20exteraldata%20operator%3C%2FP%3E%3CP%3EI%20have%20used%20externaldata%20operator%20to%20fetch%20data%20from%20a%20CSV%20having%20a%20few%20columns%20namely%2C%20IP%20ranges%2C%20country%20code%2C%20country%20name%2C%20continent%20name%20etc.%3C%2FP%3E%3CP%3EIn%20Azure%20Activity%20table%20there%20is%20a%20CallerIP%20value.%3C%2FP%3E%3CP%3EI%20need%20to%20print%20the%20location%20for%20each%20caller%20Ip.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECSV%20file%20-%26nbsp%3B-ERR%3AREF-NOT-FOUND-%3CA%20href%3D%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%23premium-data-2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%23premium-data-2%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3BCan%20you%20help%20me%20with%20the%20KQL%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1490940%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1490940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F693835%22%20target%3D%22_blank%22%3E%40uditk14%3C%2FA%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Esomething%20like%20this%20would%20respond%20to%20your%20needs.%20However%2C%20due%20to%20a%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Ffunctions%2Fuser-defined-functions%23restrictions%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Erestriction%20of%20user-defined%20functions%3C%2FA%3E%2C%20you%20cannot%20call%20functions%20sending%20parameters%20that%20depend%20on%20row-context.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-java%22%3E%3CCODE%3Elet%20GeoData%20%3D%20externaldata%20(network%3Astring%2Cgeoname_id%3Astring%2Ccontinent_code%3Astring%2Ccontinent_name%3Astring%2Ccountry_iso_code%3Astring%2Ccountry_name%3Astring%2Cis_anonymous_proxy%3Abool%2Cis_satellite_provider%3Abool)%20%5B%0A%40%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%22%0A%5D%20with(format%3D%22csv%22%2C%20ignoreFirstRecord%3Dtrue)%3B%0A%0Alet%20GetCountryName%20%3D%20(CallerIp%3Astring)%20%7B%20toscalar(%0A%20%20%20%20GeoData%0A%20%20%20%20%7C%20extend%20AddressMask%20%3D%20split(network%2C'%2F')%5B1%5D%0A%20%20%20%20%7C%20where%20ipv4_compare(CallerIp%2C%20tostring(split(network%2C'%2F')%5B0%5D)%2C%20toint(tostring(split(network%2C'%2F')%5B1%5D)))%20%3D%3D%200%0A%20%20%20%20%7C%20project%20country_name%20)%0A%7D%3B%0A%0A%2F%2Fthis%20works%2C%20because%20the%20parameter%20is%20hardcoded%0A%2F%2Fprint%20GetCountryName('94.45.78.16')%0A%0A%2F%2F%20this%20will%20fail%20with%20a%20%22Unresolved%20reference%20binding%22%20error%0AAzureActivity%0A%7C%20extend%20CountryName%20%3D%20GetCountryName(CallerIpAddress)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-ERR%3AREF-NOT-FOUND-%40CLIVE_Watson%2C%20do%20you%20have%20a%20solution%20for%20this%20one%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498004%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EWould%20this%20work%2C%20it%20maps%20your%20IP%20from%20AzureActivity%20to%20the%20data%20from%20the%20CSV%20file%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Eexternaldata%20(network%3Astring%2Cgeoname_id%3Astring%2Ccontinent_code%3Astring%2Ccontinent_name%3Astring%2Ccountry_iso_code%3Astring%2Ccountry_name%3Astring%2Cis_anonymous_proxy%3Abool%2Cis_satellite_provider%3Abool)%0A%5B%40%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%22%5D%20with(format%3D%22csv%22%2C%20ignoreFirstRecord%3Dtrue)%0A%2F%2F%20select%20only%20the%20IP%20addr%0A%7C%20project%20geoNetworkip%20%3D%20tostring(split(network%2C%22%2F%22).%5B0%5D)%2C%20continent_name%2C%20continent_code%0A%2F%2F%20join%20to%20AzureActicity%20Table%0A%7C%20join%20kind%3D%20inner%20%0A%20%20%20%20(%0A%20%20%20%20AzureActivity%0A%2F%2F%20%20%20%20%7C%20project%20CallerIpAddress%20%3D%20%2241.186.0.0%22%20%20%2F%2F%20add%20a%20fake%20match%20to%20test%20%0A%20%20%20%20%20%20%7C%20project%20CallerIpAddress%0A%20%20%20%20)%20on%20%24left.geoNetworkip%20%3D%3D%20%24right.CallerIpAddress%0A%7C%20project%20geoNetworkip%2C%20CallerIpAddress%2C%20continent_name%2C%20continent_code%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA4VQwUrDQBC9F%25252FoPQ%25252FDQQkyqiEghYBGEXkTEm0jYJtNm7HY37E5aI368s421tYrOZdk3M%25252B%25252B9efjK6IzSpWIFA4O8sW459uzILOIFWqNWmFO5QwprmAwazgtb4k80jO%25252FRxrBrc%25252FL2aLrDD2fJ58pY065s4%25252FPa2dd2PLNWB9wrRq2JMeBrKtFtW8N%25252B7%25252Bk6qphrP07TYL9qZgnZtLAOU3FO9fkp1euL1B3%25252BksKvo2fYEFeDuXUrxVkUoBhoYWTzlpznBxSSMmPXoMikKXjUWDBYo1vgCmF6D6osXb%25252F3DmLqJfRE465Lj2rIgG132cDX4n0XbByl0TB5Gj0PY%25252Fge2uE%25252FhLWVfbFkhAkmb43DScFUELfwqGYag%25252FS2vSRTZkDGoIN%25252BD6QG3fO1tJalLZ3U3u%25252BN0hrdtJ7IHei9WI4uzpKzq8tklIwiAJmXE0HBXC0RJKeiClYYPX%25252Fq%25252FMHWDQwlMDjROOfkezoZnDhaVJz8WPs9z%25252FiY%25252F7%25252F0PgA8iaqG1gIAAA%25253D%25253D%2Ftimespan%2FP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20run%20query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EAdapted%20from%20an%20old%20post%20of%20mine%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1498183%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1498183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20solution%20but%20this%20won't%20help%20in%20conditions%20with%20IPs%20falling%20in%20between%20the%20range.%20For%20instance%2C%2047.7.8.8.%3C%2FP%3E%3CP%3EThe%20csv%20contains%20the%20ranges%20and%20not%20direct%20IPs%20that%20can%20be%20mapped.%3C%2FP%3E%3CP%3EHow%20to%20go%20about%20in%20finding%20the%20location%20for%20all%20the%20Ip%20addresses.%20Most%20of%20which%20fall%20in%20the%20mid%20of%20the%20ranges%20provided.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1499421%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1499421%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F693835%22%20target%3D%22_blank%22%3E%40uditk14%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20will%20stress%20this%20is%20just%20a%20sample%2C%20its%20not%20very%20optimized%20and%20there%20is%20probably%20a%20better%20way%20to%20do%20this%20(I%20just%20cant%20think%20of%20one%20currently%20-%20so%20I%20need%20to%20take%20a%20break%20from%20it%2C%20to%20help%20me%20think!)%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%20source%20idea%3A%20https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sentinel%2Fapproximate-partial-and-combined-lookups-in-azure-sentinel%2Fba-p%2F1393795%20%0A%2F%2F%20get%20lookup%20data%20%0Alet%20geoData%20%3D%20%0A%20%20%20%20externaldata%20(network%3Astring%2Cgeoname_id%3Astring%2Ccontinent_code%3Astring%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20continent_name%3Astring%2Ccountry_iso_code%3Astring%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20country_name%3Astring%2Cis_anonymous_proxy%3Abool%2Cis_satellite_provider%3Abool)%0A%20%20%20%20%5B%40%22https%3A%2F%2Fdatahub.io%2Fcore%2Fgeoip2-ipv4%2Fr%2Fgeoip2-ipv4.csv%22%5D%20with(format%3D%22csv%22%2C%20ignoreFirstRecord%3Dtrue)%3B%0A%2F%2F%20now%20turn%20remote%20data%20to%20scalar%20%0Alet%20lookup%20%3D%20toscalar(%20geoData%20%7C%20%20summarize%20list_CIDR%3Dmake_set(network)%20)%3B%0A%2F%2F%20link%20to%20Azure%20Activity%20and%20specifically%20CallerIpAddress%20%20%0AAzureActivity%0A%2F%2F%20get%20a%20small%20time%20range%20(this%20REALLY%20helps%20perf!!!!)%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(2h)%0A%7C%20mv-apply%20list_CIDR%3Dlookup%20to%20typeof(string)%20on%0A(%0A%20%20%20%20%2F%2F%20Match%20each%20IP%20from%20'CallerIpAddress'%20with%20the%20remote%20'network'%20column%20%0A%20%20%20%20where%20ipv4_is_match%20(CallerIpAddress%2C%20list_CIDR)%20%2F%2F%3D%3D%20false%0A)%0A%2F%2F%20summarize%20to%20remove%20any%20duplicates%0A%7C%20summarize%20by%20CallerIpAddress%2C%20list_CIDR%0A%7C%20join%20kind%3Dinner%20%0A%20%20(%0A%20%20%2F%2F%20join%20to%20remote%20data%20again%2C%20to%20add%20enrichments%20%0A%20%20geoData%0A%20%20)%20on%20%24left.list_CIDR%20%3D%3D%20%24right.network%0A%2F%2F%20build%20final%20display%20%0A%7C%20summarize%20by%20CallerIpAddress%2C%20network%2C%20country_name%2C%20country_iso_code%20%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

AzureActivity | summarize LastActivity = max(TimeGenerated) by ResourceProvider, ResourceGroup | join kind = innerunique( AzureActivity | summarize Operations = count() by ResourceGroup, ResourceProvider) on ResourceGroup, ResourceProvider |project ResourceProvider, ResourceGroup, Operations, LastActivity |sort by Operations

 

The above KQL is used to print 4 columns

I need to print the fifth column as well that highlights the percentage of operations per Resource Group and Resource provider. 

There have to 5 columns in the result

Resource Provider, Resource Group,Number of Operations (Activities), Last activity time, Percentage

 

Can someone help me with this?

 

 

6 Replies
Highlighted

@uditk14 

 

there may be a better solution, but this approach should work:

 

let TotalOperations = todouble(toscalar(AzureActivity | summarize count()));

AzureActivity
| summarize LastActivity = max(TimeGenerated), Operations = count() by ResourceProvider, ResourceGroup
| extend Percentage = round(todouble(Operations) / TotalOperations * 100, 1)
| project ResourceProvider, ResourceGroup, Operations, Percentage, LastActivity
| sort by Operations
Highlighted

@hspinto - Thanks a lot

 

I have one more query with the exteraldata operator

I have used externaldata operator to fetch data from a CSV having a few columns namely, IP ranges, country code, country name, continent name etc.

In Azure Activity table there is a CallerIP value.

I need to print the location for each caller Ip.

 

CSV file - https://datahub.io/core/geoip2-ipv4#premium-data-2

 

@hspinto Can you help me with the KQL

Highlighted

@uditk14,

 

something like this would respond to your needs. However, due to a restriction of user-defined functions, you cannot call functions sending parameters that depend on row-context.

 

 
let GeoData = externaldata (network:string,geoname_id:string,continent_code:string,continent_name:string,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool) [
@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"
] with(format="csv", ignoreFirstRecord=true);

let GetCountryName = (CallerIp:string) { toscalar(
    GeoData
    | extend AddressMask = split(network,'/')[1]
    | where ipv4_compare(CallerIp, tostring(split(network,'/')[0]), toint(tostring(split(network,'/')[1]))) == 0
    | project country_name )
};

//this works, because the parameter is hardcoded
//print GetCountryName('94.45.78.16')

// this will fail with a "Unresolved reference binding" error
AzureActivity
| extend CountryName = GetCountryName(CallerIpAddress)

 

@CLIVE_Watson, do you have a solution for this one? 

Highlighted

@hspinto 


Would this work, it maps your IP from AzureActivity to the data from the CSV file?

externaldata (network:string,geoname_id:string,continent_code:string,continent_name:string,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
[@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"] with(format="csv", ignoreFirstRecord=true)
// select only the IP addr
| project geoNetworkip = tostring(split(network,"/").[0]), continent_name, continent_code
// join to AzureActicity Table
| join kind= inner 
    (
    AzureActivity
//    | project CallerIpAddress = "41.186.0.0"  // add a fake match to test 
      | project CallerIpAddress
    ) on $left.geoNetworkip == $right.CallerIpAddress
| project geoNetworkip, CallerIpAddress, continent_name, continent_code

 Go to Log Analytics and run query

Adapted from an old post of mine: https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2019/08/13/azure-log-analytics-h...

Highlighted

@Clive Watson 

Thanks for the solution but this won't help in conditions with IPs falling in between the range. For instance, 47.7.8.8.

The csv contains the ranges and not direct IPs that can be mapped.

How to go about in finding the location for all the Ip addresses. Most of which fall in the mid of the ranges provided.

Highlighted

Hello @uditk14

I will stress this is just a sample, its not very optimized and there is probably a better way to do this (I just cant think of one currently - so I need to take a break from it, to help me think!)

// source idea: https://techcommunity.microsoft.com/t5/azure-sentinel/approximate-partial-and-combined-lookups-in-azure-sentinel/ba-p/1393795 
// get lookup data 
let geoData = 
    externaldata (network:string,geoname_id:string,continent_code:string,
                  continent_name:string,country_iso_code:string,
                  country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
    [@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"] with(format="csv", ignoreFirstRecord=true);
// now turn remote data to scalar 
let lookup = toscalar( geoData |  summarize list_CIDR=make_set(network) );
// link to Azure Activity and specifically CallerIpAddress  
AzureActivity
// get a small time range (this REALLY helps perf!!!!)
| where TimeGenerated > ago(2h)
| mv-apply list_CIDR=lookup to typeof(string) on
(
    // Match each IP from 'CallerIpAddress' with the remote 'network' column 
    where ipv4_is_match (CallerIpAddress, list_CIDR) //== false
)
// summarize to remove any duplicates
| summarize by CallerIpAddress, list_CIDR
| join kind=inner 
  (
  // join to remote data again, to add enrichments 
  geoData
  ) on $left.list_CIDR == $right.network
// build final display 
| summarize by CallerIpAddress, network, country_name, country_iso_code