Kusto Query Language (KQL)

• Purpose: KQL is designed to explore and analyze structured, semi-structured, and unstructured data, making it ideal for querying telemetry, metrics, and logs overview
• Syntax: KQL uses a data-flow model where data is piped through various operators, each transforming the data step-by-step overview. The syntax is similar to SQL but optimized for performance and ease of use.
• Key Features:
  • Aggregation: Perform complex aggregations using functions like summarize, count, avg, min, and max.
  • Filtering: Use operators like where to filter data based on specific conditions.
  • Time-Series Analysis: Leverage time-series operators and functions to analyze data over time.
  • Text Search and Parsing: Deep support for text search and parsing to extract meaningful insights from text data.
  • Visualization: Integrate with visualization tools to create charts and graphs for better data interpretation.
• Use Cases: Ideal for scenarios like monitoring application performance, security threat detection, and operational analytics.

Kusto is a reference to Jacques Cousteau (a famous underwater explorer) and was the original codename for Azure Application Insights.

Understanding KQL syntax

SQL Example:

SELECT * FROM Ingredients
WHERE conference = 'Potato'

KQL Example:

Ingredients
| where ingredient == 'Potato'

In KQL, the pipe | character is used to chain together multiple operations step by step. For example, you can structure the query with multiple pipes to filter, sort, and project data in one go:

Ingredients
| where ingredient == 'Potato'
| order by type

Operators

Here’s a quick reference to some common KQL operators:

Count

To count the number of records:

Ingredients | count

Project

In KQL, project is the same as select in SQL. It allows you to choose specific columns of data:

Ingredients | project ingredient, name

Sort

To sort all ingredients:

Ingredients | sort by ingredient

To take the first 10 sorted ingredients:

Ingredients | take 10

Example

This uses Azure Logs and KQL to query performance vales.

Average disk IOPS

Calculates the average and maximum IOPS (Input/Output Operations Per Second) for logical disks over the past 30 days, grouped by instance name.

Perf
| where TimeGenerated > ago(30d)
| where ObjectName == "LogicalDisk"
| where CounterName == "Disk Transfers/sec"
| summarize IopsAvg = avg(CounterValue), IopsMax = max(CounterValue) by InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":"

  Average disk throughput in MBps

Calculates the average and maximum disk throughput in megabytes per second (MBps) for logical disks over the past 30 days, grouped by instance name.

Perf
| where TimeGenerated > ago(30d)
| where ObjectName == "LogicalDisk"
| where CounterName == "Disk Bytes/sec"
| summarize ThroughputInMBsAvg = (avg(CounterValue)/1024/1024), ThroughputInMBsMax = (max(CounterValue)/1024/1024) by InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":"

  Disk free space

Determines the percentage of free disk space for logical disks over the past day, grouped by instance name.

Perf
| where TimeGenerated > ago(1d)
| where ObjectName == "LogicalDisk"
| where CounterName == "% Free Space"
| summarize Free_Space_Percent = max(CounterValue) by InstanceName
| where strlen(InstanceName) ==2 and InstanceName contains ":"

References

• Kusto Query Language (KQL) overview