Azure Log Analytics磁盘利用率查询合并求助

Hey there! Since you're new to Log Analytics and looking to build a query that combines disk metrics to show free space percentage, total capacity, VM names, disk details, and other useful usage info—here's a tailored solution that fits your needs.

The Query (Core Version) #

This query pulls from the Perf table (where Azure VM disk metrics are stored by default), combines total capacity and free space data, calculates key metrics, and formats the output for readability:

// Step 1: Grab total disk capacity (convert bytes to GB for clarity)
let disk_total = Perf
| where ObjectName == "LogicalDisk" 
  and CounterName == "Disk Bytes Total"
| project 
    VM_Name = Computer,
    Disk_Drive = InstanceName, // Shows drive letters like C:, D:, etc.
    Total_Capacity_GB = round(CounterValue / 1024 / 1024 / 1024, 2),
    Reading_Time = TimeGenerated;

// Step 2: Grab free disk space (same unit conversion)
let disk_free = Perf
| where ObjectName == "LogicalDisk" 
  and CounterName == "Disk Bytes Free"
| project 
    VM_Name = Computer,
    Disk_Drive = InstanceName,
    Free_Space_GB = round(CounterValue / 1024 / 1024 / 1024, 2),
    Reading_Time = TimeGenerated;

// Step 3: Combine datasets, calculate metrics, and clean up output
disk_total
| join kind=inner (disk_free) on VM_Name, Disk_Drive, Reading_Time
| extend 
    Free_Space_Percent = round((Free_Space_GB / Total_Capacity_GB) * 100, 2),
    Used_Space_GB = round(Total_Capacity_GB - Free_Space_GB, 2)
| project 
    VM_Name,
    Disk_Drive,
    Total_Capacity_GB,
    Used_Space_GB,
    Free_Space_GB,
    Free_Space_Percent,
    Reading_Time
| sort by Free_Space_Percent asc // Prioritize disks running low on space

Key Details & Explanations #

• Why Perf table? Azure VMs send disk utilization metrics here by default, assuming you have the Log Analytics agent installed on your VMs.
• Unit conversion: Raw metrics are stored in bytes, so we divide by 1024^3 to get gigabytes—far easier to interpret at a glance.
• Inner join: Ensures we only keep complete records where both total capacity and free space data exist (no partial entries).
• Added metrics: Included Used_Space_GB as a quick reference, and sorted results by free space percentage to immediately spot disks needing attention.

Optional: Add Azure Disk Names (Not Just Drive Letters) #

If you want to see the actual disk names from the Azure portal (instead of just drive letters), extend the query to join with the AzureResources table to map VMs to their managed disks:

// Get VM-to-disk mapping from Azure resources
let vm_disk_mapping = AzureResources
| where type == "microsoft.compute/virtualmachines"
// Include data disks
| mv-expand data_disks = properties.storageProfile.dataDisks
| project 
    VM_Name = name,
    Azure_Disk_Name = data_disks.name,
    Disk_LUN = data_disks.lun
// Union with OS disk data
| union (
    AzureResources
    | where type == "microsoft.compute/virtualmachines"
    | project 
        VM_Name = name,
        Azure_Disk_Name = properties.storageProfile.osDisk.name,
        Disk_LUN = -1 // OS disks typically use LUN -1
);

// Step 1 & 2: Same as core query (total capacity and free space)
let disk_total = Perf
| where ObjectName == "LogicalDisk" 
  and CounterName == "Disk Bytes Total"
| project 
    VM_Name = Computer,
    Disk_Drive = InstanceName,
    Total_Capacity_GB = round(CounterValue / 1024 / 1024 / 1024, 2),
    Reading_Time = TimeGenerated;

let disk_free = Perf
| where ObjectName == "LogicalDisk" 
  and CounterName == "Disk Bytes Free"
| project 
    VM_Name = Computer,
    Disk_Drive = InstanceName,
    Free_Space_GB = round(CounterValue / 1024 / 1024 / 1024, 2),
    Reading_Time = TimeGenerated;

// Combine all datasets
disk_total
| join kind=inner (disk_free) on VM_Name, Disk_Drive, Reading_Time
| join kind=leftouter (vm_disk_mapping) on VM_Name
| extend 
    Free_Space_Percent = round((Free_Space_GB / Total_Capacity_GB) * 100, 2),
    Used_Space_GB = round(Total_Capacity_GB - Free_Space_GB, 2)
| project 
    VM_Name,
    Azure_Disk_Name,
    Disk_Drive,
    Total_Capacity_GB,
    Used_Space_GB,
    Free_Space_GB,
    Free_Space_Percent,
    Reading_Time
| sort by Free_Space_Percent asc

Note: For this extended version to work, your Log Analytics workspace needs access to Azure resource data (via Azure Resource Graph integration), and there might be a slight delay between disk changes in Azure and the data appearing in Log Analytics.

Quick Tips for New Users #

• Test queries with a small time range first (e.g., last 1 hour) to speed up results.
• If you don't see data, double-check that the Log Analytics agent is installed and running on your VMs.
• Customize the project clause to add/remove fields based on your needs—just adjust the column names!

内容的提问来源于stack exchange，提问作者Norrin Rad