Understanding the Azure Resource Graph query language

  • Article

The query language for the Azure Resource Graph supports many operators and functions. Each work and operate based on Kusto Query Language (KQL). To learn about the query language used by Resource Graph, start with the tutorial for KQL.

This article covers the language components supported by Resource Graph:

Resource Graph tables

Resource Graph provides several tables for the data it stores about Azure Resource Manager resource types and their properties. Resource Graph tables can be used with the join operator to get properties from related resource types.

Resource Graph tables support the join flavors:

Resource Graph table Can join other tables? Description
AdvisorResources Yes Includes resources related to Microsoft.Advisor.
AlertsManagementResources Yes Includes resources related to Microsoft.AlertsManagement.
DnsResources Yes Includes resources related to Microsoft.Network.
InsightsResources Yes Includes resources related to Microsoft.Insights.
KubernetesConfigurationResources Yes Includes resources related to Microsoft.KubernetesConfiguration.
MaintenanceResources Yes Includes resources related to Microsoft.Maintenance.
PatchAssessmentResources Yes Includes resources related to Azure Virtual Machines patch assessment Microsoft.Compute and Microsoft.HybridCompute.
PatchInstallationResources Yes Includes resources related to Azure Virtual Machines patch installation Microsoft.Compute and Microsoft.HybridCompute.
PolicyResources Yes Includes resources related to Microsoft.PolicyInsights.
RecoveryServicesResources Yes Includes resources related to Microsoft.DataProtection and Microsoft.RecoveryServices.
ResourceChanges Yes Includes resources related to Microsoft.Resources.
ResourceContainerChanges Yes Includes resources related to Microsoft.Resources.
ResourceContainers Yes Includes management group (Microsoft.Management/managementGroups), subscription (Microsoft.Resources/subscriptions) and resource group (Microsoft.Resources/subscriptions/resourcegroups) resource types and data.
SecurityResources Yes Includes resources related to Microsoft.Security.
ServiceHealthResources Yes Includes resources related to Microsoft.ResourceHealth/events.

For a list of tables that includes resource types, go to Azure Resource Graph table and resource type reference.

Note

Resources is the default table. While querying the Resources table, it isn't required to provide the table name unless join or union are used. But the recommended practice is to always include the initial table in the query.

To discover which resource types are available in each table, use Resource Graph Explorer in the portal. As an alternative, use a query such as <tableName> | distinct type to get a list of resource types the given Resource Graph table supports that exist in your environment.

The following query shows a simple join. The query result blends the columns together and any duplicate column names from the joined table, ResourceContainers in this example, are appended with 1. As ResourceContainers table has types for both subscriptions and resource groups, either type might be used to join to the resource from Resources table.

Resources
| join ResourceContainers on subscriptionId
| limit 1

The following query shows a more complex use of join. First, the query uses project to get the fields from Resources for the Azure Key Vault vaults resource type. The next step uses join to merge the results with ResourceContainers where the type is a subscription on a property that is both in the first table's project and the joined table's project. The field rename avoids join adding it as name1 since the property already is projected from Resources. The query result is a single key vault displaying type, the name, location, and resource group of the key vault, along with the name of the subscription it's in.

Resources
| where type == 'microsoft.keyvault/vaults'
| project name, type, location, subscriptionId, resourceGroup
| join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId
| project type, name, location, resourceGroup, SubName
| limit 1

Note

When limiting the join results with project, the property used by join to relate the two tables, subscriptionId in the above example, must be included in project.

Extended properties

As a preview feature, some of the resource types in Resource Graph have more type-related properties available to query beyond the properties provided by Azure Resource Manager. This set of values, known as extended properties, exists on a supported resource type in properties.extended. To show resource types with extended properties, use the following query:

Resources
| where isnotnull(properties.extended)
| distinct type
| order by type asc

Example: Get count of virtual machines by instanceView.powerState.code:

Resources
| where type == 'microsoft.compute/virtualmachines'
| summarize count() by tostring(properties.extended.instanceView.powerState.code)

Resource Graph custom language elements

Shared query syntax (preview)

As a preview feature, a shared query can be accessed directly in a Resource Graph query. This scenario makes it possible to create standard queries as shared queries and reuse them. To call a shared query inside a Resource Graph query, use the {{shared-query-uri}} syntax. The URI of the shared query is the Resource ID of the shared query on the Settings page for that query. In this example, our shared query URI is /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SharedQueries/providers/Microsoft.ResourceGraph/queries/Count VMs by OS. This URI points to the subscription, resource group, and full name of the shared query we want to reference in another query. This query is the same as the one created in Tutorial: Create and share a query.

Note

You can't save a query that references a shared query as a shared query.

Example 1: Use only the shared query:

The results of this Resource Graph query are the same as the query stored in the shared query.

{{/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SharedQueries/providers/Microsoft.ResourceGraph/queries/Count VMs by OS}}

Example 2: Include the shared query as part of a larger query:

This query first uses the shared query, and then uses limit to further restrict the results.

{{/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/SharedQueries/providers/Microsoft.ResourceGraph/queries/Count VMs by OS}}
| where properties_storageProfile_osDisk_osType =~ 'Windows'

Supported KQL language elements

Resource Graph supports a subset of KQL data types, scalar functions, scalar operators, and aggregation functions. Specific tabular operators are supported by Resource Graph, some of which have different behaviors.

Supported tabular/top level operators

Here's the list of KQL tabular operators supported by Resource Graph with specific samples:

