View source for KQL (Kusto Query Language)

&larr; Back to [[DP-420: Azure Cosmos DB Developer Specialty|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

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

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

<pre>
CDBDataPlaneRequests
| where TimeGenerated &gt; ago(1h) and todouble(RequestCharge) &gt; 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
</pre>

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

== More Info & Links ==
* [https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/log-analytics/tutorial-kql Microsoft Learn: Tutorial - Write KQL queries to monitor Azure Cosmos DB]
* [https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ Microsoft Learn: Overview of Kusto Query Language (KQL)]
* [https://learn.microsoft.com/en-us/azure/cosmos-db/monitor-resource-logs Microsoft Learn: Monitor and troubleshoot Azure Cosmos DB using diagnostic logs]

== 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''

[[Category:DP-420 Terminology]]
[[Category:Maintenance & Security]]