Advanced Resource Graph 查询示例Advanced Resource Graph query samples

了解使用 Azure 资源图表进行查询的第一步是对查询语言有基本的了解。The first step to understanding queries with Azure Resource Graph is a basic understanding of the Query Language. 如果还不熟悉 Azure 数据资源管理器,建议查看基础知识,以了解如何撰写所需资源的请求。If you aren't already familiar with Azure Data Explorer, it's recommended to review the basics to understand how to compose requests for the resources you're looking for.

我们将逐步介绍以下高级查询:We'll walk through the following advanced queries:

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

语言支持Language support

Azure CLI(通过扩展)和 Azure PowerShell(通过模块)支持 Azure 资源图表。Azure CLI (through an extension) and Azure PowerShell (through a module) support Azure Resource Graph. 在运行以下任何查询之前,请检查环境是否已准备就绪。Before running any of the following queries, check that your environment is ready. 有关安装和验证所选 shell 环境的步骤,请参阅 Azure CLIAzure PowerShellSee Azure CLI and Azure PowerShell for steps to install and validate your shell environment of choice.

显示资源类型和 API 版本Show resource types and API versions

在更新过程中,Resource Graph 主要使用资源提供程序的最新非预览版 API 来 GET 资源属性。Resource Graph primarily uses the most recent non-preview version of a Resource Provider's API to GET resource properties during an update. 在某些情况下,会覆盖所使用的 API 版本,以便在结果中提供更多当前或广泛使用的属性。In some cases, the API version used has been overridden to provide more current or widely used properties in the results. 以下查询详述了用于在每个资源类型上收集属性的 API 版本:The following query details the API version used for gathering properties on each resource type:

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"

获取虚拟机规模集容量和大小Get virtual machine scale set capacity and size

此查询将查找虚拟机规模集资源,并获取各种详细信息,包括规模集的虚拟机大小和容量。This query looks for virtual machine scale set resources and gets various details including the virtual machine size and the capacity of the scale set. 此查询使用 toint() 函数将容量强制转换为数字以供排序。The query uses the toint() function to cast the capacity to a number so that it can be sorted. 最后会将列重命名为自定义命名属性。Finally, the columns are renamed into custom named properties.

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"

删除结果中的列Remove columns from results

以下查询使用 summarize 按订阅对资源进行计数,使用 join 将其与 ResourceContainers 表中的订阅详细信息合并,然后使用 project-away 删除某些列。The following query uses summarize to count resources by subscription, join to combine it with subscription details from ResourceContainers table, then project-away to remove some of the columns.

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"

列出所有标记名称List all tag names

此查询以标记开头,并生成一个 JSON 对象,列出所有唯一标记名称及其对应的类型。This query starts with the tag and builds a JSON object listing all unique tag names and their corresponding types.

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

由正则表达式匹配的虚拟机Virtual machines matched by regex

此查询查找与某个正则表达式(称为 regex)匹配的虚拟机。This query looks for virtual machines that match a regular expression (known as regex). 可以使用 matches regex @ 定义要匹配的正则表达式,即 ^Contoso(.*)[0-9]+$The matches regex @ allows us to define the regex to match, which is ^Contoso(.*)[0-9]+$. 该 regex 定义说明如下:That regex definition is explained as:

  • ^ - 匹配项必须以该字符串的开头开头。^ - Match must start at the beginning of the string.
  • Contoso - 区分大小写的字符串。Contoso - The case-sensitive string.
  • (.*) - 子表达式匹配项:(.*) - A subexpression match:
    • . - 匹配任何单一字符(换行符除外)。. - Matches any single character (except a new line).
    • * - 匹配上一个元素零次或多次。* - Matches previous element zero or more times.
  • [0-9] - 数字 0 到 9 的字符组匹配项。[0-9] - Character group match for numbers 0 through 9.
  • + - 匹配上一个元素一次或多次。+ - Matches previous element one or more times.
  • $ - 上一个元素的匹配项必须出现在该字符串的末尾。$ - Match of the previous element must occur at the end of the string.

