Kusto Query Language (KQL): What is it?

Wondering what KQL is, and if someone just mistyped SQL on their keyboard? In this article, we cover all the basics: What KQL is, how it differs from SQL, and what people use it for. We also share how you can get started working with some examples of basic KQL queries.

What is KQL?

Kusto Query Language, or KQL, is a read-only request language used to write queries for Azure Data Explorer (ADX), Azure Monitor Log Analytics, Azure Sentinel, and more. The request is stated in plain text, using a data-flow model that is easy to read, author, and automate.

In KQL, operators are sequenced by a | (pipe), and the data is filtered or manipulated at each step before being fed into the following step. This sequential piping of information makes the order of query operators important, which can affect both results and performance. 

Each query consists of one or more query statements, which can be a tabular expression statement, a let statement, or a set statement, all separated by a semicolon.

KQL was developed by Microsoft as a way to rectify perceived design decisions with SQL and provide a more intuitive and powerful querying capability. For instance, SQL's structure, which starts with a SELECT clause, was seen as hindering the natural flow of query writing. Additionally, SQL's rigid structure requiring cumbersome nesting and its various filtering clauses for different scenarios were viewed as confusing. 

What is KQL used for?

Since KQL does not modify data, it is solely used to fetch, filter, analyze, and visualize it. KQL excels at handling large volumes of structured and semi-structured data, and it is particularly useful in Azure Data Explorer, where it enables fast, interactive analysis of huge amounts of data.

However, KQL also has other strengths outside of data exploration, such as:

• Troubleshooting and Diagnostics: With its ability to process logs and telemetry data, KQL plays a crucial role in identifying anomalies, tracing errors, and diagnosing problems in a system.

• Real-time Analytics: KQL is adept at analyzing real-time data streams, making it ideal for applications like IoT data analysis, user behavior analytics, application performance monitoring, and more.

• Data Visualization: KQL queries can be used in conjunction with Azure Data Explorer and Azure Dashboards to create intuitive, real-time data visualizations.

KQL Vs. SQL: How is KQL Different from SQL?

At first glance, SQL and KQL share a lot of similarities beyond the name. They’re both used to retrieve data, and they’ve got similar hierarchical organizations around databases, tables, and columns. However, they are designed to do significantly different things, and therefore they’ve got key differences in their structure, usage, and capabilities.

The underlying model: KQL uses a data flow model, where the output and input of each step are tabular datasets, making the order of operations important.

Purpose: SQL is designed for managing structured data in relational databases. KQL is designed for querying large volumes of structured and semi-structured data, including logs and telemetry data, in real-time analytics scenarios.

Data Modifying Capabilities: SQL is a read-write language, meaning it can both fetch and modify data in a database. In contrast, KQL is read-only, which means it's only used for querying and analyzing data without any modification.

Complexity and Learning Curve: SQL is known for its complexity with multiple clauses, subqueries, and intricate joining capabilities, providing a steeper learning curve. Conversely, KQL is designed to be simpler, focusing on easy-to-write log queries and offering built-in functions to analyze time series data.

Integration with Azure Data Explorer: KQL is natively integrated with Azure Data Explorer, meaning it offers more streamlined performance and capabilities within the Azure ecosystem. SQL, while powerful, is a more general language not specifically tied to a single ecosystem.

Transitioning from SQL to KQL

Transitioning from SQL to KQL is made easier with a handy cheat sheet provided by Microsoft, which maps SQL commands and concepts to their KQL equivalents. This cheat sheet covers a wide range of categories such as selecting data from tables, null evaluation, comparison operators, grouping, aggregation, and more. It's a great tool for those who are already familiar with SQL and want to learn KQL.

Microsoft also provides a method to translate SQL queries into KQL. By prefacing the SQL query with a comment line, --, and the keyword explain, you can see the KQL version of the query, which can help you understand the KQL syntax and concepts.

How to Use KQL

While a comprehensive guide on using KQL would be beyond the scope of this article, here's a brief outline on using the language:

1. Setup: To start using KQL, you need to have an Azure account and set up Azure Data Explorer.

