KQL · usage
Requests by client country, with share and p95
Each country or region's share of server requests over the last 7 days, with p95 duration, sampling-adjusted, and what the geolocation can and can't tell you.
requests usage
let lookback = 7d;
let total = toscalar(
requests
| where timestamp > ago(lookback)
| where isempty(operation_SyntheticSource)
| summarize sum(itemCount));
requests
| where timestamp > ago(lookback)
| where isempty(operation_SyntheticSource)
| summarize
requests = sum(itemCount),
p95_ms = percentilew(duration, itemCount, 95)
by client_CountryOrRegion
| extend share_pct = round(100.0 * requests / total, 2)
| order by requests descReturns: One row per client country or region with its sampling-adjusted request count, its share of all non-synthetic requests and its p95 server duration in milliseconds.
Pitfalls
- Server-side country is only as good as the IP the server sees. Behind a proxy or load balancer that doesn't pass X-Forwarded-For, every request comes from the proxy's location.
- When the forwarded header holds several addresses, the SDK uses the last one, which is not always the original client.
- p95 here is server time. Users far away wait longer than this, because network time isn't in duration.
- IP addresses are stored as 0.0.0.0 by default, so you can group by location but not by address.
How it works
Where do requests come from, and is the service slower for some of them? This query answers both, with each country’s share of traffic and its p95, as long as you read it knowing how the location gets there.
Line by line
let total = toscalar(...)counts all non-synthetic requests in the window once and turns the one-cell result into a number the main query can divide by.where isempty(operation_SyntheticSource)drops traffic the SDK marked as synthetic. The data model names web crawlers and availability tests as examples of synthetic sources; availability tests in particular run from Azure regions and would otherwise inflate a handful of countries.requests = sum(itemCount)counts requests the sampling-aware way, andpercentilew(duration, itemCount, 95)gives the weighted p95 in milliseconds.share_pctis each country’s percentage of the total.
How the location is set
Microsoft documents the mechanism: when telemetry arrives, the client IP is looked up to fill client_City, client_StateOrProvince and client_CountryOrRegion, and then, by default, the address is discarded and 0.0.0.0 is stored in client_IP. For server telemetry the SDK reads the X-Forwarded-For header, and when it holds more than one address the last one is used. OpenTelemetry apps provide the address through the client.address attribute.
So the column is accurate when your front door forwards the caller’s address and wrong in a predictable way when it doesn’t. If one country, often the one your data centre is in, holds nearly all traffic, check the forwarding before believing it. The client IP and geolocation guide walks through the settings for each SDK and proxy setup.
Reading the result
A country with a much higher p95 than the rest, on the same routes, often points at a dependency close to those users being slow, or at one region’s deployment. Compare with what users see in browsers through page views by name and country.
Variations
- By city within a country: add
| where client_CountryOrRegion == "Germany"and group byclient_City. - Workspace tables: use
AppRequests,TimeGenerated,ClientCountryOrRegion,SyntheticSource,DurationMsandItemCount.