Kusto Query Language overview

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Kusto Query Language (KQL) is a powerful tool for exploring your data and discovering patterns, identifying anomalies and outliers, creating statistical modeling, and more.

KQL is a simple yet powerful language to query structured, semi-structured, and unstructured data. The language is expressive, easy to read and understand the query intent, and optimized for authoring experiences. KQL is optimal for querying telemetry, metrics, and logs with deep support for text search and parsing, time-series operators and functions, analytics and aggregation, geospatial, vector similarity searches, and many other language constructs that provide the most optimal language for data analysis. The query uses schema entities that are organized in a hierarchy similar to SQLs: databases, tables, and columns.

If you have a background in scripting or working with databases, much the content of this article should feel familiar. If not, don't worry, as the intuitive nature of the language quickly enables you to start writing your own queries and driving value for your organization.

This article provides an explanation of the query language and offers practical exercises to get you started writing queries. To access the query environment, use the KQL queryset. To learn how to use KQL, see Tutorial: Learn common operators.

What is a Kusto query?

A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate. Kusto queries are made of one or more query statements.

A Kusto query doesn’t write any data. Queries operate on data that's organized into a hierarchy of databases, tables, and columns, similar to SQL.

What is a query statement?

There are three kinds of user query statements:

• A tabular expression statement
• A let statement
• A set statement

All query statements are separated by a ; (semicolon), and only affect the query at hand.

Note

For information about application query statements, see Application query statements.

The most common kind of query statement is a tabular expression statement, which means both its input and output consist of tables or tabular datasets. Tabular statements contain zero or more operators, each of which starts with a tabular input and returns a tabular output. Operators are sequenced by a | (pipe). Data flows, or is piped, from one operator to the next. The data is filtered or manipulated at each step and then fed into the following step.

It's like a funnel, where you start out with an entire data table. Each time the data passes through another operator, it's filtered, rearranged, or summarized. Because the piping of information from one operator to another is sequential, the query operator order is important, and can affect both results and performance. At the end of the funnel, you're left with a refined output.

Let's look at an example query.

StormEvents
| where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01))
| where State == "FLORIDA"
| count

Count
28

Note

KQL is case-sensitive for everything – table names, table column names, operators, functions, and so on. Keywords can be used as identifiers by enclosing them in brackets and quotes ([' and '] or [" and "]). For example, ['where']. For more information, see Identifier naming rules

This query has a single tabular expression statement. The statement begins with a reference to a table called StormEvents and contains several operators, where and count, each separated by a pipe. The data rows for the source table are filtered by the value of the StartTime column and then filtered by the value of the State column. In the last line, the query returns a table with a single column and a single row containing the count of the remaining rows.

To try out some more Kusto queries, see Tutorial: Write Kusto queries.

Management commands

In contrast to Kusto queries, Management commands are requests to Kusto to process or modify data or metadata. For example, the following management command creates a new Kusto table with two columns, Level and Text:

.create table Logs (Level:string, Text:string)

Management commands have their own syntax, which isn't part of the KQL syntax, although the two share many concepts. In particular, management commands are distinguished from queries by having the first character in the text of the command be the dot (.) character (which can't start a query). This distinction prevents many kinds of security attacks, simply because it prevents embedding management commands inside queries.

Not all management commands modify data or metadata. The large class of commands that start with .show, are used to display metadata or data. For example, the .show tables command returns a list of all tables in the current database.

For more information on management commands, see Management commands overview.

KQL in other services

KQL is used by many other Microsoft services. For specific information on the use of KQL in these environments, refer to the following links:

• Log queries in Azure Monitor
• Understanding the Azure Resource Graph query language
• Proactively hunt for threats with advanced hunting in Microsoft 365 Defender
• CMPivot queries

Related content

• Tutorial: Learn common operators
• Tutorial: Use aggregation functions
• KQL quick reference
• SQL to KQL cheat sheet
• Query best practices