# Apim Kql > Guide for creating Kusto Query Language (KQL) queries for Azure API Management tables in Azure Monitor (Log Analytics Workspace). Use when users want to query, analyze, or monitor APIM logs including gateway logs, LLM/AI logs, MCP logs, WebSocket logs, and Application Insights data. This skill provides KQL syntax, table schemas, and query patterns for APIM monitoring scenarios. Use when this capability is needed. - Skill: `tomevault-io/apim-kql-2` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add tomevault-io/apim-kql-2` - Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/apim-kql-2/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: DevOps & Infra - Author: tomevault-io (https://skillmd.com/u/tomevault-io) - Updated: 2026-09-17 - Page: https://skillmd.com/skills/tomevault-io/apim-kql-2 --- # APIM KQL Skill Guide for creating Kusto Query Language (KQL) queries for Azure API Management monitoring data in Azure Monitor. ## Quick Start ### Basic Gateway Logs Query ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(1h) | project TimeGenerated, Method, Url, ResponseCode, TotalTime, BackendTime, CallerIpAddress | order by TimeGenerated desc | take 100 ``` ### LLM Token Usage Query ```kql ApiManagementGatewayLlmLog | where TimeGenerated > ago(24h) | summarize TotalPromptTokens = sum(PromptTokens), TotalCompletionTokens = sum(CompletionTokens), TotalTokens = sum(TotalTokens) by DeploymentName, ModelName ``` ## Tables Overview | Table | Description | Use Case | |-------|-------------|----------| | `ApiManagementGatewayLogs` | Gateway request/response logs | API traffic analysis, errors, latency | | `ApiManagementGatewayLlmLog` | LLM/AI model usage logs | Token usage, model performance | | `ApiManagementGatewayMCPLog` | MCP server logs | MCP tool calls, sessions | | `ApiManagementWebSocketConnectionLogs` | WebSocket connection events | Real-time API monitoring | | `AppRequests` | Application Insights requests | End-to-end request tracking | | `AppMetrics` | Application Insights metrics | Custom metrics analysis | | `AppTraces` | Application Insights traces | Debug and diagnostic traces | For complete table schemas, see [references/table-schemas.md](references/table-schemas.md). ## Essential Query Patterns ### Joining LLM Logs with Gateway Logs ```kql let llmLogs = ApiManagementGatewayLlmLog | where DeploymentName != ''; let logsWithSubscription = llmLogs | join kind=leftouter ApiManagementGatewayLogs on CorrelationId | project SubscriptionId = ApimSubscriptionId, DeploymentName, ModelName, PromptTokens, CompletionTokens, TotalTokens; logsWithSubscription | summarize SumPromptTokens = sum(PromptTokens), SumCompletionTokens = sum(CompletionTokens), SumTotalTokens = sum(TotalTokens) by SubscriptionId, DeploymentName ``` ### Error Analysis ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(24h) | where ResponseCode >= 400 | summarize ErrorCount = count() by ResponseCode, ApiId, LastErrorReason | order by ErrorCount desc ``` ### Request Latency Analysis ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(1h) | summarize AvgTotalTime = avg(TotalTime), AvgBackendTime = avg(BackendTime), P95TotalTime = percentile(TotalTime, 95), RequestCount = count() by bin(TimeGenerated, 5m), ApiId | order by TimeGenerated desc ``` ### Backend Performance ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(1h) | where BackendId != "" | summarize AvgBackendTime = avg(BackendTime), MaxBackendTime = max(BackendTime), ErrorCount = countif(BackendResponseCode >= 400), TotalRequests = count() by BackendId | order by AvgBackendTime desc ``` ### Token Usage by Time ```kql ApiManagementGatewayLlmLog | where TimeGenerated > ago(7d) | summarize TotalTokens = sum(TotalTokens), PromptTokens = sum(PromptTokens), CompletionTokens = sum(CompletionTokens) by bin(TimeGenerated, 1h), DeploymentName | order by TimeGenerated desc ``` ### MCP Tool Usage ```kql ApiManagementGatewayMCPLog | where TimeGenerated > ago(24h) | summarize CallCount = count(), UniqueClients = dcount(ClientName) by ToolName, ServerName, Method | order by CallCount desc ``` ### WebSocket Connection Events ```kql ApiManagementWebSocketConnectionLogs | where TimeGenerated > ago(1h) | summarize EventCount = count() by EventName, Source, Destination | order by EventCount desc ``` ## KQL Operators Reference ### Filtering ```kql | where TimeGenerated > ago(1h) | where ResponseCode == 200 | where ApiId contains "openai" | where isnotempty(BackendId) ``` ### Aggregation ```kql | summarize count() by ApiId | summarize avg(TotalTime), percentile(TotalTime, 95) by ApiId | summarize sum(TotalTokens) by DeploymentName | summarize dcount(CallerIpAddress) by bin(TimeGenerated, 1h) ``` ### Time Functions ```kql | where TimeGenerated > ago(1h) | where TimeGenerated between (datetime(2024-01-01) .. datetime(2024-01-31)) | where TimeGenerated >= startofmonth(now()) and TimeGenerated <= endofmonth(now()) | summarize ... by bin(TimeGenerated, 5m) ``` ### Joins ```kql | join kind=leftouter OtherTable on CorrelationId | join kind=inner OtherTable on $left.Field1 == $right.Field2 ``` ### Projections ```kql | project TimeGenerated, Method, Url, ResponseCode | project-away _BilledSize, _IsBillable | extend Duration = TotalTime - BackendTime | extend IsError = ResponseCode >= 400 ``` ### Ordering and Limiting ```kql | order by TimeGenerated desc | take 100 | top 10 by TotalTime desc ``` ## Common Scenarios ### Daily Request Summary ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(7d) | summarize TotalRequests = count(), SuccessfulRequests = countif(ResponseCode < 400), FailedRequests = countif(ResponseCode >= 400), AvgLatency = avg(TotalTime) by bin(TimeGenerated, 1d), ApiId | extend SuccessRate = round(100.0 * SuccessfulRequests / TotalRequests, 2) | order by TimeGenerated desc ``` ### Top Consumers by Token Usage ```kql let llmLogs = ApiManagementGatewayLlmLog | where TimeGenerated > ago(30d); llmLogs | join kind=leftouter ApiManagementGatewayLogs on CorrelationId | summarize TotalTokens = sum(TotalTokens), RequestCount = count() by ApimSubscriptionId | top 10 by TotalTokens desc ``` ### Error Rate Over Time ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(24h) | summarize Total = count(), Errors = countif(ResponseCode >= 400) by bin(TimeGenerated, 1h) | extend ErrorRate = round(100.0 * Errors / Total, 2) | project TimeGenerated, Total, Errors, ErrorRate | order by TimeGenerated desc ``` ### Cache Hit Analysis ```kql ApiManagementGatewayLogs | where TimeGenerated > ago(24h) | where Cache != "" | summarize HitCount = countif(Cache == "hit"), MissCount = countif(Cache == "miss") by ApiId | extend HitRate = round(100.0 * HitCount / (HitCount + MissCount), 2) ``` ### Streaming vs Non-Streaming LLM Requests ```kql ApiManagementGatewayLlmLog | where TimeGenerated > ago(24h) | summarize StreamingRequests = countif(IsStreamCompletion == true), NonStreamingRequests = countif(IsStreamCompletion == false), AvgTokens = avg(TotalTokens) by DeploymentName ``` ## Running KQL Queries ### Azure CLI ```bash az monitor log-analytics query \ -w \ --analytics-query "ApiManagementGatewayLogs | take 10" ``` ### Python ```python query = """ ApiManagementGatewayLogs | where TimeGenerated > ago(1h) | take 100 """ result = utils.run(f'az monitor log-analytics query -w {workspace_id} --analytics-query "{query}"') ``` ## Best Practices 1. **Always filter by time** - Use `where TimeGenerated > ago(...)` to limit data scanned 2. **Use summarize early** - Aggregate data before joins when possible 3. **Project only needed columns** - Reduce data transfer with explicit projections 4. **Use let statements** - Break complex queries into readable parts 5. **Join on CorrelationId** - Link APIM tables together using this common key 6. **Use bin() for time series** - Group time data into meaningful intervals ## References - [references/table-schemas.md](references/table-schemas.md) - Complete table column definitions - [references/query-examples.md](references/query-examples.md) - Additional query examples from this repository ## FinOps framework The [AI Gateway FinOps framework](https://github.com/Azure-Samples/AI-Gateway/blob/main/labs/finops-framework/main.bicep) deploys two custom Log Analytics tables to enable cost tracking and budget enforcement per APIM subscription. Data is ingested via Data Collection Rules (DCR). Use the `query` endpoint with API version `2020-08-01` to query the linked Log Analytics workspace. The results will be returned in PascalCase format. ### PRICING_CL Model pricing reference table. Stores per-model token prices that are periodically updated via DCR ingestion. | Column | Type | Description | |--------|------|-------------| | `TimeGenerated` | datetime | Record timestamp | | `Model` | string | Model/deployment name (matches `DeploymentName` in `ApiManagementGatewayLlmLog`) | | `InputTokensPrice` | real | Price per input (prompt) token | | `OutputTokensPrice` | real | Price per output (completion) token | ### SUBSCRIPTION_QUOTA_CL Per-subscription cost budget table. Stores the cost quota assigned to each APIM subscription. | Column | Type | Description | |--------|------|-------------| | `TimeGenerated` | datetime | Record timestamp | | `Subscription` | string | APIM subscription name (matches `ApimSubscriptionId` in `ApiManagementGatewayLogs`) | | `CostQuota` | real | Maximum allowed cost for the subscription (same unit as computed `TotalCost`) | ### Subscription budget vs actual cost Joins LLM logs with pricing data and subscription quotas to calculate actual cost per subscription for the current month and compare it against the budget. ```kql let llmHeaderLogs = ApiManagementGatewayLlmLog | where TimeGenerated >= startofmonth(now()) and TimeGenerated <= endofmonth(now()) | where DeploymentName != ""; let llmLogsWithSubscriptionId = llmHeaderLogs | join kind=leftouter ApiManagementGatewayLogs on CorrelationId | project SubscriptionName = ApimSubscriptionId, DeploymentName, PromptTokens, CompletionTokens, TotalTokens; llmLogsWithSubscriptionId | join kind=inner ( PRICING_CL | summarize arg_max(TimeGenerated, *) by Model | project Model, InputTokensPrice, OutputTokensPrice ) on $left.DeploymentName == $right.Model | extend InputCost = PromptTokens * InputTokensPrice | extend OutputCost = CompletionTokens * OutputTokensPrice | summarize InputCost = sum(InputCost), OutputCost = sum(OutputCost) by SubscriptionName | extend TotalCost = (InputCost + OutputCost) / 1000 | join kind=inner ( SUBSCRIPTION_QUOTA_CL | summarize arg_max(TimeGenerated, *) by Subscription | project Subscription, CostQuota ) on $left.SubscriptionName == $right.Subscription | project SubscriptionName, CostQuota, TotalCost ``` This same query pattern is used by the FinOps framework's scheduled alert rules — one to **suspend** subscriptions that exceed their quota (`TotalCost > CostQuota`) and another to **re-activate** subscriptions that are back within budget (`TotalCost <= CostQuota`). --- > Source: [Azure-Samples/ai-gateway-dev-portal](https://github.com/Azure-Samples/ai-gateway-dev-portal) — distributed by [TomeVault](https://tomevault.io).