Advanced Resource Graph 查询示例

了解使用 Azure 资源图表进行查询的第一步是对查询语言有基本的了解。 如果还不熟悉 Azure 数据资源管理器,建议查看基础知识,以了解如何撰写所需资源的请求。

我们将逐步介绍以下高级查询:

如果没有 Azure 订阅,请在开始前创建一个试用版订阅

语言支持

Azure CLI(通过扩展)和 Azure PowerShell(通过模块)支持 Azure 资源图表。 在运行以下任何查询之前,请检查环境是否已准备就绪。 有关安装和验证所选 shell 环境的步骤,请参阅 Azure CLIAzure PowerShell

显示资源类型和 API 版本

在更新过程中,Resource Graph 主要使用资源提供程序的最新非预览版 API 来 GET 资源属性。 在某些情况下,会覆盖所使用的 API 版本,以便在结果中提供更多当前或广泛使用的属性。 以下查询详述了用于在每个资源类型上收集属性的 API 版本:

Resources
| distinct type, apiVersion
| where isnotnull(apiVersion)
| order by type asc
az graph query -q "Resources | distinct type, apiVersion | where isnotnull(apiVersion) | order by type asc"

获取虚拟机规模集容量和大小

此查询将查找虚拟机规模集资源,并获取各种详细信息,包括规模集的虚拟机大小和容量。 此查询使用 toint() 函数将容量强制转换为数字以供排序。 最后会将列重命名为自定义命名属性。

Resources
| where type=~ 'microsoft.compute/virtualmachinescalesets'
| where name contains 'contoso'
| project subscriptionId, name, location, resourceGroup, Capacity = toint(sku.capacity), Tier = sku.name
| order by Capacity desc
az graph query -q "Resources | where type=~ 'microsoft.compute/virtualmachinescalesets' | where name contains 'contoso' | project subscriptionId, name, location, resourceGroup, Capacity = toint(sku.capacity), Tier = sku.name | order by Capacity desc"

删除结果中的列

以下查询使用 summarize 按订阅对资源进行计数,使用 join 将其与 ResourceContainers 表中的订阅详细信息合并,然后使用 project-away 删除某些列。

Resources
| summarize resourceCount=count() by subscriptionId
| join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
| project-away subscriptionId, subscriptionId1
az graph query -q "Resources | summarize resourceCount=count() by subscriptionId | join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId| project-away subscriptionId, subscriptionId1"

列出所有标记名称

此查询以标记开头,并生成一个 JSON 对象,列出所有唯一标记名称及其对应的类型。

Resources
| project tags
| summarize buildschema(tags)
az graph query -q "Resources | project tags | summarize buildschema(tags)"

由正则表达式匹配的虚拟机

此查询查找与某个 正则表达式(称为 regex)匹配的虚拟机。 可以使用 matches regex @ 定义要匹配的正则表达式,即 ^Contoso(.*)[0-9]+$。 该 regex 定义说明如下:

  • ^ - 匹配项必须以该字符串的开头开头。
  • Contoso - 区分大小写的字符串。
  • (.*) - 子表达式匹配项:
    • . - 匹配任何单一字符(换行符除外)。
    • * - 匹配上一个元素零次或多次。
  • [0-9] - 数字 0 到 9 的字符组匹配项。
  • + - 匹配上一个元素一次或多次。
  • $ - 上一个元素的匹配项必须出现在该字符串的末尾。

按名称进行匹配后,查询将对名称进行投影并按名称升序排序。

Resources
| where type =~ 'microsoft.compute/virtualmachines' and name matches regex @'^Contoso(.*)[0-9]+$'
| project name
| order by name asc
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' and name matches regex @'^Contoso(.*)[0-9]+$' | project name | order by name asc"

列出具有特定写入位置的 Cosmos DB

以下查询限制为 Azure Cosmos DB 资源,使用 mv-expand 扩展了 properties.writeLocations 的属性包,然后投影了特定字段并将结果进一步限制为 properties.writeLocations.locationName 值(与“中国东部”或“中国北部”匹配) 。

