Forum Discussion
KQL question
Microsoft
there may be a better solution, but this approach should work:
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
- hspinto
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)Deleted, do you have a solution for this one?
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_codeGo 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-how-to-read-a-file/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.