按名称进行匹配后,查询将对名称进行投影并按名称升序排序。After matching by name, the query projects the name and orders by name ascending.

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 DBList Cosmos DB with specific write locations

以下查询限制为 Cosmos DB 资源,使用 mv-expand 扩展了 properties.writeLocations 的属性包,然后投影了特定字段并将结果进一步限制为 properties.writeLocations.locationName 值(与“中国东部”或“中国北部”匹配)。The following query limits to Cosmos DB resources, uses mv-expand to expand the property bag for properties.writeLocations, then project specific fields and limit the results further to properties.writeLocations.locationName values matching either 'China East' or 'China North'.

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"

具有订阅名称的密钥保管库Key vaults with subscription name

以下查询演示了 join(“类型”为“leftouter”)的复杂用法。The following query shows a complex use of join with kind as leftouter. 查询将联接表限制为订阅资源并具有 project,以仅包括原始字段 SubscriptionId 和重命名为 SubNamename 字段。The query limits the joined table to subscriptions resources and with project to include only the original field subscriptionId and the name field renamed to SubName. 字段重命名避免了 join 将其添加为 name1,因为该字段已存在于_资源_中。The field rename avoids join adding it as name1 since the field already exists in resources. 原始表使用 where 进行筛选,以下 project 包括两个表中的列。The original table is filtered with where and the following project includes columns from both tables. 查询结果是所有密钥保管库,其中显示密钥保管库的类型、名称以及其所在订阅的名称。The query result is all key vaults displaying type, the name of the key vault, and the name of the subscription it's in.

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 数据库及其弹性池List SQL Databases and their elastic pools

以下查询使用 leftouter join 将 SQL 数据库资源及其相关弹性池组合在一起(如果有)。The following query uses leftouter join to bring together SQL Database resources and their related elastic pools, if they've any.

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 的虚拟机List virtual machines with their network interface and public IP

此查询使用两个 leftouter join 命令将使用资源管理器部署模型创建的虚拟机、其相关网络接口以及与这些网络接口相关的任何公共 IP 地址组合在一起。This query uses two leftouter join commands to bring together virtual machines created with the Resource Manager deployment model, their related network interfaces, and any public IP address related to those network interfaces.

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"

列出虚拟机上安装的所有扩展List all extensions installed on a virtual machine

首先,此查询针对虚拟机资源类型使用 extend 以获取大写的 (toupper()) ID、获取操作系统名称和类型,以及获取虚拟机大小。First, this query uses extend on the virtual machines resource type to get the ID in uppercase (toupper()) the ID, get the operating system name and type, and get the virtual machine size. 获取大写的资源 ID 是准备联接另一个属性的好方法。Getting the resource ID in upper case is a good way to prepare to join to another property. 然后,此查询将 join 用于为 leftouter 的 kind,通过匹配扩展 ID 的大写 substring 来获取虚拟机扩展。Then, the query uses join with kind as leftouter to get virtual machine extensions by matching an upper cased substring of the extension ID. “/extensions/<ExtensionName>”之前的 ID 部分与虚拟机 ID 的格式相同,因此我们将此属性用于 joinThe portion of the ID before "/extensions/<ExtensionName>" is the same format as the virtual machines ID, so we use this property for the join. 然后会针对虚拟机扩展的名称将 summarize 用于 make_list 以合并每个扩展的名称,其中 id、OSName、OSType和 VMSize 在单个数组属性中是相同的 。summarize is then used with make_list on the name of the virtual machine extension to combine the name of each extension where id, OSName, OSType, and VMSize are the same into a single array property. 最后,我们通过 asc 对小写的 OSName 进行 order by 操作。Lastly, we order by the lower cased OSName with asc. 默认情况下,order by 为降序。By default, order by is descending.

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"

