Kusto Query Language (KQL): a primer

What is Kusto Query Language (KQL)?

KQL is the query language used by Azure Data Explorer (ADX) and a number of other Microsoft Azure services built on top of ADX, such as Log Analytics and Application Insights, as well as other Microsoft services such as Microsoft Defender. It enables users to perform complex data analysis tasks and extract valuable insights from large data sets. KQL is designed to be user-friendly, easy to learn, and efficient for data querying.

Getting started with KQL

Syntax conventions below based on related Microsoft documentation:

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/syntax-conventions

As you start exploring KQL, it’s essential to familiarize yourself with the basic syntax and common types of user queries. The primary building blocks of KQL are:

Data sources (tables)
Columns (fields)
Rows (records)

KQL queries follow a simple structure: data_source | command. The pipe symbol | separates the data source from the commands you want to apply to that data.

KQL is case-sensitive

Before we get too much farther, be aware that KQL is case-sensitive for EVERYTHING. Table names, column names, operators, functions, etc. To my knowledge, the only thing that isn’t case-sensitive by default is when you passing a parameter to the search operator.

Common types of user KQL queries

Here are some of the most common types of KQL queries that you’ll likely encounter:

Counting

Use the count command to get the number of records that match the preceding query.

data_source | count

Filtering

Use the where command to filter rows based on a condition.

data_source | where column operator value

Projection

Use the project command to select specific columns from the data source.

data_source | project column1, column2, …

You can also use project to rename, drop, or insert new computed columns (more on that later).

Aggregation

Use the summarize command to perform aggregation operations like count, sum, average, min, and max.

data_source | summarize aggregation_function(column) by grouping_column

Sorting

Use the order by command to sort the results by one or more columns.

data_source | order by column [asc | desc]

Limiting

Use the take command to limit the number of rows returned by the query. You can also use the alias limit in place of take.

data_source | take number_of_rows

Common KQL syntax

To help you dive into KQL, let’s look at some of the most commonly used syntax. I’ll dig into these and demonstrate in future posts.

Operators

KQL supports arithmetic, comparison, and logical operators to create conditions and perform calculations.

+, -, *, /, %, ==, !=, <, >, <=, >=, and, or, not

Functions

KQL provides various functions for manipulating data, such as date and time, string, and mathematical functions.

datetime_add(), now(), strlen(), trim(), round(), floor(), ceiling()

Joining

Use the join command to combine rows from two data sources based on a matching key.

data_source1 | join kind=[inner | leftouter | rightouter | fullouter] data_source2 on key_column

Time series

KQL supports time-series data analysis with a make-series command that generates a time-series set.

data_source | make-series aggregation_function(column) on time_column from start_time to end_time step time_interval

Comments

You can annotate your queries with comments to help readers better understand intent. KQL currently only supports a single line comment operator of //. If you want multiple lines of comments, precede each line with //.

// This is a comment.
// This is also a comment.

Formatting queries

When authoring Kusto queries, the common practice is to start a new line when you use the | symbol. This improves readability and makes the query easier to edit and update.

KQL query example

Let’s look at a simple KQL query to tie it all together. Let’s say we want to find the top 10 types of Azure operations that have been performed within the last 24 hours.

We will use the table AzureActivity and the columns OperationName and TimeGenerated.


AzureActivity
| where TimeGenerated > ago(24h) 
| summarize OperationCount=count() by OperationName 
| order by OperationCount desc 
| take 5

// This query filters activities in the last 24 hours (where), counts the number of each type of operation (summarize and count()), sorts operations by count in descending order (order by), and finally takes the top 5 operations (take).

Here are some example results:

OperationName	OperationCount
List Storage Account Keys	1780
Dynamic Invoke Connection.	466
Get Restore Point along with blob SAS URIs	260
Delete Restore Point	225
Write queue	204

More to come

In future posts, I’ll delve deeper into KQL’s capabilities and applications. Whether you’re a data analyst, engineer, scientist