- Manage Azure PowerShell global settings - Fri, Sep 22 2023
- Create and manage append blobs with PowerShell - Wed, Oct 12 2022
- Permanently delete a Key Vault in Azure using PowerShell - Fri, Feb 4 2022
Azure Resource Graph and Kusto Query Language
Azure Resource Graph is a service that allows us to perform resource queries with advanced filtering across multiple subscriptions using Resource Graph tables. Kusto Query Language (KQL) is the query language that Resource Graph uses to return the requested data. KQL supports many operators, including join and union, which enable cross-table references to return more detailed results from multiple tables. Azure Resource Graph queries can be run either in Azure Portal or via PowerShell. To run queries in Azure Portal, Azure Resource Graph Explorer can be used, which has a user interface where you can find supported Resource Graph tables that you can use in your queries. You can also save your queries and reuse them when needed.
Resource Graph Explorer in Azure Portal can be used to execute queries
The above example lists all resources with their default details, such as ID, name, subscriptionId, resourceGroup, etc.
PowerShell Resource Graph module
Az.ResourceGraph is the module that can be used in PowerShell to run Resource Graph queries against Azure resources across the entire tenant or set of subscriptions. A very simple query that can be used with PowerShell is as follows:
$KustoQuery = " resources | where name starts with 'Network' " $result = Search-AzGraph -Query $KustoQuery $result | select name
A simple Kusto query to list all resources
The above query will list all resources with resource names starting with "Network."
Limitations
It's important to know that PowerShell can only query the first 1000 subscriptions with Resource Graph. So, if you have more than 1000 subscriptions, then you need to use batches, each of which can be used to query a certain group of subscriptions.
There is also another limit of 1000 on the results that we can get out of Resource Graph. What that means is that a Resource Graph query can return only 1000 results. To overcome this limit, the "Skip" parameter can be used to ignore the specified number of results. In the following example, the first command gives us the first five results, and the second command ignores the first five results and gives us the rest. So you can simply set the first and skip parameters dynamically to return all the results without worrying about the 1000 limit.
$result = Search-AzGraph -Query $KustoQuery -first 5 $result | select name $result = Search-AzGraph -Query $KustoQuery -Skip 5 $result | select name
First and Skip parameters are useful when you have more than 1000 resources to lis
Some Useful Basic Queries
Here are some sample queries that we can use with Resource Graph.
List resources by type
To list a certain type of resource, we can use the following query:
$KustoQuery = " Resources | where type == 'microsoft.storage/storageaccounts' " $result = Search-AzGraph -Query $KustoQuery $result | select name
Listing storage account resources
List Windows virtual machines
To list only the VMs with the Windows operating system, use the following:
$KustoQuery = "
Resources
| where type == 'microsoft.compute/virtualmachines'
| where properties.storageProfile.osDisk.osType == 'Windows'
"
$result = Search-AzGraph -Query $KustoQuery
$result | select name, @{l="OsType";e={$_.properties.storageProfile.osDisk.osType}}
Listing Windows VMs
List all public IPs
In the scenario below, we will list all Public IP addresses along with their Resource IDs, but the display name for the resource IDs will be "SampleColumnToRepresentResourceId".
Listing public IP addresses with a custom display name for ResourceId
List all resource groups with a specific tag value
With the following query, only the resource groups with a specific tag can be listed:
$KustoQuery = " resourcecontainers | where type == 'microsoft.resources/subscriptions/resourcegroups' | where tags['Importance'] == 'High' " $result = Search-AzGraph -Query $KustoQuery $result | select name
Listing resource groups with a specific tag
Count resources in a specific location
The following query can be used to obtain the number of resources in the North Europe region grouped by subscriptionId:
$KustoQuery = "
resources
| where location == 'northeurope'
| summarize total=count () by subscriptionId
"
$result = Search-AzGraph -Query $KustoQuery
$result | select total, subscriptionId, @{l="location";e={"North Europe"}}
Resource count for the resources in the North Europe region
List running VMs only
To list only the running VMs, we can use the following query:
$KustoQuery = " resources | where type == 'microsoft.compute/virtualmachines' | where properties.extended.instanceView.powerState.displayStatus == 'VM running' | project name, location, resourceGroup " $result = Search-AzGraph -Query $KustoQuery $result
A query to get running VMs
List noncompliant virtual machine resources based on the policies in effect
We can list noncompliant virtual machines with the following query:
Subscribe to 4sysops newsletter!
$KustoQuery = " policyresources | where type == 'microsoft.policyinsights/policystates' | where properties.complianceState == 'NonCompliant' | where properties.resourceType =~ 'microsoft.compute/virtualmachines' | project resourceGroup, id=properties.resourceId, ComplianceStatus = properties.complianceState " $result = Search-AzGraph -Query $KustoQuery $result | fl *
Getting a list of noncompliant VM resources
Conclusion
Resource Graph is a very convenient and fast query solution in Azure that allows you to develop advanced queries against Azure resources across multiple subscriptions. It's even easier to get data out of Azure using the combination of PowerShell and Resource Graph queries with the KQL language.
