Agent skill

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.

View SKILL.md on GitHub Repository
Stars 900
Forks 410

Install this agent skill to your Project

npx add-skill https://github.com/Azure-Samples/AI-Gateway/tree/main/.github/skills/apim-kql

SKILL.md

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.

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 <workspace-id> \
  --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 - Complete table column definitions
  • references/query-examples.md - Additional query examples from this repository

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

Azure-Samples/AI-Gateway

apim-terraform

Guide for creating Terraform files for Azure API Management (APIM) and related Azure services. Use when users want to create, modify, or understand Terraform configurations for APIM instances, APIs, backends, subscriptions, policies, products, loggers, diagnostics, and supporting infrastructure using the azurerm provider. This skill provides HCL syntax, resource definitions, and patterns from the Terraform Registry and this repository.

900 410
Explore
Azure-Samples/AI-Gateway

apim-policies

Guide for creating Azure API Management (APIM) XML policies. Use when users want to create, modify, or understand APIM policies including inbound/outbound processing, authentication, rate limiting, caching, transformations, AI gateway policies, and policy expressions. This skill provides policy syntax, examples, and C# policy expressions for request/response manipulation.

900 410
Explore
Azure-Samples/AI-Gateway

mcp-builder

Guide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).

900 410
Explore
Azure-Samples/AI-Gateway

apim-bicep

Guide for building Bicep files for Azure API Management (APIM) and related Azure services. Use when users want to create, modify, or understand Bicep templates for APIM instances, APIs, backends, subscriptions, policies, products, loggers, diagnostics, and MCP servers. This skill provides Bicep syntax, patterns from Azure Verified Modules, and examples from this repository.

900 410
Explore
Azure-Samples/AI-Gateway

lab-creator

Guide for creating new AI Gateway labs. Use when users want to create a new lab in the labs/ folder. This skill provides the standard lab structure, templates, and patterns used across the AI Gateway repository including Jupyter notebooks, Bicep infrastructure templates, APIM policies, and README documentation.

900 410
Explore
Azure-Samples/AI-Gateway

skill-creator

Guide for creating effective skills. This skill should be used when users want to create a new skill (or update an existing skill) that extends Claude's capabilities with specialized knowledge, workflows, or tool integrations.

900 410
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results