KQL question

Copper Contributor

Jun 25, 2020

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

Microsoft

Jun 26, 2020

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)

Deleted, do you have a solution for this one?

CliveWatson

Silver Contributor

Jun 30, 2020

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-how-to-read-a-file/

Forum Discussion

Resources