Search code examples
mapsdata-visualizationazure-application-insightskqlazure-monitor-workbooks

AzMonitor workbook to visualize data by city

One is able to group the number of page views by country, per this documentation. PFB KQL query.

    pageViews | project client_CountryOrRegion, itemCount, client_City

However, visualizing by client_City doesn't work.

enter image description here

Is there a way one could group and visualize by the name of the city instead?

Solution

  • Workbooks itself doesn't currently have any built in mapping of city to lat/long, we only have that at the country/region level. (In the screenshots above, you told workbooks that a column of data has country information, but then you passed it cities, so it doesn't know of any countries named those things)

    there are various ways to do it by using the externaldata operator in ADX/Log Analytics to have the database load, parse, and join with your other data. If you can get to lat/long, then you can tell workbooks to use that mode where you tell it which columns are lat and long and you'd have your points.

    not the exact files you want, but in another example someone wanted to map from ip address to country, and in that example you'd add something like this to your query:

    let geoData = externaldata
(network:string,geoname_id:string,continent_code:string,continent_name:string,
country_iso_code:string,country_name:string,is_anonymous_proxy:string,is_satellite_provider:string)
[@"https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv"] with (ignoreFirstRecord=true, format="csv");
geoData
| limit 10

    in your real query you'd not have the limit, you'd use the kql join operator to do an intersection and you'd get your lat/long that way.