在资源组上查找具有特定标记的存储帐户Find storage accounts with a specific tag on the resource group

以下查询使用 inner join 将存储帐户连接到具有指定标记名称和标记值(区分大小写)的资源组。The following query uses an inner join to connect storage accounts with resource groups that have a specified case-sensitive tag name and tag value.

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 参数配合使用。If it's necessary to look for a case insensitive tag name and tag value, use mv-expand with the bagexpansion parameter. 此查询使用的配额高于前一查询,因此只在必要的时候使用 mv-expandThis query uses more quota than the previous query, so use mv-expand only if necessary.

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"

将两个查询的结果合并为单个结果Combine results from two queries into a single result

以下查询使用 unionResourceContainers 表中获取结果,并将它们添加到 Resources 表中的结果。The following query uses union to get results from the ResourceContainers table and add them to results from the Resources table.

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)"

按电源状态扩展属性汇总虚拟机Summarize virtual machine by the power states extended property

此查询使用虚拟机上的扩展属性按电源状态进行汇总。This query uses the extended properties on virtual machines to summarize by power states.

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)"

使用 DisplayNames 包括租户和订阅名称Include the tenant and subscription names with DisplayNames

此查询使用 Include 参数和选项 DisplayNames 将 subscriptionDisplayName 和 tenantDisplayName 添加到结果中 。This query uses the Include parameter with option DisplayNames to add subscriptionDisplayName and tenantDisplayName to the results. 此参数仅可用于 Azure CLI 和 Azure PowerShell。This parameter is only available for Azure CLI and Azure PowerShell.

az graph query -q "limit 1" --include displayNames
Search-AzGraph -Query "limit 1" -Include DisplayNames

获取订阅名称的替代方法是使用 join 运算符并连接到 ResourceContainers 表和 Microsoft.Resources/subscriptions 类型。An alternative to getting the subscription name is to use the join operator and connect to the ResourceContainers table and the Microsoft.Resources/subscriptions type. join 在 Azure CLI、Azure PowerShell、门户和所有受支持的 SDK 中都可以工作。join works in Azure CLI, Azure PowerShell, portal, and all supported SDK. 有关示例,请参阅示例 - 带订阅名称的密钥保管库For an example, see Sample - Key vault with subscription name.

备注

如果查询未使用 project 指定返回的属性,则 subscriptionDisplayNametenantDisplayName 将自动包括在结果中。If the query doesn't use project to specify the returned properties, subscriptionDisplayName and tenantDisplayName are automatically included in the results. 如果查询确实使用了 project,则每个 DisplayName 字段必须显式包含在 project 中,否则它们将不会在结果中返回,即使使用了 Include 参数也是如此。If the query does use project, each of the DisplayName fields must be explicitly included in the project or they won't be returned in the results, even when the Include parameter is used. Include 参数对不起作用。The Include parameter doesn't work with tables.


不合规来宾配置分配计数Count of non-compliant Guest Configuration assignments

显示每个来宾配置分配原因的不合规计算机的计数。Displays a count of non-compliant machines per Guest Configuration assignment reason. 为了提高性能,将结果限制为前 100 个。Limits results to first 100 for performance.

GuestConfigurationResources
| extend vmid = split(properties.targetResourceId,'/')
| where properties.complianceStatus == 'NonCompliant'
| mvexpand properties.latestAssignmentReport.resources
| mvexpand properties_latestAssignmentReport_resources.reasons
| project machine = tostring(vmid[(-1)]),
    type = tostring(vmid[(-3)]),
    name,
    status = tostring(properties.complianceStatus),
    resource = tostring(properties_latestAssignmentReport_resources.resourceId),
    phrase = tostring(properties_latestAssignmentReport_resources_reasons.phrase)
