Advanced Resource Graph queries

The first step to understanding queries with Azure Resource Graph is a basic understanding of the Query Language. 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:

If you don't have an Azure subscription, create a free account before you begin.

Language support

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. See Azure CLI and Azure PowerShell for steps to install and validate your shell environment of choice.

Show resource types and API versions

Resource Graph primarily uses the most recent non-preview version of a Resource Provider's API to GET resource properties during an update. In some cases, the API version used has been overridden to provide more current or widely used properties in the results. 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. 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

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

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

This query looks for virtual machines that match a regular expression (known as regex). The matches regex @ allows us to define the regex to match, which is ^Contoso(.*)[0-9]+$. That regex definition is explained as:

  • ^ - Match must start at the beginning of the string.
  • 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] - 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"

List Cosmos DB with specific write locations

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 'East US' or 'West US'.

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 ('East US', 'West US')
| 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 ('East US', 'West US') | summarize by id, name"

Key vault with subscription name

The following query shows a complex use of join. 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. The field rename avoids join adding it as name1 since the field already exists in resources. The original table is filtered with where and the following project includes columns from both tables. The query result is a single key vault displaying type, the name of the key vault, and the name of the subscription it's in.

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

List SQL Databases and their elastic pools

The following query uses leftouter join to bring together SQL Database resources and their related elastic pools, if they have 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"

List virtual machines with their network interface and public IP

This query uses two leftouter join commands to bring together virtual machines, 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) 
| mvexpand 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) 
    | mvexpand 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) | mvexpand 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) | mvexpand 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"

Find storage accounts with a specific tag on the resource group

The following query uses an inner join to connect storage accounts with resource groups that have a specified 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"

Combine results from two queries into a single result

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

Include the tenant and subscription names with DisplayNames

This query uses the new Include parameter with option DisplayNames to add subscriptionDisplayName and tenantDisplayName to the results. 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

Note

If the query doesn't use project to specify the returned properties, subscriptionDisplayName and tenantDisplayName are automatically included in the results. 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.

Next steps