Introduction to Kusto query language (KQL) in Azure Monitor

Azure Monitor enables you to analyze the availability and performance of your applications, services, and servers.

Azure Monitor allows you to write queries against logs and metrics. These queries are written in Kusto Query Language or KQL. This language, similar to a SQL dialect, is not only used in Azure Monitor queries but also in Azure Data Explorer.
KQL is based on standard database query languages like MS SQL Server or MySql and supports concepts like Tables and Columns.

Let’s try it but as every environment is different, to be sure you can use the code using in this blog post, let use the demo environment provided by Azure. You can find it here

You should end on this page

On the left, you will see all the tables by category, and on the upper right the query editor.

To add a predicate to your query you need to use the | characters. The pipe characters separate expressions in KQL.
By convention, and for better readability, you should add a carriage return before any expression or predicate.
How to search for a particular computer, DC01.NA.contosohotels.com, for example.
The first query you can make is to use the search operator. The search operator will search a value in all columns of the table.

InsightsMetrics 
| search  "DC01.NA.contosohotels.com"

One thing you need to know about KQL, the language is case sensitive, table, operator and values are case sensitive. Event search operations are case sensitive most of the time.
If you mistype the name of a table in the query editor, you will get the error “Failed to resolve table or column or scalar expression…”.
Try it, type Insightsmetrics in the query editor and hit the “Run” button. The query should fail but if you correct the name of the table by changing Insightsmetrics into InsightsMetrics the query should run normally.
The InsightsMetrics table, contains all metrics, such as heartbeat, CPU, disk, … collected by VM Insights.
When you type the name of a table in the editor you obtain the whole table. Converted to SQL you will have SELECT * FROM TableName.
Running this kind of query gives you a dataset. But there is no filter, the data set you obtain is the whole table. If you want to restrict the result you need to use a predicate.

But performance matters and the search operator is not a good option. Instead, you can use the WHERE operator with the name of the column you want to filter. The computer name is stored in the Computer column.

InsightsMetrics 
| where Computer ==  "DC01.NA.contosohotels.com"

The == expression check for the exact term in the column. This expression
is case sensitive so if you type "dc01.NA.contosohotels.com" instead, the query will return no results found.
To perform a non-case sensitive search you can use the =~ expression.

InsightsMetrics 
| where Computer =~ "dc01.NA.contosohotels.com"

You can also use the has operator

InsightsMetrics 
| where Computer has "dc01.NA.contosohotels.com"

The has operator can be used to search for part of the string, dc01 instead of the complete computer name.

InsightsMetrics 
| where Computer has "dc01"

But when you query a table you may want only the most recent data or data from a particular time frame.

To get data between 7:30 AM and 8:30 AM on 2020-12-27 you can use this query:

InsightsMetrics 
| where TimeGenerated  >= datetime('2020-12-27 07:30:00') and  TimeGenerated <= datetime('2020-12-27 08:30:00')

The data type for the TimeGenerated column is DateTime, so you need to cast data into the DateTime data type with the DateTime function.

If you want to get the result from the current time and a timespan you can use the ago() function. This function substracts the current DateTime with the timespan.
To filter data between now and one hour.

InsightsMetrics 
| where TimeGenerated  >= ago(1h)

To specify the timespan, you can use one of these identifiers

• d day
• h hours
• m minutes
• s seconds
• ms millisecond

If you want to get data for the last 2 days

InsightsMetrics 
| where TimeGenerated  >= ago(2d)

It’s possible to combine filters to your query. You can select data from one computer and for a selected time frame.

InsightsMetrics 
| where TimeGenerated  >= ago(1h)
| where Computer ==  "DC01.NA.contosohotels.com"

The first filter is a time filter. Kusto is optimized to manage time filters. Having a time filter in the first position creates a dataset you can easily query without performance issues.

Now let’s try to dig a little more and try to see how we can extract more data from the table.

The InsightsMetrics table store your VM’s metrics, it includes processor utilization, network, memory, network, and disk counters. These metrics are organized by Namespace and counter names (name).
How can you get the complete list for a VM? To perform that you need to aggregate NameSpace and Name.

InsightsMetrics 
| where Computer ==  "DC01.NA.contosohotels.com"
| summarize by Namespace, Name

Summarize operator aggregate NameSpace and Name into a new table. You can also add a count to see how many times a counter is present in the result.

InsightsMetrics 
| where Computer ==  "DC01.NA.contosohotels.com"
| summarize count() by Namespace, Name

You can also add a filter the number of measures during the last ten minutes.

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| summarize  count() by Namespace, Name

You can see that there are about 10 processor/UtilizationPercentage measure. To see them

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Processor" and Name == "UtilizationPercentage"

You can limit the output to the only columns you need by using the project operator with the list of columns you want to output.

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Processor" and Name == "UtilizationPercentage"
| project TimeGenerated, Val

With the last query, you will have a table with a list of values and the corresponding measure time. But Kusto offers us a better way to present these data. Kusto allows you to create graphics by using the render operator. It changes the output into a graphic. You can choose a timechart, a scatterchart or and areachart, a barchart, a columnchart, a piechart (but it will not work here), or you can also choose a table which is the default output.