| summarize count() by resource, name
| order by count_
| limit 100
az graph query -q "GuestConfigurationResources | extend vmid = split(properties.targetResourceId,'/') | where properties.complianceStatus == 'NonCompliant' | mvexpand properties.latestAssignmentReport.resources | mvexpand properties_latestAssignmentReport_resources.reasons | project machine = tostring(vmid[(-1)]), type = tostring(vmid[(-3)]), name, status = tostring(properties.complianceStatus), resource = tostring(properties_latestAssignmentReport_resources.resourceId), phrase = tostring(properties_latestAssignmentReport_resources_reasons.phrase) | summarize count() by resource, name | order by count_ | limit 100"

查询来宾配置分配报表的详细信息Query details of Guest Configuration assignment reports

显示来宾配置分配原因详细信息的报表。Display report from Guest Configuration assignment reason details. 在以下示例中,查询仅返回来宾分配名称为 installed_application_linux 并且输出包含字符串 Python 的结果,以列出安装了包含名称 Python 的包的所有 Linux 计算机。In the example below, the query returns only results where the Guest Assignment name is installed_application_linux and the output contains the string Python to list all Linux machines where a package is installed that includes the name Python. 若要查询所有计算机是否符合特定分配,请删除第二个 where 子句。To query compliance of all machines for a specific assignment, remove the 2nd where clause.

GuestConfigurationResources
| extend vmid = split(properties.targetResourceId,'/')
| mvexpand properties.latestAssignmentReport.resources
| mvexpand properties_latestAssignmentReport_resources.reasons
| where name in ('installed_application_linux')
| where properties_latestAssignmentReport_resources_reasons.phrase contains 'Python'
| project machine = tostring(vmid[(-1)]),
    type = tostring(vmid[(-3)]),
    name,
    status = tostring(properties.complianceStatus),
    resource = tostring(properties_latestAssignmentReport_resources.resourceId),
    phrase = tostring(properties_latestAssignmentReport_resources_reasons.phrase)
az graph query -q "GuestConfigurationResources | extend vmid = split(properties.targetResourceId,'/') | mvexpand properties.latestAssignmentReport.resources | mvexpand properties_latestAssignmentReport_resources.reasons | where name in ('installed_application_linux') | where properties_latestAssignmentReport_resources_reasons.phrase contains 'Python' | project machine = tostring(vmid[(-1)]), type = tostring(vmid[(-3)]), name, status = tostring(properties.complianceStatus), resource = tostring(properties_latestAssignmentReport_resources.resourceId), phrase = tostring (properties_latestAssignmentReport_resources_reasons.phrase)"

查找计算机不符合来宾配置分配的所有原因Find all reasons a machine is non-compliant for Guest Configuration assignments

显示特定计算机的所有来宾配置分配原因Display all Guest Configuration assignment reasons for a specific machine. 删除第一个 where 子句,以便还包含计算机合规的审核。Remove the first where clause to also include audits where the machine is compliant.

GuestConfigurationResources
| where properties.complianceStatus == 'NonCompliant'
| extend vmid = split(properties.targetResourceId,'/')
| mvexpand properties.latestAssignmentReport.resources
| mvexpand properties_latestAssignmentReport_resources.reasons
| extend machine = tostring(vmid[(-1)])
| where machine == 'MACHINENAME'
| project phrase = tostring(properties_latestAssignmentReport_resources_reasons.phrase),
    resource = tostring(properties_latestAssignmentReport_resources.resourceId),
    name,
    machine,
    resourceGroup,
    subscriptionId
az graph query -q "GuestConfigurationResources | where properties.complianceStatus == 'NonCompliant' | extend vmid = split(properties.targetResourceId,'/') | mvexpand properties.latestAssignmentReport.resources | mvexpand properties_latestAssignmentReport_resources.reasons | extend machine = tostring(vmid[(-1)]) | where machine == 'MACHINENAME' | project phrase = tostring(properties_latestAssignmentReport_resources_reasons.phrase), resource = tostring(properties_latestAssignmentReport_resources.resourceId), name, machine, resourceGroup, subscriptionId"