KQL Search

Query Details

APIM AI Gateway - Request volume against seven-day baseline

APIM Request Volume Baseline

Query

let recentWindow=1h;
let baselineWindow=7d;
let Historical=AppRequests
| where TimeGenerated between (ago(baselineWindow) .. ago(recentWindow))
| where SDKVersion startswith "apim:" or tostring(Properties["Service Type"]) =~ "API Management"
| extend Caller=coalesce(UserAuthenticatedId, UserId, tostring(Properties["Subscription ID"]), tostring(Properties["SubscriptionId"]), tostring(Properties["subscriptionId"]), ClientIP, "unknown"), APIName=coalesce(tostring(Properties["API Name"]), Name)
| extend AgentKey=coalesce(tostring(Properties["AgentId"]), tostring(Properties["Agent ID"]), tostring(Properties["ApplicationId"]), tostring(Properties["Application ID"]), AppRoleName, Caller)
| summarize HourRequests=sum(ItemCount) by AgentKey, APIName, bin(TimeGenerated, 1h)
| summarize BaselineMedian=percentile(HourRequests, 50), BaselineP95=percentile(HourRequests, 95), BaselineHours=count() by AgentKey, APIName;
AppRequests
| where TimeGenerated > ago(recentWindow)
| where SDKVersion startswith "apim:" or tostring(Properties["Service Type"]) =~ "API Management"
| extend Caller=coalesce(UserAuthenticatedId, UserId, tostring(Properties["Subscription ID"]), tostring(Properties["SubscriptionId"]), tostring(Properties["subscriptionId"]), ClientIP, "unknown"), APIName=coalesce(tostring(Properties["API Name"]), Name)
| extend AgentKey=coalesce(tostring(Properties["AgentId"]), tostring(Properties["Agent ID"]), tostring(Properties["ApplicationId"]), tostring(Properties["Application ID"]), AppRoleName, Caller)
| summarize CurrentRequests=sum(ItemCount), Operations=dcount(Name), FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated), Caller=take_any(Caller), ClientIP=take_any(ClientIP) by AgentKey, APIName
| join kind=leftouter Historical on AgentKey, APIName
| extend BaselineMedian=todouble(coalesce(BaselineMedian, 0)), BaselineP95=todouble(coalesce(BaselineP95, 0)), BaselineHours=tolong(coalesce(BaselineHours, 0))
| extend MedianRatio=round(todouble(CurrentRequests) / max_of(todouble(BaselineMedian), 1.0), 2), P95Ratio=round(todouble(CurrentRequests) / max_of(todouble(BaselineP95), 1.0), 2)
| extend Classification=case(BaselineHours < 6, "INSUFFICIENT BASELINE", CurrentRequests >= 50 and MedianRatio >= 3.0 and P95Ratio >= 1.5, "ANOMALOUS", MedianRatio >= 2.0, "ELEVATED", "EXPECTED RANGE")
| project LastSeen, Classification, AgentKey, Caller, ClientIP, APIName, CurrentRequests, BaselineMedian, BaselineP95, MedianRatio, P95Ratio, BaselineHours, Operations, FirstSeen
| order by MedianRatio desc

Explanation

This query is designed to analyze and compare the volume of API requests over the last hour with a baseline established from the past seven days. Here's a simple breakdown of what it does:

  1. Define Time Windows: It sets two time periods: the recent window (last hour) and the baseline window (last seven days).

  2. Gather Historical Data: It collects data from the past seven days, focusing on API requests that are related to API Management. It identifies requests by various identifiers like user ID, subscription ID, or client IP.

  3. Calculate Baseline Metrics: For each unique combination of agent and API, it calculates the median and 95th percentile of hourly request counts over the seven-day period. It also counts the number of hours with data in this period.

  4. Analyze Recent Data: It looks at the API requests from the last hour, summarizing the total requests, number of operations, and the time range of these requests.

  5. Compare and Classify: It joins the recent data with the historical baseline data. It calculates ratios comparing current request counts to the baseline median and 95th percentile. Based on these ratios and the amount of baseline data, it classifies the request volume as "ANOMALOUS," "ELEVATED," "EXPECTED RANGE," or "INSUFFICIENT BASELINE."

  6. Output: The results are presented with details like the last time a request was seen, classification, agent, caller, client IP, API name, request counts, baseline metrics, and operation counts. The results are ordered by how much the current request volume exceeds the baseline median.

This query helps identify unusual spikes or drops in API request volumes, which can be useful for detecting issues or planning capacity.