In this Azure Back to School post, we’re going to look at the Kusto Query language (KQL). KQL can be used with many different services across Azure, however, it is often overlooked. In this introductory blog post we will first look at the different services that utilize KQL, before looking at some basic syntax, and some real-world examples.

This post is part of the Azure Back to School series, and I would encourage you to look at the schedule for the rest of this year’s Azure Back to school sessions.

What is KQL

KQL is a query language, that much like SQL allows us to filter, aggregate and join large data sets together. Unlike SQL, KQL is read only and takes a different approach in the structure of the queries. The primary use of KQL is to help us better understand data for the purposes of diagnostics and analysis.

You’ll find KQL used throughout Azure, wherever you find large data sets. Let’s look at the most common datasets we would use a KQL query on.

Azure Data Explorer is a fully managed analytics service, we can stream information into it from websites, applications or IoT devices. Once the data is present in the platform, we can then explore it using KQL.

Similarly Azure log analytics takes data from a range of sources and allows us to query with KQL. Unlike Azure Data Explorer the data fed into log analytics is generally from other Azure services. This can include but is not limited to Azure Security Centre, Microsoft Sentinel, Microsoft Defender for Cloud), Azure Monitor Logs and Azure Application Insights data.

The Azure Resource Graph Explorer allows us to use KQL to query our Azure resources. With this we can find out information such as the number of server instances we have, tags, and other resource attributes. We can also query subscriptions resource groups and anything else that makes up our Azure tenant.

Azure Data Factory allows us to create workflows and perform transformation on data sets. We can use KQL in several ways with Azure data factory including with the import and export of data.

It is also possible to use KQL queries inside Grafana dashboards when using data based on the above sources.

As you can see there are many places we can use KQL to query our data. Although we will focus on only a few of these, the information found in the rest of this post can easily be transferred between the different places and datasets.

Basic KQL Syntax and Queries

For this section we’re going to use the Azure resource graph explorer. The reason for this is that you should have data already available from your current subscription and there is no need to configure anything else. Inside the Azure portal search for “Azure Resource Graph Explorer”. Once this is loaded you will see an area to write your query. The scope of your query will be defined in the top left-hand corner of the window.

Basic Selecting and Filtering Data

To select unfiltered data we just enter the name of the table and press Run query.

resources

As you will see this returns many columns. We can now limit the columns with the project keyword. Each line is passed the data from the previous using the pipe symbol.

resources
| project name, location, resourceGroup  

To rename the columns in the display we can prefix them with our alias

resources
| project name, location, RG=resourceGroup  

We can filter down the results using a where clause to only show web app resources in a resource group named demo.

resources
| project name, location, resourceGroup  
| where ['type']  == 'microsoft.web/sites' and resourceGroup  == "demo"

It is possible to use alternative comparison operators, negative statements and function such as has or contains.

Basic Aggregation

Like in SQL, a powerful feature is to aggregate data. This is done through the summarize keyword specifying an aggregation type and the grouping. This example shows a count of the website resources we have, grouped by resource group name.

resources
| project name, location, resourceGroup  
| where ['type']  == 'microsoft.web/sites'   
| summarize count() by resourceGroup, subscriptionId

Joining Data

A common scenario is wanting to join different data sets. In our above example we are showing the resource group name, subscriptionId and total. To make it more useful we can join on the resourcecontainers data set to provide the subscription name.

resources
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| summarize count() by resourceGroup, subscriptionName

The join operator, like in SQL, supports combinations of full, inner and outer joins. For more information see the learn documentation

Json Data

Sometimes data in a single column will be returned in a json format, take for example the SKU on a resource.

resources
| where type == "microsoft.web/serverfarms"
| project sku

We can turn this into separate columns to either filter or project with the expand keyword.

resources
| where type == "microsoft.web/serverfarms"
| extend size = tostring(sku["size"])
| extend tier = tostring(sku["tier"])
| project size, tier

Real-world Use Cases

Now we have covered the basics I wanted to share with you some of the queries that I have found useful and some stories where KQL has really helped me as an Azure User.

Reviewing Resources

I had been looking for the number of instances that each of our web apps were scaled to. This was to ensure that non were over provisioned. Using the resource graph explorer, I could run a query to answer this and export the results to excel to be reviewed.

resources
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| extend size = tostring(sku["size"])
| extend tier = tostring(sku["tier"])
| extend instances = toint(sku["capacity"])
| where type == "microsoft.web/serverfarms"
| project name, subscriptionName, size, tier, instances

Diagnosing Slow Queries

Azure application insights is a powerful tool however sometimes you want to query the data directly and produce custom reports. I had this exact problem while trying to pin down a slow performing API call. Luckly all the data was also available via a log analytics workspace.

Using this query I could produce a report of the time taken for API calls each day to see if things had slowed over time.

AppServiceHTTPLogs
| summarize percentiles(TimeTaken, 90, 95, 99),['Total Requests'] = count() by format_datetime(['Day of Month'] = TimeGenerated, 'dd')
| order by ['Day of Month'] asc

I could expand the query to include different paths and find the dependencies to establish the root cause.

Alerting on Resources

Inside a log analytics workspace, after we’ve written our query it is possible then to set up a new alert based on the results. This will run the query at a set interval and if the allocated threshold is met it will trigger the action group. I have used this before to monitor the health of an unstable application.

Going beyond the Basics

KQL is a large and fully featured querying language, and this is only a brief introduction. The Microsoft learn documentation covers it in much more detail. If you are familiar with SQL, Microsoft also has a helpful translation SQL to KQL cheat sheet.

Last year I took a look at one of the more complex areas, how we can run reports on tags in the resource graph explorer which also may be of interest.

Finally it’s worth reading over the best practice guide before getting too deep into the subject.

Summary

In this post we took an introductory look at the Kusto Query Language (KQL) in Azure. We highlighted its usage for filtering, aggregating, and joining large datasets for diagnostics and analysis.

We looked over KQL’s presence in various Azure services like Data Explorer, Log Analytics, Resource Graph Explorer, and more. Before then covering basic KQL syntax, including selecting, filtering, aggregation, and joining data. I hope you enjoyed reading the post and it inspired you to take a further look at the KQL syntax.

As mentioned earlier this blog post is part of the Azure Back to School series, and I would encourage you to look at the schedule for the rest of this year’s great Azure Back to school sessions.

Photo by Tech Daily on Unsplash