ip lookup

Application Insights makes automatic ip lookup for your telemetry. Geo information can be quite useful for monitoring, troubleshooting and usage scenarios.

I already wrote about IP address collection. Application Insights collects an IP address of the monitored service visitor. So you can group telemetry by the country of origin. This will allow to filter out long executing AJAX calls made from the countries with high latency or group usage metrics by “nighttime” visitors vs. “daytime” visitors.

First, a word of caution. Application Insights is using the snapshot of MaxMind geo IP database (Credits) from some time ago. So it may give wrong results at times and is not in sync with the demo.

For instance, this query demonstrates that not all availability tests locations geo mapped correctly by Application Insights.

  | where timestamp > ago(10m) 
  | join (requests 
    | where timestamp > ago(10m)) on $left.id == $right.session_Id
  | extend 
    originatingLocation = location, 
    recievedLocation = strcat(client_CountryOrRegion, " ", client_StateOrProvince, " ", client_City)
  | summarize count() 
    by originatingLocation, recievedLocation, client_IP 

This is a resultgin view. Note, some locations were not correctly mapped and some do not have city associated with it:

US : CA-San JoseUnited States California San Jose207.46.98.0
US : FL-MiamiUnited States Florida Miami65.54.78.0
US : TX-San AntonioUnited States Texas San Antonio65.55.82.0
NL : AmsterdamNetherlands North Holland Amsterdam213.199.178.0
US : IL-ChicagoUnited States Illinois Chicago207.46.14.0
IE : DublinIreland Leinster Dublin157.55.14.0
JP : KawaguchiJapan Tokyo Tokyo202.89.228.0
RU : MoscowUnited Kingdom94.245.82.0
CH : ZurichUnited Kingdom94.245.66.0
HK : Hong KongHong Kong Long Keng207.46.71.0
AU : SydneyUnited States Washington Redmond70.37.147.0
BR : Sao PauloBrazil Sao Paulo São Paulo65.54.66.0
SE : StockholmUnited Kingdom94.245.78.0
SG : SingaporeUnited States Delaware Wilmington52.187.30.0
US : VA-AshburnUnited States13.106.106.0
FR : ParisUnited Kingdom94.245.72.0

Try this query yourself for an up to date information.

I authored a simple query to check whether my blog is read during the day or night. This demo is not produciton ready and I might have messed with the timezones. However for an adhoc analysis it was OK. It also demonstrates the use of the operators datatable and the power of join:

let timezones = datatable (timezone_location:string, shift:time)
        "United States", time(-6h),
        "Canada", time(-6h),
        "Japan", time(9h),
        "Brazil", time(-3h),
        "United Kingdom", time(0),
        "Hong Kong", time(8h),
        "Ireland", time(0),
        "Switzerland", time(2h),
        "Slovenia", time(1h),
        "South Africa", time(2h),
        "Sweden", time(1h),
        "Poland", time(1h),
        "Ukraine", time(2h),
        "Netherlands", time(2h),
 | extend timezone_location = client_CountryOrRegion
 | where timestamp > ago(10h) and timestamp < ago(5h)
 | join kind= leftouter (
 ) on timezone_location
 | extend localtimehour = datepart("Hour", timestamp + shift)
 | project name, timezone_location, timestamp, localtimehour, isDay = iff(localtimehour > 5 and localtimehour < 20, "day", "night")
 | summarize count() by isDay
 | render piechart

Here is the result view:

day vs night


comments powered by Disqus