如何通过Azure Resource Graph查询数据库存储使用等监控数据？

我近期开始使用Azure Resource Graph查询并生成全环境下Azure数据库的报表，已从resources表中提取了大量有用数据，但我是KQL及Resource Graph的新手，不清楚从何处获取已用空间、剩余空间等监控数据。

我当前运行的查询语句如下：

resources
| where type =~ 'microsoft.sql/servers'
| project serverName = name, serverLocation = location, serverResourceGroup = resourceGroup, serverFQDN = properties.fullyQualifiedDomainName, serverLocalAdminAccount = properties.administratorLogin, serverAdminGroup = properties.administrators.login, serverTags = tags
| join (resources
| where type =~ 'microsoft.sql/servers/databases' and name != 'master'
| extend serverName = extract("servers/(.*)/databases", 1, id)
| project serverName, databaseName = name, databaseCreationDate = format_datetime(todatetime(properties.creationDate), 'yyyy-MM-dd HH:mm'), databaseStatus = properties.status, databaseBackupStorageRedundancy = properties.requestedBackupStorageRedundancy, databaseZoneRedundant = properties.zoneRedundant, databaseCollation = properties.collation, databaseEarliestRestoreDate = format_datetime(todatetime(properties.earliestRestoreDate), 'yyyy-MM-dd HH:mm'), elasticPoolId = tolower(properties.elasticPoolId)) on serverName
| project-away serverName1
| join kind=leftouter (resources
  | where type =~ 'microsoft.sql/servers/elasticpools'
  | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId
| project-away elasticPoolId, elasticPoolId1

原本我以为通过上述3种资源类型就能获取所需全部数据，但显然遗漏了某些内容，恳请指点。

• Azure Resource Graph的resources表只存储资源的静态属性，已用空间、剩余空间这类动态监控指标不在其中，需要从Azure Monitor的AzureMetrics表获取。
• 你可以通过以下步骤整合数据：
  1. 先从resources表抓取数据库、服务器、弹性池的基础信息，用数据库的资源ID作为关联键
  2. 关联AzureMetrics表，筛选对应的空间指标：
     • 数据库已用空间：storage_used（单位通常为MB）
     • 数据库总容量：storage_capacity
     • 剩余空间可通过storage_capacity - storage_used计算得出
  3. 由于AzureMetrics是时序数据，需要用summarize取最新值或指定时间段的统计值

示例整合查询：

// 获取数据库基础信息
let db_base = resources
| where type =~ 'microsoft.sql/servers/databases' and name != 'master'
| extend serverName = extract("servers/(.*)/databases", 1, id)
| project resourceId = id, serverName, databaseName = name, resourceGroup, location, elasticPoolId = tolower(properties.elasticPoolId);

// 获取服务器信息
let server_base = resources
| where type =~ 'microsoft.sql/servers'
| project serverName = name, serverFQDN = properties.fullyQualifiedDomainName, serverLocalAdminAccount = properties.administratorLogin, serverAdminGroup = properties.administrators.login, serverTags = tags;

// 获取弹性池信息
let pool_base = resources
| where type =~ 'microsoft.sql/servers/elasticpools'
| project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state;

// 获取数据库空间指标（取最近1小时的最新值）
let db_metrics = AzureMetrics
| where ResourceType == "MICROSOFT.SQL/SERVERS/DATABASES"
| where MetricName in ("storage_used", "storage_capacity")
| where TimeGenerated > ago(1h)
| summarize arg_max(TimeGenerated, Average) by resourceId, MetricName
| pivot(MetricName, any(Average))
| project resourceId, storageUsedMB = storage_used, storageCapacityMB = storage_capacity, remainingSpaceMB = storage_capacity - storage_used;

// 关联所有表
db_base
| join server_base on serverName
| join kind=leftouter pool_base on elasticPoolId
| join kind=leftouter db_metrics on resourceId
| project-away resourceId1, elasticPoolId, serverName1

• 额外注意：
  • 确保你的账号拥有Azure Monitor数据的读取权限
  • 若数据库属于弹性池，还可从AzureMetrics中获取弹性池的storage_used和storage_limit指标，查看池级的空间使用情况
  • 时序数据的时间范围可按需调整，比如用ago(24h)获取最近一天的数据，再通过summarize取平均值或最大值

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