2. Basic Query: A basic KQL query follows the pattern: <table_name> | where <condition> | project <columns>. For instance, StormEvents | where State == "FLORIDA" | project StartTime, EndTime, State, EventType would return the start time, end time, state, and event type for all storm events in Florida.

3. Functions and Operators: KQL supports a variety of operators for arithmetic, comparison, logical operations, and string manipulations. It also has a rich library of functions for array processing, datetime manipulations, machine learning, etc.

4. Aggregation and Joins: KQL supports summarizing data through aggregation functions like summarize, count, avg, etc. You can also perform joins between tables, similar to SQL, with the join operator.

5. Time Series Analysis: With the make-series operator, you can create time series and apply further analysis with various built-in functions.

In KQL, everything is case-sensitive, including table names, table column names, operators, and functions

For an excellent rundown on how to get started with a basic KQL recipe for Azure monitoring, check out Amy Coughlin’s article, “How to use KQL for Azure monitoring (A basic recipe guide).”

Some examples of KQL queries

A single-statement KQL script

Tweets | where Language == "English" | summarize Count = count() by Hashtags | order by Count desc | take 10

So in this query, we’re analyzing a hypothetical table called Tweets that contains data (you guessed it) about Tweets. Here’s a breakdown of what this query does.

• Tweets: This specifies the table we are querying. In this case, it's a table that contains tweet data.
  
• where Language == "English": This is a filter that selects only the rows where the Language column is English. The pipe | character is used to pass the output of one operation to the next operation.
  
• summarize Count = count() by Hashtags: This groups the data by the Hashtags column and counts the number of rows in each group. The count for each group is stored in a new column called Count.
  
• order by Count desc: This sorts the output by the Count column in descending order.
  
• take 10: This limits the output to the top 10 rows.
  

So, in summary, the whole query is finding the top 10 most commonly used hashtags in English language tweets.

A multiple-statement KQL script

let TopHashtags = Tweets | summarize Count = count() by Hashtags | order by Count desc | take 10;

TopHashtags | project Hashtags

In this example, there are two query statements. The first statement creates a variable called TopHashtags that holds the top 10 hashtags. The second statement projects or selects only the Hashtags column from TopHashtags. The two statements are separated by a semicolon​.

Using a KQL query to detect unsigned drivers

Here is an example of how a security analyst might use KQL to detect potential security threats, such as unsigned drivers, by querying device event logs and applying filters to the data.

let DriverLoads = DeviceImageLoadEvents | where InitiatingProcessFileName == "spoolsv.exe"; DriverLoads

In this query, DeviceImageLoadEvents is the table being queried, and the where operator is used to filter the data. The query returns all entries where the InitiatingProcessFileName equals "spoolsv.exe"

KQL Commands and Control Commands

In addition to KQL queries, Kusto also supports control commands. These commands request Kusto to process or modify data or metadata. For example, a control command can create a new Kusto table. 

Control commands have their own syntax, which is separate from the KQL syntax. They are distinguished from queries by the first character in the command text being a dot (.) character, which can't start a query. This distinction helps prevent security attacks by prohibiting the embedding of control commands inside queries.

Learning more about monitoring your Azure data

If you are interested in learning about features in Azure that can produce data worthy of inquiry with KQL, check out Amy Coughlin’s Azure certification course titled, “AZ-204: Developing Solutions for Microsoft Azure.” You can also check out Pluralsight's Azure Data learning path, which has video courses for everyone from Azure beginners to practicing professionals.

Other FAQs about KQL

Is KQL easy to learn?

Yes! Compared to SQL, KQL’s syntax and structure are designed to be easy to read and write, potentially making it more accessible to newcomers than SQL. However, as with any language, mastering KQL requires practice and experience.

Is KQL better than SQL?

Whether KQL is "better" than SQL largely depends on the specific use case. KQL's data-flow model can be more intuitive and flexible than SQL's rigid structure, particularly for complex data analysis tasks. However, SQL remains the standard for relational database management systems and has a vast user base and ecosystem.