Resources
| where type =~ 'microsoft.documentdb/databaseaccounts'
| project id, name, writeLocations = (properties.writeLocations)
| mv-expand writeLocations
| project id, name, writeLocation = tostring(writeLocations.locationName)
| where writeLocation in ('China East', 'China North')
| summarize by id, name
az graph query -q "Resources | where type =~ 'microsoft.documentdb/databaseaccounts' | project id, name, writeLocations = (properties.writeLocations) | mv-expand writeLocations | project id, name, writeLocation = tostring(writeLocations.locationName) | where writeLocation in ('China East', 'China North') | summarize by id, name"

具有订阅名称的密钥保管库

以下查询演示了 join(“类型”为“leftouter”)的复杂用法。 查询将联接表限制为订阅资源并具有 project,以仅包括原始字段 SubscriptionId 和重命名为 SubNamename 字段。 字段重命名避免了 join 将其添加为 name1,因为该字段已存在于 资源 中。 原始表使用 where 进行筛选,以下 project 包括两个表中的列。 查询结果是所有密钥保管库,其中显示密钥保管库的类型、名称以及其所在订阅的名称。

Resources
| join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
| where type == 'microsoft.keyvault/vaults'
| project type, name, SubName
az graph query -q "Resources | join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId | where type == 'microsoft.keyvault/vaults' | project type, name, SubName"

列出 SQL 数据库及其弹性池

以下查询使用 leftouter join 将 SQL 数据库资源及其相关弹性池组合在一起(如果有)。

Resources
| where type =~ 'microsoft.sql/servers/databases'
| project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId))
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.sql/servers/elasticpools'
    | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state)
on elasticPoolId
| project-away elasticPoolId1
az graph query -q "Resources | where type =~ 'microsoft.sql/servers/databases' | project databaseId = id, databaseName = name, elasticPoolId = tolower(tostring(properties.elasticPoolId)) | join kind=leftouter ( Resources | where type =~ 'microsoft.sql/servers/elasticpools' | project elasticPoolId = tolower(id), elasticPoolName = name, elasticPoolState = properties.state) on elasticPoolId | project-away elasticPoolId1"

列出具有其网络接口和公共 IP 的虚拟机

此查询使用两个 leftouter join 命令将使用资源管理器部署模型创建的虚拟机、其相关网络接口以及与这些网络接口相关的任何公共 IP 地址组合在一起。

Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic)
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | extend ipConfigsCount=array_length(properties.ipConfigurations)
    | mv-expand ipconfig=properties.ipConfigurations
    | where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true'
    | project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id))
on nicId
| project-away nicId1
| summarize by vmId, vmName, vmSize, nicId, publicIpId
| join kind=leftouter (
    Resources
    | where type =~ 'microsoft.network/publicipaddresses'
    | project publicIpId = id, publicIpAddress = properties.ipAddress)
on publicIpId
| project-away publicIpId1
az graph query -q "Resources | where type =~ 'microsoft.compute/virtualmachines' | extend nics=array_length(properties.networkProfile.networkInterfaces) | mv-expand nic=properties.networkProfile.networkInterfaces | where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic) | project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id) | join kind=leftouter ( Resources | where type =~ 'microsoft.network/networkinterfaces' | extend ipConfigsCount=array_length(properties.ipConfigurations) | mv-expand ipconfig=properties.ipConfigurations | where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true' | project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id)) on nicId | project-away nicId1 | summarize by vmId, vmName, vmSize, nicId, publicIpId | join kind=leftouter ( Resources | where type =~ 'microsoft.network/publicipaddresses' | project publicIpId = id, publicIpAddress = properties.ipAddress) on publicIpId | project-away publicIpId1"

列出虚拟机上安装的所有扩展

首先,此查询针对虚拟机资源类型使用 extend 以获取大写的 (toupper()) ID、获取操作系统名称和类型,以及获取虚拟机大小。 获取大写的资源 ID 是准备联接另一个属性的好方法。 然后,此查询将 join 用于为 leftouter 的 kind,通过匹配扩展 ID 的大写 substring 来获取虚拟机扩展。 “/extensions/<ExtensionName>”之前的 ID 部分与虚拟机 ID 的格式相同,因此我们将此属性用于 join。 然后会针对虚拟机扩展的名称将 summarize 用于 make_list 以合并每个扩展的名称,其中 id、OSName、OSType和 VMSize 在单个数组属性中是相同的 。 最后,我们通过 asc 对小写的 OSName 进行 order by 操作。 默认情况下,order by 为降序。

