Introduction to Kusto Query Language
IT Tips & Insights: An experienced Software Engineer shares KQL shortcuts in this helpful primer.
By Fabio Godinho, Softensity Software Engineer
KQL, or Kusto Query Language, is a language for data analysis typical to Microsoft Azure monitoring and observability services (such as Application Insights, Log Analytics, and others). It makes use of the Kusto Engine to analyze large datasets in a few seconds. Initially, KQL was only used in Microsoft’s internal tools. From 2017 onwards, it started to appear in public Microsoft’s cloud services.
For a better understanding of the concepts, let’s have some practice. For this, we can use the portal provided by Microsoft (free of charge). This portal uses the Application Insights tool to display the data. However, we can also use other means such as Log Analytics, where only the data source would be different. However, the query language used there is the same.
In this portal, we can find several collections of pre-filled data to explore the capabilities of KQL using, for example, the exceptions table. The simplest way to query against your data is using the table name. To get all the data from a table, include the table name and the run button to execute the query.
table
It is possible to execute multiple processes in your data, separating them by a pipeline “|”. For example, querying all of your data and limiting the number of results.
table | limit 10
The query time range can be included in the query or included in the portal query window.
This series of articles will cover some of the most common commands that will probably be the most used in everyday life, whether resource management or application debugging. The most straightforward commands you would use to filter your queries will be ‘search’ and ‘where.’
Search
Search will search for a given term in all columns of a given table. It is also possible to search for a word across all tables in your database, which is not a good idea in cases of extensive datasets. If you need to search for a term in more than one table in your database, search allows you to delimit the search in some tables.
search in (table) “term”
It is also possible to search for a given term in a given column.
table | search column_name == ‘term’
You can also search for a value in any part of the text of the informed column. Use the following syntax:
table | search column_name: “value”.
As in more traditional query languages, some wildcards help you find data in any part of the input text:
table | search “* term *”
There are some helpful ways to do this for more specific cases where you only want to search for sentences at the start of the text, or the end.
To search in all columns for text starting with “term”:
table | search column_name startswith “term”
Remember, the search command is not case sensitive, but this can be changed using a switch (modifier) of the search command, as follows:
table | search kind=case_sensitive.
In addition to these options, you can also combine queries logically:
table | search “term” and (“term_1” or “term_2”),
Or use regex for more advanced searches:
table | search column_name matches regex “[A-Z]”
Where
Like the search command, the ‘where’ command assists in data query in a controlled way, limiting the results. The difference between the two is that instead of searching for terms in columns, ‘where’ limits the results based on conditions (which does not prevent searches for words).
The basic syntax of the where is as follows:
table | where condition_1 == condition_2
As in the case of search, it is possible to combine the search clauses, building robust and specific queries according to the user’s needs.
table | where condition_1 == condition_2
and (condition_3 == 1
or
condition_4 == 0)
and condition_5 > 10
As in the case of search, it is possible to perform searches by terms, in specific columns, or all columns. It is also possible to perform term searches in a positional way, at the beginning of the text:
table | where column_name hasprefix “term”
Or at the end of the text:
table | where column_name hassuffix “term”
Or anywhere in the text:
table | where column_name contains “term”
You can also use where with regex:
table | where * matches regex “term”
With these two simple commands, we were able to limit searches and find important information about the behavior of applications or resources running in our environment. In future articles, we will cover more commands such as summarize, extend and project.
About
Hi, I’m Fabio, a 35-year-old software engineer with 10+ years of experience in .NET stack. I’m currently learning and exploring the SRE and DevOps universe.