KQL queries every Azure VM operator should keep handy

These queries assume the Azure Monitor Agent sends data to a Log Analytics workspace. VM insights writes to InsightsMetrics, and classic performance counter DCRs write to Perf. Use whichever table your workspace has.

#Which VMs stopped reporting?

Heartbeat
| summarize LastSeen = max(TimeGenerated) by Computer, OSType, ResourceGroup = _ResourceId
| where LastSeen < ago(10m)
| order by LastSeen asc

A missing heartbeat means the agent isn't sending, which isn't always the same as the VM being down. Check the VM's power state before you escalate.

#CPU: top consumers in the last hour

InsightsMetrics
| where TimeGenerated > ago(1h)
| where Namespace == "Processor" and Name == "UtilizationPercentage"
| summarize AvgCPU = avg(Val), P95CPU = percentile(Val, 95) by Computer
| top 15 by P95CPU desc

With the Perf table (Windows counters):

Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
| summarize P95CPU = percentile(CounterValue, 95) by Computer
| top 15 by P95CPU desc

#Memory pressure

InsightsMetrics
| where TimeGenerated > ago(1h)
| where Namespace == "Memory" and Name == "AvailableMB"
| extend TotalMB = todouble(todynamic(Tags)["vm.azm.ms/memorySizeMB"])
| summarize MinAvailMB = min(Val), TotalMB = max(TotalMB) by Computer
| extend MinAvailPct = round(100 * MinAvailMB / TotalMB, 1)
| where MinAvailPct < 10
| order by MinAvailPct asc

#Disks running out of space

InsightsMetrics
| where TimeGenerated > ago(30m)
| where Namespace == "LogicalDisk" and Name == "FreeSpacePercentage"
| extend Disk = tostring(todynamic(Tags)["vm.azm.ms/mountId"])
| summarize FreePct = round(min(Val), 1) by Computer, Disk
| where FreePct < 15
| order by FreePct asc

Tip

To predict when a disk will fill instead of alerting at a fixed threshold, use series_fit_line() on the last 7 days of free space.

#Errors in the guest OS

// Windows
Event
| where TimeGenerated > ago(24h) and EventLevelName == "Error"
| summarize Count = count(), Sample = any(RenderedDescription) by Computer, Source, EventID
| top 20 by Count desc

// Linux
Syslog
| where TimeGenerated > ago(24h) and SeverityLevel in ("emerg", "alert", "crit", "err")
| summarize Count = count(), Sample = any(SyslogMessage) by Computer, Facility, ProcessName
| top 20 by Count desc

#Who changed it?

AzureActivity
| where TimeGenerated > ago(7d)
| where OperationNameValue has_any ("MICROSOFT.COMPUTE/VIRTUALMACHINES/WRITE", "DEALLOCATE", "RESTART", "DELETE")
| where ActivityStatusValue == "Success"
| project TimeGenerated, Caller, OperationNameValue, _ResourceId
| order by TimeGenerated desc

#Turning a query into an alert

1. Run the query in Log Analytics and check that it returns rows only when something is wrong.
2. Select New alert rule. Set a frequency of 5 minutes, Number of results > 0, and split by Computer so each VM gets its own alert.
3. Attach an action group, and put the KQL in the alert description so whoever is on call can rerun it.

Note

Log alerts are billed per rule and evaluation frequency. For simple numeric thresholds such as CPU, metric alerts are cheaper and fire faster.