KQL Resource Graph sample query Notes
count Count key vaults
distinct Show resources that contain storage
extend Count virtual machines by OS type
join Key vault with subscription name Join flavors supported: innerunique, inner, leftouter, and fullouter. Limit of three join in a single query, one of which might be a cross-table join. If all cross-table join use is between Resource and ResourceContainers, then three cross-table join are allowed. Custom join strategies, such as broadcast join, aren't allowed. For which tables can use join, go to Resource Graph tables.
limit List all public IP addresses Synonym of take. Doesn't work with Skip.
mvexpand Legacy operator, use mv-expand instead. RowLimit max of 2,000. The default is 128.
mv-expand List Azure Cosmos DB with specific write locations RowLimit max of 2,000. The default is 128. Limit of 3 mv-expand in a single query.
order List resources sorted by name Synonym of sort
parse Get virtual networks and subnets of network interfaces It's optimal to access properties directly if they exist instead of using parse.
project List resources sorted by name
project-away Remove columns from results
sort List resources sorted by name Synonym of order
summarize Count Azure resources Simplified first page only
take List all public IP addresses Synonym of limit. Doesn't work with Skip.
top Show first five virtual machines by name and their OS type
union Combine results from two queries into a single result Single table allowed: | union [kind= inner|outer] [withsource=ColumnName] Table. Limit of three union legs in a single query. Fuzzy resolution of union leg tables isn't allowed. Might be used within a single table or between the Resources and ResourceContainers tables.
where Show resources that contain storage

There's a default limit of three join and three mv-expand operators in a single Resource Graph SDK query. You can request an increase in these limits for your tenant through Help + support.

To support the Open Query portal experience, Azure Resource Graph Explorer has a higher global limit than Resource Graph SDK.

Note

You can't reference a table as right table multiple times, which exceeds the limit of 1. If you do so, you would receive an error with code DisallowedMaxNumberOfRemoteTables.

Query scope

The scope of the subscriptions or management groups from which resources are returned by a query defaults to a list of subscriptions based on the context of the authorized user. If a management group or a subscription list isn't defined, the query scope is all resources, and includes Azure Lighthouse delegated resources.

The list of subscriptions or management groups to query can be manually defined to change the scope of the results. For example, the REST API managementGroups property takes the management group ID, which is different from the name of the management group. When managementGroups is specified, resources from the first 10,000 subscriptions in or under the specified management group hierarchy are included. managementGroups can't be used at the same time as subscriptions.

Example: Query all resources within the hierarchy of the management group named My Management Group with ID myMG.

  • REST API URI

    POST https://management.chinacloudapi.cn/providers/Microsoft.ResourceGraph/resources?api-version=2021-03-01

  • Request Body

    {
  "query": "Resources | summarize count()",
  "managementGroups": ["myMG"]
}

The AuthorizationScopeFilter parameter enables you to list Azure Policy assignments and Azure role-based access control (Azure RBAC) role assignments in the AuthorizationResources table that are inherited from upper scopes. The AuthorizationScopeFilter parameter accepts the following values for the PolicyResources and AuthorizationResources tables:

  • AtScopeAndBelow (default if not specified): Returns assignments for the given scope and all child scopes.
  • AtScopeAndAbove: Returns assignments for the given scope and all parent scopes, but not child scopes.
  • AtScopeAboveAndBelow: Returns assignments for the given scope, all parent scopes, and all child scopes.
  • AtScopeExact: Returns assignments only for the given scope; no parent or child scopes are included.

Note

To use the AuthorizationScopeFilter parameter, be sure to use the 2021-06-01-preview or later API version in your requests.

Example: Get all policy assignments at the myMG management group and Tenant Root (parent) scopes.

  • REST API URI

    POST https://management.chinacloudapi.cn/providers/Microsoft.ResourceGraph/resources?api-version=2021-06-01-preview

  • Request Body Sample

    {
  "options": {
    "authorizationScopeFilter": "AtScopeAndAbove"
  },
  "query": "PolicyResources | where type =~ 'Microsoft.Authorization/PolicyAssignments'",
  "managementGroups": ["myMG"]
}

Example: Get all policy assignments at the mySubscriptionId subscription, management group, and Tenant Root scopes.

  • REST API URI

    POST https://management.chinacloudapi.cn/providers/Microsoft.ResourceGraph/resources?api-version=2021-06-01-preview

  • Request Body Sample

    {
  "options": {
    "authorizationScopeFilter": "AtScopeAndAbove"
  },
  "query": "PolicyResources | where type =~ 'Microsoft.Authorization/PolicyAssignments'",
  "subscriptions": ["mySubscriptionId"]
}

Escape characters

Some property names, such as those that include a . or $, must be wrapped or escaped in the query or the property name is interpreted incorrectly and doesn't provide the expected results.

  • Dot (.): Wrap the property name ['propertyname.withaperiod'] using brackets.

    Example query that wraps the property odata.type:

    where type=~'Microsoft.Insights/alertRules' | project name, properties.condition.['odata.type']

  • Dollar sign ($): Escape the character in the property name. The escape character used depends on the shell that runs Resource Graph.

    • Bash: Use a backslash (\) as the escape character.

      Example query that escapes the property $type in Bash:

      where type=~'Microsoft.Insights/alertRules' | project name, properties.condition.\$type

    • cmd: Don't escape the dollar sign ($) character.

    • PowerShell: Use a backtick (`) as the escape character.

      Example query that escapes the property $type in PowerShell:

      where type=~'Microsoft.Insights/alertRules' | project name, properties.condition.`$type

Next steps