Simplify Tenant-Wide Azure Tasks with KQL and PowerShell

Managing resources across a large Azure tenant can feel overwhelming, especially when dealing with diverse subscriptions and resource types. What if you need to:

• Delete all storage accounts with a specific tag across your tenant?
• Shut down all VMs with a specific SKU?
• Identify storage accounts accessed via SAS tokens?

These scenarios share one thing: tenant-wide scope. While Azure Policy can handle some cases, one-time operations often call for a more flexible approach. That’s where PowerShell and Azure Resource Graph Explorer come in. In this article, I’ll share a proven technique to manage large-scale Azure operations efficiently.

Tooling

This will be easy, if you are using Azure PowerShell, you don’t need anything extra.?

We will use following tools / modules:

• Azure Resource Graph Explorer
• KQL query/queries inside Resource Graph Explorer
• PowerShell
• Azure PowerShell module (most likely several modules from Azure PowerShell bundle)

Approach

In the end it will be PowerShell script that has some steps.

We can say the script will have following sections:

1. Query section that will fetch Azure resources which fulfil some specific condition e.g. all resources with XYZ tag
2. Execution section to performs desired operation (delete/update/data fetching) over the resources that came from first part
3. Main loop going through all subscriptions and wrapping execution section

It will become clear once you see the PowerShell code.

?

Querying Azure Resource Graph Explorer

Integral part of this approach is Azure Resource Graph Explorer and KQL for querying desired resources.

I wont be going into depth of writing queries, good info can be found in official docs here: https://learn.microsoft.com/en-us/azure/governance/resource-graph/samples/starter?tabs=azure-powershell

There are some starter queries for many scenarios.

It is using KQL heavily with some limitations, limitations compared to complex Log Analytics / App Insights KQL queries.

There are several tables, I found myself using those two the most:

• resources
• resourcecontainers

Resources is for all resources in Azure - except containers. Containers are Resource Groups, Subscriptions and Management Groups - basically resource that can contain child resources is qualified as "resource container" and these are queried from resourcecontainers table.

If you are not comfortable with using Azure Resource Graph Explorer, its great time to start doing so now, it has many usages. In portal you can find it like this:

You can get started just by typing one of the tables mentioned above and running the query.

It returns all the resources that you have access to inside the tenant (except subscriptions, rgs, those are in another table).

Code itself

I will explain below some important parts

$ErrorActionPreference = 'Stop';

# reusable function to query whole tenant
function Get-AzureResourceGraphResources([string] $Query, [string] $SubscriptionId = $null)
{
    $results = [System.Collections.Generic.List[psobject]]::new();
    $skipToken = $null;
    $queryResult = $null;
    $invocationParams = @{
        Query = $query;
        First = 1000;
    }
    if(![string]::IsNullOrEmpty($SubscriptionId))
    {
        $invocationParams['Subscription'] = $SubscriptionId;
    }
    do {
        if ($null -eq $skipToken) {
            $invocationParams.Remove('SkipToken')
            $queryResult = Search-AzGraph @invocationParams;
        }
        else {
            $invocationParams['SkipToken'] = $skipToken;
            $queryResult = Search-AzGraph @invocationParams;
        }
        $skipToken = $queryResult.SkipToken;
        if($queryResult.Data)
        {
            foreach($item in $queryResult.Data)
            {
                $results.Add($item);
            }
        }
    } while ($null -ne $skipToken);
    return $results;
}

# Query to fulfil business requirements
$query = @'
resources
| where type =~ "microsoft.storage/storageaccounts"
| where tags['owner'] =~ "[email protected]"
| where tags['cost-center'] !~ "BKB Cloud"
'@;

$allResources = Get-AzureResourceGraphResources -Query $query;
# Subscription ids to set PowerShell context before execution part
$subscriptionIds = $allResources | select -ExpandProperty SubscriptionId -Unique;

foreach($subId in $subscriptionIds)
{
    Write-Host "SubscriptionId: $subId" -ForegroundColor Yellow;
    [Void](Set-AzContext -Subscription $subId);

    # get all resources for subscription
    $resourcesInSubscription = $allResources | where { $_.SubscriptionId -eq $subId };
    foreach($resource in $resourcesInSubscription)
    {
        Write-Host "Resource: $($resource.name)" -ForegroundColor Cyan;
        # In this scenario we are updating tags, but you could change it to any other operation
        Update-AzTag -Tag @{ 'cost-center' = 'BKB Cloud' } -ResourceId $resource.id -Operation Merge;
    }
}
Write-Host "Finished..." -ForegroundColor Green;?        

Explaining the code

Function Get-AzureResourceGraphResources([string] $Query, [string] $SubscriptionId = $null)

Generic function, its purpose is to execute KQL passed to it, with optional SubscriptionId.

If you pass SubscriptionId, it will perform the query and return results that conform to the query only from that subscription.

Result is .NET generic list with custom PS objects, each item has exactly same attributes as Azure Resource Graph Explorer would return in Portal.

?

KQL Query

In the code query is exactly the same query as you would run in Azure Resource Graph Explorer within Portal.

$query = @'
resources
| where type =~ "microsoft.storage/storageaccounts"
| where tags['owner'] =~ "[email protected]"
| where tags['cost-center'] !~ "BKB Cloud"
'@;        

This query gets all storage accounts (=~ means case insensitive so that it doesn’t matter if its microsoft.storage/storageaccounts or Microsoft.Storage/StorageAccounts) that have owner tag with specific value, while cost-center tag is not equal to BKB Cloud (again case insensitive).

Main Execution Loop

Main loop goes through all subscriptions, one by one and for every subscription it gets resources in that particular subscription.

Nested - execution loop iterates over every resource and performs some action.

In this case the action was to add "cost-center" tag and set it to "BKB Cloud".

Together with the query which is written in a way that it only returns resources that do not have this tag, it forms idempotent execution, so that only resources that don’t have this tag will be tagged - we could say its optimized for runtime this way, because it doesn't try to add tag if there is one already.

If instead updating tags, you want to let's say delete the resource

Change

Update-AzTag -Tag @{ 'cost-center' = 'BKB Cloud' } -ResourceId $resource.id -Operation Merge;        

To

Remove-AzResource -ResourceId $resource.id -Force;        

Closing Thoughts

This is my personal favorite approach for making organization-wide changes that contain some business logic which is not suitable for Azure Policy or one time activities.

For very large tenants, runtime of this solution could be in hours, due to its sequential approach.

In some future article I will focus on parallelization of this approach.

I would be interested, do you employ any similar technique like this for changes across whole tenant?