InsightsMetrics 
| where TimeGenerated >= ago(30m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Processor" and Name == "UtilizationPercentage"
| project Val, TimeGenerated
| render areachart 

It returns

To analyze CPU utilization, it can better to compare this result against another computer. Instead of using the == operator, you need to use the in operator. In, filter the result based on a set of data enclosed by ( and ).

InsightsMetrics 
| where TimeGenerated >= ago(1h)
| where Computer in ("DC01.NA.contosohotels.com" , "DC11.NA.contosohotels.com") 
| where Namespace == "Processor" and Name == "UtilizationPercentage"
| project Val , TimeGenerated,Computer
| render timechart   

The in expression includes two computers DC01 and DC11. You may also notice two changes. The project operator is used to limit the output is used with a new column, Computer. It’s mandatory for the chart. Finally, the render option was changed to timechart for better readability.

The result

You may want to create the same chart but with more than two computers.
To create the chart using any computer with a name starting by DC you can use startswith or startswith_cs. The second option is better. We know that all computer names start with DC and case-sensitive search is more efficient so we can use startswith_cs.

InsightsMetrics 
| where TimeGenerated >= ago(1h)
| where Computer startswith_cs "DC" 
| where Namespace == "Processor" and Name == "UtilizationPercentage"
| project Val , TimeGenerated,Computer
| render timechart   

It’s possible to make calculation inside a query. For example, if you query the namespace memory to get the amount of free memory, the returned value is in Megabytes. You can convert this value to Gigabytes by using the extend operator.

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"
| extend FreeMemGb = Val/1024
| project TimeGenerated, FreeMemGb

To use extend you need to create a new value, FreeGb, with a name that do not belong to any other column following by = and the calculation. It creates a new column, FreeMemGb, you can add the output with the project operator.

But if instead of having the amount of free memory, an absolute value, you want to the percentage of free memory? If you look closer to the table using this query:

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"

You will notice the column named Tags; it contains this value {"vm.azm.ms/memorySizeMB":8191.55078125}. To compute a percentage, you need to extract the data (8191.55078125) from the JSON.

The first question you can ask, what are the datatype of Val and Tags columns. KQL can answer the question.

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"
| extend typeVal = gettype(Val)
| extend typeTags = gettype(Tags)

You will find that Val is a real and Tags is a string.

Second question, how do I transform the JSON data from Tags into real before computing the percentage.

There are two steps, extract the value of vm.azm.ms/memorySizeMB from the JSON data. You can use the toobject() function to use the col as a JSON data and use the result to get data from ["vm.azm.ms/memorySizeMB"].

toobject(Tags)["vm.azm.ms/memorySizeMB"]

The second step is to cast the value from string to real. For that, you will need to use the function toreal().

toreal(toobject(Tags)["vm.azm.ms/memorySizeMB"])

With that, you can create a new output based on the calculus of the percentages

| extend AvailableMemoryPercentage = (Val / toreal(toobject(Tags)["vm.azm.ms/memorySizeMB"])) * 100.0

The result is a real, you may want to round the result. You will need to use the function round(). It takes two arguments, the number you want to round, and the precision

| extend AvailableMemoryPercentage = round((Val / toreal(toobject(Tags)["vm.azm.ms/memorySizeMB"])) * 100.0, 2)

The complete query

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"
| extend AvailableMemoryPercentage = round((Val / toreal(toobject(Tags)["vm.azm.ms/memorySizeMB"])) * 100.0, 2)
| project AvailableMemoryPercentage

You can re-write the query like this

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"
| extend AvailableMemoryPercentage = round(
        (Val / toreal(
            toobject(Tags)["vm.azm.ms/memorySizeMB"]
            )
        ) * 100.0
        , 2
    )
| project AvailableMemoryPercentage

You may think the percentage calculation doesn't stop here. You may want to use this formula in other queries or for other values.

There is a way to create a function in Kusto by using the let statement. It binds an expression to a name you can use in another part of your query.
You can use to create variables

let vmName = "DC01.NA.contosohotels.com";

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  vmName
| where Namespace == "Memory" and Name == "AvailableMB"

To create a variable, you need to use the let statement following with the name of the variable, the = sign, and your data. The let statement must end with a semi-column.

Let allow also to enclose an entire expression and create a function. In this case, the let statement is divided into two parts, the first one, in the beginning, is enclosed by parenthesis and define the function’s parameters. Parameters in a function are typed. To declare a parameter you need to provide the name of the parameter following by : and the data type, paramName: type (real, string, …).
The second part, enclosed by {}, define the expression and return a value.

To create a function returning a rounded percentage

let roundedFreePercentage = (
        freeValue: real,
        TotalValue: real 
    )
{
    round(
        (freeValue / TotalValue) * 100.0
        , 2
    )
};

InsightsMetrics 
| where TimeGenerated >= ago(10m)
| where Computer ==  "DC01.NA.contosohotels.com"
| where Namespace == "Memory" and Name == "AvailableMB"
| extend AvailableMemoryPercentage = roundedFreePercentage(Val, toreal(toobject(Tags)["vm.azm.ms/memorySizeMB"]))
| project AvailableMemoryPercentage

This short introduction to Kusto Query Language may help you to understand a little more how to query Azure Monitor to extract data from Azure Monitor metrics and events. If you want to learn more about KQL you can check the Microsoft Documentation you can also check this MS Learn Module