= 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 [[cheatsheet]] and refactored for new layout. \\
{{tag>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 ==

<code kusto>
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
</code>

== Show errors ==

> every 10 minutes in a stacked graph

<code kusto>
APP_LOG
| where RawData contains "error"
| summarize count() by Computer, bin(TimeGenerated, 10m)
| order by TimeGenerated
| render areachart
</code>
\\

> Week overview of errors per service account in a column graph

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

> All exceptions per 30 seconds per application

<code kusto>
exceptions
| where outerMessage contains "timeout"
| where cloud_RoleName != ""
| summarize count() by bin(timestamp, 30s), cloud_RoleName
| order by timestamp
</code>
\\

> All high severity level exceptions of a specific type

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

== SQL timeouts in a column graph ==

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

== Unique Logitems per RoleName/RoleInstance ==

> Get unique logitems per rolename/roleinstance in an appi log

<code kusto>
requests
| distinct cloud_RoleName, cloud_RoleInstance
| sort by cloud_RoleInstance
</code>

== Find all errors of cloud_rolename ==

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

= 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 ==

<code>
SecurityEvent
| where EventID = 4625
| summarize count() by TargetAccount
</code>

== Users Flagged as Risky ==

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

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

== Errors from Server Eventlog ==

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

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

<code>
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"
</code>

=== With eventlevel 1 or 2 ===

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

== Searching for Crashed Service ==

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

= Queries for RabbitMQ log =

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

== RabbitMQ Missed Heartbeats ==

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

== RabbitMQ Errors ==

<code>
RabbitMQ_CL
| where LogType_CF == '[error]'
</code>

=== Add a Timeframe to your query ===

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

== RabbitMQ Errors per Server per Day ==

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

= Azure DevOps Audit Stream =

== yaml and classic release that were not successful ==

<code kusto>
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
</code>

== yaml releases to production that were successful ==

<code kusto>
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
</code>

== classis releases that were not successful ==

<code kusto>
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
</code>

= Azure Resource Graph Explorer =

The Azure Resource Graph Explorer also uses Kusto queries. \\

== Get all resources, type, location, subName and rgName ==

<code kql>
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
</code>

== Get all application insights resources without a workspace ==

<code kusto>
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
</code>

== Get all storage accounts with an old tls version ==

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

== Get backup job status ==

<WRAP tip> For use with multiple log analytics workspaces, change the scope</WRAP>

<code kusto>
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
</code>

= Useful links =

* [[https://learn.microsoft.com/en-us/azure/devops/organizations/audit/auditing-streaming?view=azure-devops|Azure Devops: Audit Stream to Log Analytics]]
* [[https://learn.microsoft.com/en-us/azure/devops/organizations/audit/auditing-events?view=azure-devops|Azure Devops: Auditing event list]]