如何获取Azure虚拟机Insights性能数据（含分位数）并导出CSV？

核心问题原因 #

你之前使用的标准Azure Metrics API/CLI/SDK获取的是平台级基础指标（如Percentage CPU），而VM Insights中展示的CPU Utilization等是基于Guest OS性能计数器的数据，这类数据存储在关联的Log Analytics工作区的Perf表中，无法通过普通Metrics接口获取。

解决方案：使用Log Analytics查询接口 #

要获取带Avg/Min/Max/50th/90th/95th聚合值的VM Insights性能数据，需要通过Log Analytics的查询API或SDK执行Kusto查询，从Perf表中提取数据，再导出为CSV。

1. 确认前提 #

• 目标VM已启用VM Insights，并关联了Log Analytics工作区
• 用于查询的身份（如服务主体）拥有该工作区的Log Analytics Reader权限

2. 关键指标对应关系 #

VM Insights的核心性能指标对应Perf表中的字段：

• CPU Utilization：ObjectName = "Processor"，CounterName = "% Processor Time"
• Available Memory：ObjectName = "Memory"，CounterName = "Available MBytes"
• Logical Disk MB/s：ObjectName = "LogicalDisk"，CounterName = "Disk Read Bytes/sec"/Disk Write Bytes/sec（需除以1048576转换为MB/s）
• Logical Disk IOPS：ObjectName = "LogicalDisk"，CounterName = "Disk Reads/sec"/Disk Writes/sec

3. 示例Kusto查询 #

以下查询按小时聚合所有目标指标的所需统计值：

Perf
| where Computer == "YOUR_VM_NAME"
| where ObjectName in ("Processor", "Memory", "LogicalDisk")
| where CounterName in (
    "% Processor Time", 
    "Available MBytes", 
    "Disk Read Bytes/sec", 
    "Disk Write Bytes/sec", 
    "Disk Reads/sec", 
    "Disk Writes/sec"
)
| where TimeGenerated between (datetime(2024-01-01T00:00:00Z) .. datetime(2024-01-02T00:00:00Z))
// 转换磁盘字节数为MB/s
| extend AdjustedValue = iif(CounterName contains "Bytes/sec", CounterValue / 1048576, CounterValue)
| summarize
    Avg = avg(AdjustedValue),
    Min = min(AdjustedValue),
    Max = max(AdjustedValue),
    P50 = percentile(AdjustedValue, 50),
    P90 = percentile(AdjustedValue, 90),
    P95 = percentile(AdjustedValue, 95)
    by 
        MetricName = strcat(ObjectName, " - ", CounterName),
        TimeBucket = bin(TimeGenerated, 1h)

4. Python SDK实现（导出CSV） #

使用azure-loganalytics库执行查询并导出为CSV：

from azure.loganalytics import LogAnalyticsDataClient
from azure.loganalytics.models import QueryBody
from azure.common.credentials import ServicePrincipalCredentials
import pandas as pd

# 配置参数
TENANT_ID = "你的租户ID"
CLIENT_ID = "服务主体ID"
CLIENT_SECRET = "服务主体密钥"
WORKSPACE_ID = "Log Analytics工作区ID"
VM_NAME = "目标VM名称"
START_TIME = "2024-01-01T00:00:00Z"
END_TIME = "2024-01-02T00:00:00Z"

# 初始化认证客户端
credentials = ServicePrincipalCredentials(
    client_id=CLIENT_ID,
    secret=CLIENT_SECRET,
    tenant=TENANT_ID
)
la_client = LogAnalyticsDataClient(credentials)

# 构建查询语句
query = f"""
Perf
| where Computer == "{VM_NAME}"
| where ObjectName in ("Processor", "Memory", "LogicalDisk")
| where CounterName in (
    "% Processor Time", 
    "Available MBytes", 
    "Disk Read Bytes/sec", 
    "Disk Write Bytes/sec", 
    "Disk Reads/sec", 
    "Disk Writes/sec"
)
| where TimeGenerated between (datetime({START_TIME}) .. datetime({END_TIME}))
| extend AdjustedValue = iif(CounterName contains "Bytes/sec", CounterValue / 1048576, CounterValue)
| summarize
    Avg = avg(AdjustedValue),
    Min = min(AdjustedValue),
    Max = max(AdjustedValue),
    P50 = percentile(AdjustedValue, 50),
    P90 = percentile(AdjustedValue, 90),
    P95 = percentile(AdjustedValue, 95)
    by 
        MetricName = strcat(ObjectName, " - ", CounterName),
        TimeBucket = bin(TimeGenerated, 1h)
"""

# 执行查询
response = la_client.query(WORKSPACE_ID, QueryBody(query=query))

# 转换为DataFrame并保存CSV
data_rows = []
for table in response.tables:
    col_names = [col.name for col in table.columns]
    for row in table.rows:
        data_rows.append(dict(zip(col_names, row)))

df = pd.DataFrame(data_rows)
df.to_csv("vm_insights_performance.csv", index=False)

5. 其他可选方式 #

• Log Analytics REST API：直接向https://api.loganalytics.io/v1/workspaces/{WORKSPACE_ID}/query发送POST请求，携带Kusto查询和认证信息
• Azure CLI：使用az monitor log-analytics query命令执行Kusto查询，再将结果导出为CSV

注意事项 #

• 如果需要更细粒度的时间间隔，修改bin(TimeGenerated, 1h)中的1h为5m等
• 分位数计算依赖足够的样本数据，若数据点过少结果可能不具备参考性
• 确保VM的Guest OS性能计数器已正确采集（VM Insights默认会配置采集规则）

内容的提问来源于stack exchange，提问作者Saiyam Jain