Can you imagine how long it would take to generate a list of VMs across hundreds of subscriptions on Azure? It would take ages not only to create a list of resources but also to find a single resource in a massive environment. As we know, Azure Portal only lists the first 1000 subscriptions, and it is not really convenient to query resources using the portal when you have a lot of subscriptions. Fortunately, we've got the option to query resources in a much faster and more dynamic way. In this post, we will cover Azure Resource Graph and use it with PowerShell for even more flexible query management.

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

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

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

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

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

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

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

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

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

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

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.

avatar
0 Comments

Leave a reply

Please enclose code in pre tags

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2021

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account