Kusto Queries in Azure

Summary: In Azure the kusto query language is mostly used to query Azure Monitor. On this page I explain a few of the basics I use a lot and a selection of examples I use or have used.
Date: Around 2023
Refactor: 8 December 2024: Added examples and hints from Cheat Sheet and refactored for new layout.

azure, kusto

Structure

Expression	Description	Example
==	Check equality (case-sensitive)	`Level == 8`
=~	Check equality (case-insensitive)	`EventSourceName =~ "microsoft-windows-security-auditing"`
!=, <>	Check inequality (both expressions are identical)	`Level != 4`
and or	Required between conditions	`Level == 16 or CommandLine != ""`

Command	Description	Example
take n	Ideally suited to small result sets. Take returns n rows from the result set in no particular order.	`AuditLogs \| Take 10`
top n by field	Use this filter command to return the top n rows, sorted by the nominated field.	`AuditLogs \| Top 10 by timeGenerated`
sort by field (desc)	If you want to sort only the result set, you can use the sort command. You need to specify the field to sort on, and then you can optionally add the desc instruction to specify a descending sort pattern.	`AuditLogs \| Sort by timeGenerated desc`
Where field (expression) value	The principal filtering command. You nominate the field, expression, and comparator value. You can stack multiple where commands, each separated by a pipe.	`AuditLogs \| where CreatedDateTime >= ago(2d)`
project fields	If you want to restrict the result set to display only nominated fields or columns, you can use the project command with a comma-separated list of the fields.	`AuditLogs \| project timeGenerated, OperationName, ResourceGroup, Result`

Basic Queries

Check for incoming data

Perf
| where TimeGenerated > ago(10m)
| order by TimeGenerated desc
 
Event
| where TimeGenerated > ago(10m)
| order by TimeGenerated desc
 
RabbitMQ_CL
| where TimeGenerated > ago(10m)
| order by TimeGenerated desc
 
W3CIISLog
| where TimeGenerated > ago(10m)
| order by TimeGenerated desc

Show errors

every 10 minutes in a stacked graph

APP_LOG
| where RawData contains "error"
| summarize count() by Computer, bin(TimeGenerated, 10m)
| order by TimeGenerated
| render areachart

Week overview of errors per service account in a column graph

APP_LOG
| where LogType_CF == '[error]'
| summarize count() by UserName_CF, Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated, UserName_CF
| render columnchart

All exceptions per 30 seconds per application

exceptions
| where outerMessage contains "timeout"
| where cloud_RoleName != ""
| summarize count() by bin(timestamp, 30s), cloud_RoleName
| order by timestamp

All high severity level exceptions of a specific type

exceptions
| where severityLevel > 2 and type == 'MassTransit.RequestTimeoutException'
| summarize count() by cloud_RoleName, bin(timestamp, 1d)
| render columnchart

SQL timeouts in a column graph

exceptions
| where outerMessage has "timeout" and outerType == "Microsoft.Data.SqlClient.SqlException"
| summarize count() by bin(timestamp, 1d), cloud_RoleName
| order by timestamp
| render columnchart

Unique Logitems per RoleName/RoleInstance

Get unique logitems per rolename/roleinstance in an appi log

requests
| distinct cloud_RoleName, cloud_RoleInstance
| sort by cloud_RoleInstance

Find all errors of cloud_rolename

union exceptions, traces
| where cloud_RoleName == "Shift.Service"
| order by timestamp desc

Log Analytics for Windows VMs

This obviously requires you to have the Log Analytics agent installed on the VMs, and send the logs to Log Analytics.

Failed Login Events VM

SecurityEvent
| where EventID = 4625
| summarize count() by TargetAccount

Users Flagged as Risky

See how many of your users were flagged as risky in the last 14 days:

SigninLogs
| where CreatedDateTime >= ago(14d)
| where isRisky = true

Errors from Server Eventlog

Event
| where TimeGenerated > ago(24h) and EventLevel == 1 and EventLog == 'Application'
| where Computer contains 'SRV01.SHIFT.LOCAL'
| limit 100

Errors from Server Eventlog - Search for service errors in time range

Event
| where TimeGenerated > startofday(datetime("2022-01-02")) and TimeGenerated < endofday(datetime("2022-01-04")) and EventLevel == 1 and EventLog == 'Application'
| where Computer contains 'appprd01.shift'
| where RenderedDescription contains "shift.ServiceName"

With eventlevel 1 or 2

Event
| where TimeGenerated between (datetime('2022-02-17 11:25')..datetime('2022-02-17 11:31'))
| where EventLevel <= 2

Searching for Crashed Service

Event
| where RenderedDescription  contains "coreServiceShell.exe"
| summarize count() by bin(TimeGenerated, 1d), Computer
| order by TimeGenerated

Queries for RabbitMQ log

This requires you to have the RabbitMQ logs in Log Analytics.

RabbitMQ Missed Heartbeats

RabbitMQ_CL
| where RawData contains "missed heartbeats"
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated

RabbitMQ Errors

RabbitMQ_CL
| where LogType_CF == '[error]'

Add a Timeframe to your query

RabbitMQ_CL
| where DateTime_CF between (datetime('2022-07-10 10:25')..datetime('2022-07-11 12:31')) and LogType_CF == '[error]'

RabbitMQ Errors per Server per Day

RabbitMQ_CL
| where LogType_CF == '[error]'
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated

Azure DevOps Audit Stream

yaml and classic release that were not successful

AzureDevOpsAuditing
| where OperationName == "Pipelines.DeploymentJobCompleted" or OperationName == "Release.DeploymentCompleted"
| where Data.DeploymentResult != "Succeeded"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 10m), deployment
| render columnchart

yaml releases to production that were successful

AzureDevOpsAuditing
| where OperationName == "Pipelines.DeploymentJobCompleted" and Data.DeploymentResult == "Succeeded" and Data.StageName contains "Production"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 1d), deployment
| render columnchart

classis releases that were not successful

AzureDevOpsAuditing
| where OperationName == "Release.DeploymentCompleted" and Data.DeploymentResult != "Succeeded"
| extend deployment = strcat(Data.PipelineName, "-", Data.StageName)
| project TimeGenerated, deployment
| summarize count() by bin(TimeGenerated, 1d), deployment
| render columnchart

Azure Resource Graph Explorer

The Azure Resource Graph Explorer also uses Kusto queries.

Get all resources, type, location, subName and rgName

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

Get all application insights resources without a workspace

resources
| where type == "microsoft.insights/components"
| where properties['WorkspaceResourceId'] == ""
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| project name, type, subscriptionName, resourceGroup
| order by subscriptionName asc

Get all storage accounts with an old tls version

resources
| where type == "microsoft.storage/storageaccounts"
| where properties['minimumTlsVersion'] != "TLS1_2"
| project name, resourceGroup, properties.minimumTlsVersion

Get backup job status

For use with multiple log analytics workspaces, change the scope

AddonAzureBackupJobs
| parse VaultName with 'bvault-euw-' Spoke '-' Environment '-' Service
| extend JobDurationInSecsApprox = datetime_diff('second', TimeGenerated, JobStartDateTime)
| where JobOperation == 'Backup'
| project BackupItemFriendlyName, JobOperation, JobStartDateTime, JobDurationInSecsApprox, JobStatus, JobFailureCode, Environment, Spoke, Service, BackupItemUniqueId, VaultName, JobUniqueId, _ResourceId

SHIFT-WIKI

Table of Contents