Optimizing Azure Resource Requests with Resource Graph and PowerShell

Can you imagine how long it will take to generate a list of VMs among hundreds of Azure subscriptions? A month of Sundays. The Azure portal is known to only display the first 1000 subscriptions, which makes it more difficult to request resources with it when you have many more subscriptions. Fortunately, there is a way to do this much faster and more dynamically. In this article, we will break down the functionality of the Azure Resource Graph and use this service using PowerShell, significantly increasing the flexibility of managing requests.

The content of the article:

– Resource Graph and Kusto Query Language
– Resource Graph Module for PowerShell
– Restrictions
– Useful types of queries
– Formatting results
– Conclusion

Resource Graph and Kusto Query Language

Azure Resource Graph is a service that allows you to more accurately query resources across multiple subscriptions using Resource Graph tables.

Kusto Query Language (KQL) is a query language with which Resource Graph returns requested data. KQL supports many operators including join and unionthat allow you to establish cross-table relationships to return more detailed results across multiple tables at once.

Approx. translator:

A little more about Kusto. It is a special process, a language that allows you to get ridonly results from your data sources. The whole system is very similar to SQL.
The query itself consists of a sequence of instructions separated by semicolons. Exactly as we are used to seeing in almost any database shell. The last statement should return what we call the Tabular Expression Statement. The first statement usually gets data from some source, subsequent statements transform the data, and the last statement renders that data.

Data is passed from instruction to instruction through a pipeline |. A typical request in Kusto looks like this (example from docs.microsoft.com):

StormEvents 
| where StartTime >= datetime(2007-11-01) and StartTime < datetime(2007-12-01)
| where State == "FLORIDA"  
| count

Any habrahuman will understand everything here.

Resource Graph queries can be made in the Azure portal. But we are advanced users, so we will use PowerShell. Yes, of course we have the Resource Graph Explorer offering a UI where you can find the Resource Graph tables available for use in queries. Queries can also be saved for later use. But after writing a couple of dozen of these queries, you will already know all the tables by heart and will be able to write them directly.

Azure Portal queries can be made using the Resource Graph explorer

The example above lists all resources with their basic details such as ID, name, subscriptionID, resourceGroup and so on.

Resource Graph Module for PowerShell

Module

Az.ResourceGraph

can be used in PowerShell to query Azure resources across a tenant or across a set of subscriptions. This is how a simple request looks like, which can be executed through PowerShell:

$KustoQuery = "
resources
| where name starts with 'Network'
"
$result = Search-AzGraph -Query $KustoQuery
$result | select name

A simple Kusto request to list all resources:

PS C: $KustoQuery = "
>> resources
>> | where name starts with 'Network'
>> "
$result = Search-AzGraph -Query $KustoQuery
$result | select name

name
- - - -
NetworkWatcher_cenralus
NetworkWatcher_norteurope
NetworkWatcher_westeurope
NetworkWatcher_westus
NetworkWatcher_eastus
NetworkWatcher_japanwest
NetworkWatcher_norteurope
NetworkWatcher_uksouth
NetworkWatcher_westeurope
NetworkWatcher_eastus
NetworkWatcher_norteurope

PS C:>

This query will list all resources with names starting with

Network

…

Approx. translator:

For a convenient work with PowerShell, it is highly recommended to install

oh-my-posh

and use Windows Terminal.

oh-my-posh

adds many features to Powershell that Linux command line users lack. In particular,

reverse-i-search

…

Restrictions

Be aware that PowerShell can only query the first 1000 subscriptions through the Resource Graph. If you have more of them, you will need to split them into separate packages.

On the one hand, this is a very strange limitation. Anyone who has worked with databases may be surprised here. But if you think about it, it seems reasonable enough. We are not working with a database, but requesting objects from AD. If there are more than 1000 of them, then maybe you should think about how to structure them in a more decent way.

Another limitation is the output of a maximum of 1000 results in response to a query. To get around it, you can ignore a certain number of them using the parameter Skip… In the example below, the first command prints the first five results, while the second ignores those first results and prints the rest. So you can just dynamically set the parameters first and Skipto return all results, not limited to 1000.

Anyone who encounters such restrictions is advised to contact this document. There you will find more detailed work options.

$result = Search-AzGraph -Query $KustoQuery -first 5
$result | select name
$result = Search-AzGraph -Query $KustoQuery -Skip 5
$result | select name

Options first and Skip help when you need to withdraw more than 1000 resources

Useful types of queries

Here are some examples of requests you can make using the Resource Graph.

Resource listing by type

Request for displaying a certain type of resources:

$KustoQuery = "
Resources
| where type == 'microsoft.storage/storageaccounts'
"
$result = Search-AzGraph -Query $KustoQuery
$result | select name

Displaying storage account resources

Outputting Windows virtual machines

Request to output only VM with Windows:

$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}}

Output VM from Windows

List all public IPs

In the script below, we display all public IP addresses along with their resource IDs, but the name for the IDs will be displayed as

SampleColumnToRepresentResourceId

…

Displaying public IP addresses with a custom name for ResourceId

Displaying resource groups with a specific tag

The following query will only list resource groups with the given tag:

$KustoQuery = "
resourcecontainers
| where type == 'microsoft.resources/subscriptions/resourcegroups'
| where tags['Importance'] == 'High'
"
$result = Search-AzGraph -Query $KustoQuery
$result | select name

Displaying resource groups with a given tag

Counting resources in a specific region

The following query will get the number of resources in

North Europe

grouped by their

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 calculation in the North Europe region

Displaying running VMs

To display only running VMs, use the following request:

$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

Request to get started VMs

Displaying VM resources that do not comply with policies

VMs that do not comply with the current policies are displayed like this:

$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 non-compliant VM resources

Approx. translator:

Formatting results

Of course, keep in mind that PowerShell has many different ways to format the final results. In this case, requests to Kusto can be formatted using a parameter in the method call. There are two ways to format the resulting data: Table and Array of Objects.

Moreover, this data is perfectly handled by the built-in function ConvertTo-Json…

Here is an example of the returned data table created using the parameter resultFormat=Tablepassed through ConvertTo-JSON:

{
    "totalRecords": 47,
    "count": 1,
    "data": {
        "columns": [{
                "name": "name",
                "type": "string"
            },
            {
                "name": "type",
                "type": "string"
            },
            {
                "name": "location",
                "type": "string"
            },
            {
                "name": "subscriptionId",
                "type": "string"
            }
        ],
        "rows": [
            [
                "veryscaryvm2-nsg",
                "microsoft.network/networksecuritygroups",
                "eastus",
                "11111111-1111-1111-1111-111111111111"
            ]
        ]
    },
    "facets": [],
    "resultTruncated": "true"
}

Such data is very easy to process in any other program. Details on how to properly format your data are described

here

…