KQL (Kusto Query Language)

From Ilianko

← Back to DP-420 Terminology Reference

Category: Maintenance & Security

Summary: Query language used in Azure Monitor Log Analytics to analyze Cosmos DB diagnostic logs and metrics.

Overview

KQL (Kusto Query Language) is the read-only query language used to explore, analyze, and aggregate telemetry and diagnostic data in Azure Monitor Log Analytics, Azure Data Explorer, and Microsoft Fabric Real-Time Intelligence. In the context of Azure Cosmos DB and the DP-420 exam, KQL is used to query diagnostic log tables — such as CDBDataPlaneRequests, CDBQueryRuntimeStatistics, CDBPartitionKeyRUConsumption, and CDBControlPlaneRequests (or legacy AzureDiagnostics) — to analyze RU consumption patterns, troubleshoot HTTP 429 throttling errors, identify hot partitions, and pinpoint high-cost or slow queries. KQL queries are built using a pipeline model where data flows from left to right through tabular operators separated by the pipe (|) character, such as where, project, summarize, join, order by, and render.

Example 1 (KQL): Find operations throttled with HTTP 429 status code 

CDBDataPlaneRequests
| where TimeGenerated > ago(24h)
| where StatusCode == 429
| summarize Count429 = count() by bin(TimeGenerated, 5m), OperationName, PartitionKey
| order by Count429 desc

This query identifies requests that were rate-limited (HTTP 429) across 5-minute intervals in the last 24 hours, grouping by operation and partition key to isolate hot partitions causing throttling.

Example 2 (KQL): Correlate high RU queries with query text 

CDBDataPlaneRequests
| where TimeGenerated > ago(1h) and todouble(RequestCharge) > 50.0
| project TimeGenerated, ActivityId, RequestCharge, DurationMs
| join kind=inner (
    CDBQueryRuntimeStatistics
    | project ActivityId, QueryText
) on $left.ActivityId == $right.ActivityId
| order by RequestCharge desc
| limit 20

This query correlates request execution telemetry from CDBDataPlaneRequests with query text from CDBQueryRuntimeStatistics via ActivityId to find the top 20 most expensive queries.

More Info & Links

DP-420 Style Practice Questions

Q1. What is the primary role of KQL (Kusto Query Language) in Azure Cosmos DB operations and troubleshooting?

  • A. Querying and analyzing diagnostic logs and metrics in Azure Monitor Log Analytics to troubleshoot issues like throttling, hot partitions, and expensive queries
  • B. Defining server-side stored procedures and triggers directly in Cosmos DB containers
  • C. Querying JSON documents via the Cosmos DB API for NoSQL endpoint
  • D. Creating and managing Cosmos DB indexing policies declaratively

Q2. In a KQL query for Azure Cosmos DB logs, what does the pipe (|) operator represent?

  • A. It pipes the tabular output of the preceding operator as the input to the next operator in a data-flow pipeline
  • B. It performs a logical bitwise OR between two numeric fields
  • C. It defines a primary key constraint on the diagnostic table
  • D. It separates alternative partition key values in a point read

Q3. Which resource-specific table would you query using KQL to inspect RU charges and HTTP status codes for individual data operations?

  • A. CDBDataPlaneRequests
  • B. CDBControlPlaneRequests
  • C. CDBPartitionKeyStatistics
  • D. AzureMetrics

Answer Key

Answers are listed at the very end so you can attempt the questions first.

  • Q1 (KQL (Kusto Query Language)): A. Querying and analyzing diagnostic logs and metrics in Azure Monitor Log Analytics to troubleshoot issues like throttling, hot partitions, and expensive queries — KQL is the read-only query language for Azure Monitor Log Analytics used to analyze diagnostic logs like CDBDataPlaneRequests and CDBPartitionKeyRUConsumption.
  • Q2 (KQL (Kusto Query Language)): A. It pipes the tabular output of the preceding operator as the input to the next operator in a data-flow pipeline — KQL uses the pipe (|) operator to pass data sequentially from one tabular expression/operator to another (e.g., filtering with where, then aggregating with summarize).
  • Q3 (KQL (Kusto Query Language)): A. CDBDataPlaneRequests — CDBDataPlaneRequests logs individual data-plane operations along with RequestCharge, StatusCode, DurationMs, and ActivityId.

DP-420: Designing and Implementing Cloud-Native Applications Using Microsoft Azure Cosmos DB — term detail page

Retrieved from "https://ilianko.com/index.php?title=KQL_(Kusto_Query_Language)&oldid=6235"