Script to report on Tenant Subscriptions using Resource Manager API, KQL and PowerShell

subscriptionReport.ps1

Connect-AzAccount -Subscription $subIdContext['Prod']
Connect-MgGraph

class Identity {
    [string] $Id
    [string] $DisplayName
    [string] $Type

    Identity([hashtable] $identity) {
        $this.Id = $identity['id']
        $this.DisplayName = $identity['displayName']
        $this.Type = $identity['@odata.type'].SubString(17)
    }
}

$owner = Search-AzGraph @'
authorizationresources
| where ['type'] == 'microsoft.authorization/roledefinitions'
    and properties.roleName == 'Owner'
| take 1
| project ['id']
'@ -AllowPartialScope -UseTenantScope

[string[]] $subscriptions = Search-AzGraph @'
resourcecontainers
| where ['type'] == 'microsoft.resources/subscriptions'
| summarize Subscriptions = make_set(subscriptionId)
'@ | ForEach-Object { $_.Data.Subscriptions }

$query = @"
resourcecontainers
| where ['type'] == 'microsoft.resources/subscriptions'
| where ['subscriptionId'] in ({0})
| join kind = leftouter (
    resources
    | where ['subscriptionId'] in ({0})
    | summarize ResourceCount = count() by subscriptionId
) on subscriptionId
| join kind = leftouter (
    authorizationresources
    | where ['type'] == 'microsoft.authorization/roleassignments'
        and ['subscriptionId'] in ({0})
        and properties.roleDefinitionId == '$($owner.id)'
         or ['type'] == 'microsoft.authorization/classicadministrators'
        and properties.role !~ 'none'
    | summarize
        OwnerRoleAssignments = make_set_if(
            properties.principalId,
            ['type'] == 'microsoft.authorization/roleassignments'
            and properties.scope endswith subscriptionId),
        LastRoleAssignmentDate = arg_max(make_datetime(properties.updatedOn), ''),
        ClassicAdminCount = countif(
            ['type'] == 'microsoft.authorization/classicadministrators')
        by subscriptionId
) on subscriptionId
| join kind = leftouter (
    resourcecontainers
    | extend chain = properties.managementGroupAncestorsChain
    | mv-expand chain
    | summarize chain = make_list(chain.displayName)
    by subscriptionId
) on subscriptionId
| project
    Name = name,
    SubscriptionId = subscriptionId,
    State = properties.state,
    ManagementGroupChain = chain,
    QuotaId = properties.subscriptionPolicies.quotaId,
    LastRoleAssignmentDate,
    ResourceCount,
    OwnerRoleAssignments,
    ActiveOwnerCount = 0,
    ClassicAdminCount,
    TenantId = tenantId
"@

$result = foreach ($chunk in [System.Linq.Enumerable]::Chunk($subscriptions, 100)) {
    $subs = $chunk |
        ForEach-Object { "'$_'" } |
        Join-String -Separator ','

    $searchAzGraphSplat = @{
        Query        = $query -f $subs
        Subscription = $chunk
    }

    do {
        $request = Search-AzGraph @searchAzGraphSplat
        $searchAzGraphSplat['SkipToken'] = $request.SkipToken
        if ($request.Data.Count) {
            $request.Data
        }
    }
    while ($request.SkipToken)
}

$uri = 'v1.0/directoryObjects/{0}'
$map = [System.Collections.Generic.Dictionary[string, Identity]]::new()
$skipHash = [System.Collections.Generic.HashSet[string]]::new()

foreach ($item in $result) {
    $owners = foreach ($id in $item.OwnerRoleAssignments) {
        if ($skipHash.Contains($id)) {
            continue
        }

        if (-not $map.ContainsKey($id)) {
            try {
                $map[$id] = Invoke-MgGraphRequest GET ($uri -f $id)
            }
            catch {
                $null = $skipHash.Add($id)
                continue
            }
        }

        $map[$id]
        $item.ActiveOwnerCount++
    }

    $item.OwnerRoleAssignments = ConvertTo-Json -InputObject @($owners) -Compress
    if ($item.ManagementGroupChain.Length -eq 1) {
        $item.ManagementGroupChain = $item.ManagementGroupChain[0]
        continue
    }

    [array]::Reverse($item.ManagementGroupChain)
    $item.ManagementGroupChain = $item.ManagementGroupChain -join '/'
}

$name = 'subscriptionReport {0}' -f [datetime]::Now.ToString('MM-dd-yyyy')

$excelParams = @{
    WorksheetName = $name
    BoldTopRow    = $true
    TableName     = 'subscriptionReport'
    TableStyle    = 'Medium11'
    InputObject   = $result
    Path          = [System.IO.Path]::Combine(
        '.\SubscriptionsReport\new\export',
        [System.IO.Path]::ChangeExtension($name, 'xlsx'))
}
Export-Excel @excelParams
Invoke-Item .\SubscriptionsReport\new\export\test.xlsx