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.
| 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 |
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
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
exceptions | where outerMessage has "timeout" and outerType == "Microsoft.Data.SqlClient.SqlException" | summarize count() by bin(timestamp, 1d), cloud_RoleName | order by timestamp | render columnchart
Get unique logitems per rolename/roleinstance in an appi log
requests | distinct cloud_RoleName, cloud_RoleInstance | sort by cloud_RoleInstance
union exceptions, traces | where cloud_RoleName == "Shift.Service" | order by timestamp desc
This obviously requires you to have the Log Analytics agent installed on the VMs, and send the logs to Log Analytics.
SecurityEvent | where EventID = 4625 | summarize count() by TargetAccount
See how many of your users were flagged as risky in the last 14 days:
SigninLogs | where CreatedDateTime >= ago(14d) | where isRisky = true
Event | where TimeGenerated > ago(24h) and EventLevel == 1 and EventLog == 'Application' | where Computer contains 'SRV01.SHIFT.LOCAL' | limit 100
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"
Event
| where TimeGenerated between (datetime('2022-02-17 11:25')..datetime('2022-02-17 11:31'))
| where EventLevel <= 2
Event | where RenderedDescription contains "coreServiceShell.exe" | summarize count() by bin(TimeGenerated, 1d), Computer | order by TimeGenerated
This requires you to have the RabbitMQ logs in Log Analytics.
RabbitMQ_CL | where RawData contains "missed heartbeats" | summarize count() by Computer, bin(TimeGenerated, 1d) | order by TimeGenerated
RabbitMQ_CL | where LogType_CF == '[error]'
RabbitMQ_CL
| where DateTime_CF between (datetime('2022-07-10 10:25')..datetime('2022-07-11 12:31')) and LogType_CF == '[error]'
RabbitMQ_CL | where LogType_CF == '[error]' | summarize count() by Computer, bin(TimeGenerated, 1d) | order by TimeGenerated
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
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
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
The Azure Resource Graph Explorer also uses Kusto queries.
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
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
resources | where type == "microsoft.storage/storageaccounts" | where properties['minimumTlsVersion'] != "TLS1_2" | project name, resourceGroup, properties.minimumTlsVersion
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