Resources
| where type == 'microsoft.compute/virtualmachines'
| extend
    JoinID = toupper(id),
    OSName = tostring(properties.osProfile.computerName),
    OSType = tostring(properties.storageProfile.osDisk.osType),
    VMSize = tostring(properties.hardwareProfile.vmSize)
| join kind=leftouter(
    Resources
    | where type == 'microsoft.compute/virtualmachines/extensions'
    | extend
        VMId = toupper(substring(id, 0, indexof(id, '/extensions'))),
        ExtensionName = name
) on $left.JoinID == $right.VMId
| summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize
| order by tolower(OSName) asc
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | extend JoinID = toupper(id), OSName = tostring(properties.osProfile.computerName), OSType = tostring(properties.storageProfile.osDisk.osType), VMSize = tostring(properties.hardwareProfile.vmSize) | join kind=leftouter( Resources | where type == 'microsoft.compute/virtualmachines/extensions' | extend VMId = toupper(substring(id, 0, indexof(id, '/extensions'))), ExtensionName = name ) on $left.JoinID == $right.VMId | summarize Extensions = make_list(ExtensionName) by id, OSName, OSType, VMSize | order by tolower(OSName) asc"

在资源组上查找具有特定标记的存储帐户

以下查询使用 inner join 将存储帐户连接到具有指定标记名称和标记值(区分大小写)的资源组。

Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
    ResourceContainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | where tags['Key1'] =~ 'Value1'
    | project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | where tags['Key1'] =~ 'Value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"

如果必须查找不区分大小写的标记名称和标记值,请将 mv-expandbagexpansion 参数配合使用。 此查询使用的配额高于前一查询,因此只在必要的时候使用 mv-expand

Resources
| where type =~ 'microsoft.storage/storageaccounts'
| join kind=inner (
    ResourceContainers
    | where type =~ 'microsoft.resources/subscriptions/resourcegroups'
    | mv-expand bagexpansion=array tags
    | where isnotempty(tags)
    | where tags[0] =~ 'key1' and tags[1] =~ 'value1'
    | project subscriptionId, resourceGroup)
on subscriptionId, resourceGroup
| project-away subscriptionId1, resourceGroup1
az graph query -q "Resources | where type =~ 'microsoft.storage/storageaccounts' | join kind=inner ( ResourceContainers | where type =~ 'microsoft.resources/subscriptions/resourcegroups' | mv-expand bagexpansion=array tags | where isnotempty(tags) | where tags[0] =~ 'key1' and tags[1] =~ 'value1' | project subscriptionId, resourceGroup) on subscriptionId, resourceGroup | project-away subscriptionId1, resourceGroup1"

将两个查询的结果合并为单个结果

以下查询使用 unionResourceContainers 表中获取结果,并将它们添加到 Resources 表中的结果。

ResourceContainers
| where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type  | limit 5
| union  (Resources | project name, type | limit 5)
az graph query -q "ResourceContainers | where type=='microsoft.resources/subscriptions/resourcegroups' | project name, type  | limit 5 | union  (Resources | project name, type | limit 5)"

获取网络接口的虚拟网络和子网

使用正则表达式 parse 从资源 ID 属性中获取虚拟网络和子网名称。 虽然 parse 支持从复杂字段获取数据,但如果属性存在,则最好直接访问属性,而不是使用 parse

Resources
| where type =~ 'microsoft.network/networkinterfaces'
| project id, ipConfigurations = properties.ipConfigurations
| mvexpand ipConfigurations
| project id, subnetId = tostring(ipConfigurations.properties.subnet.id)
| parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet 
| project id, virtualNetwork, subnet
az graph query -q "Resources | where type =~ 'microsoft.network/networkinterfaces' | project id, ipConfigurations = properties.ipConfigurations | mvexpand ipConfigurations | project id, subnetId = tostring(ipConfigurations.properties.subnet.id) | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet | project id, virtualNetwork, subnet"

按电源状态扩展属性汇总虚拟机

此查询使用虚拟机上的扩展属性按电源状态进行汇总。

Resources
| where type == 'microsoft.compute/virtualmachines'
| summarize count() by tostring(properties.extended.instanceView.powerState.code)
az graph query -q "Resources | where type == 'microsoft.compute/virtualmachines' | summarize count() by tostring(properties.extended.instanceView.powerState.code)"

后续步骤