Most Useful Azure KQL queries

Background

Kusto Query Language is an open source query language used by Microsoft to query data from different information sources

Common Uses

  • Log Analytics / Alerts
  • Querying and visualizing data your typical database
  • Getting information on resources and configuration of resources in your environment

In this post, I will be reviewing some very useful queries as administrators, I have used in my day to day work. I definitely love to hear about yours as well.

Tools You will Need

Reader role : Azure Account with at least Reader RBAC to see the subscription or objects wont be be shown in your queries

Launch resource graph explorer

Example 1: VNET and Subnet KQL Query

Sometimes its nice to have an IPAM or inventory of the VNET and subnet ranges allocated. Here’s a quick KQL that you can run in resource graph explorer to find this out

resources
| where type == "microsoft.network/virtualnetworks" 
| mvexpand subnet=properties.subnets
| project name, subnet.name, subnet.properties.addressPrefix

Example 2: Getting a list of all images running on VMs in Azure

resources
| where type == "microsoft.compute/virtualmachines"
| extend os_type = properties.storageProfile.osDisk.osType
| extend publisher = properties.storageProfile.imageReference.publisher
| extend offer = properties.storageProfile.imageReference.offer
| extend sku = properties.storageProfile.imageReference.sku
| project name, os_type, publisher, offer,sku

Get a list of Virtual Machines Running in Azure

Example 3:CPU Utilization Alert (Log Analytics Example)

KQL queries are used in alerts heres an example of a CPU Utilization alert. Note there a few more pre-steps to set this one up

  1. Ensure you have azure loganaltyics workspace created
  2. Ensure your vms are enable to send insights metrics to the log analytics workspace

Setting up the alert

  1. Log into Azure Monitor->Logs (we will be using the metricsInsights table)
  2. Lets a create a new query
  3. Type the following

InsightsMetrics 
| where Namespace  == 'Processor'
| where Name  like 'UtilizationPercentage'
| where todecimal(Val) > 80

What this will do for VMs that we enabled to allow insight metrics we should see rows where percent utilization or CPU load is over 80%.

Now lets click “New Alert Rule”

Before clicking “Review + Create”, make sure you have an action group created. For now I recommend setting up email if you dont have an action group set to see it fire.