Loop Through Azure Resources

PowerShell Help
1

Hello,

My code prompts a user for their Azure subscription, then loops through all resource groups, then all Azure SQL servers, then through each database on those servers. However, it’s only returning a set of databases from one server.

PARAM(
    [string] [Parameter(Mandatory = $True, HelpMessage = "Choose subscription you want be inventoried")] $SubscriptionName
    )

#Variables

#Login to Azure
Connect-AzAccount

$cusTab1 = @{Expression={$_.DatabaseName};Label="Display Name";width=25}, `
           @{Expression={$_.BlobStorageTargetState};Label="Login Name";width=40}
 
#Select Azure Subscription
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription

$rg = Get-AzResourceGroup | ?{ $_.ProvisioningState -eq "succeeded" } | select -expandproperty resourcegroupname
foreach ($resourcegroup in $rg)
{
    $svr=(get-azsqlserver -ResourceGroupName $resourcegroup).ServerName
    foreach ($servers in $svr)
    {
        $dbs = Get-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $servers
        $results = foreach ($db in $dbs.DatabaseName)
        {
            $dbAudit = Get-AzSqlDatabaseAudit -ResourceGroupName $resourcegroup -ServerName $servers -DatabaseName $db

            [PSCustomObject]@{
                            Subscription  = $SubscriptionName
                            Server        = $servers
                            ResourceGroup = $dbAudit.ResourceGroupName
                            Database      = $dbAudit.DatabaseName
                            Storage       = $dbAudit.BlobStorageTargetState
                            EventHub      = $dbAudit.EventHubTargetState
                            LogAnalytics  = $dbAudit.LogAnalyticsTargetState
                            Retention     = $dbAudit.RetentionInDays
                            }
        }
    }

}

$results | FT

I’m looking for a second set of eyes to see if I’m missing anything. Obviously my code is failing somewhere, but I’m stuck at this point.

Thanks in advance!

2

Remove the variable assignment for $results inside your innermost loop and place it on the outermost loop.

3

I’m not sure I understand. Am I removing the entire ‘foreach’ statement? I tested that and it doesn’t work.

4

:roll_eyes:

PARAM(
    [string] [Parameter(Mandatory = $True, HelpMessage = "Choose subscription you want be inventoried")] $SubscriptionName
    )

#Variables

#Login to Azure
Connect-AzAccount

$cusTab1 = @{Expression={$_.DatabaseName};Label="Display Name";width=25}, `
           @{Expression={$_.BlobStorageTargetState};Label="Login Name";width=40}
 
#Select Azure Subscription
Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription

$rg = Get-AzResourceGroup | ?{ $_.ProvisioningState -eq "succeeded" } | select -expandproperty resourcegroupname
$results = 
foreach ($resourcegroup in $rg)
{
    $svr=(get-azsqlserver -ResourceGroupName $resourcegroup).ServerName
    foreach ($servers in $svr)
    {
        $dbs = Get-AzSqlDatabase -ResourceGroupName $resourcegroup -ServerName $servers
        foreach ($db in $dbs.DatabaseName)
        {
            $dbAudit = Get-AzSqlDatabaseAudit -ResourceGroupName $resourcegroup -ServerName $servers -DatabaseName $db

            [PSCustomObject]@{
                            Subscription  = $SubscriptionName
                            Server        = $servers
                            ResourceGroup = $dbAudit.ResourceGroupName
                            Database      = $dbAudit.DatabaseName
                            Storage       = $dbAudit.BlobStorageTargetState
                            EventHub      = $dbAudit.EventHubTargetState
                            LogAnalytics  = $dbAudit.LogAnalyticsTargetState
                            Retention     = $dbAudit.RetentionInDays
                            }
        }
    }

}

$results | FT
5

:man_facepalming:

Haha, that worked. My mind is a little slow this morning.

Thanks for your help!