Azure - Add Subscription Field to Output

Hello,

The output of the below script is leaving the “Subscription” field blank. I have it as part of the object but there is no value showing. Any help would be appreciated.

Blockquote

Authenticate to Azure

Connect-AzAccount

Variables

$OutData = @()
$IgnoreDB = @(‘master’, ‘SSISDB’)

Get-AzSubscription | ForEach-Object{
$subscriptionId = $.Id
$subscriptionName = $
.Name

Set-AzContext -SubscriptionId $subscriptionId

#$AzSqlServer = Get-AzSqlServer | Out-GridView -OutputMode Multiple
$AzSqlServer = Get-AzSqlServer
if($AzSqlServer)
{
    Foreach ($server in $AzSqlServer)
    {
        $SQLDatabase = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object { $_.DatabaseName -notin $IgnoreDB }
        Foreach ($database in $SQLDatabase)
        {
            $db_resource = Get-AzResource -ResourceId $database.ResourceId
            $subscriptionName = Get-AzSubscription -SubscriptionName

            # Database maximum storage size
            $db_MaximumStorageSize = $database.MaxSizeBytes / 1GB

            # Database used space
            $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage'
            $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
            $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)

            # Database used space procentage
            $db_metric_storage_percent = $db_resource | Get-AzMetric -MetricName 'storage_percent'
            $db_UsedSpacePercentage = $db_metric_storage_percent.Data.Maximum | Select-Object -Last 1

            # Database allocated space
            $db_metric_allocated_data_storage = $db_resource | Get-AzMetric -MetricName 'allocated_data_storage'
            $db_AllocatedSpace = $db_metric_allocated_data_storage.Data.Average | Select-Object -Last 1
            $db_AllocatedSpace = [math]::Round($db_AllocatedSpace / 1GB, 2) 

            <#
            $report += [PSCustomObject]@{
                "Subscription Name" = $subscriptionName
                "Server" = $server.ServerName
            }
            #>

            $Report = New-Object PSObject
            $Report | Add-Member -Name "Subscription" -MemberType NoteProperty -Value $subscriptionName.Name
            $Report | Add-Member -Name "Server" -MemberType NoteProperty -Value $server.ServerName
            $Report | Add-Member -Name "Database" -MemberType NoteProperty -Value $database.DatabaseName
            $Report | Add-Member -Name "Used Space (GB)" -MemberType NoteProperty -Value $db_UsedSpace
            $Report | Add-Member -Name "Used Space Percentage" -MemberType NoteProperty -Value $db_UsedSpacePercentage
            $Report | Add-Member -Name "Allocated Space (GB)" -MemberType NoteProperty -Value $db_AllocatedSpace
            $Report | Add-Member -Name "Max Storage Size (GB)" -MemberType NoteProperty -Value $db_MaximumStorageSize
            #$Report | Add-Member -Name "Min vCores" -MemberType NoteProperty -Value $db_VCoreMin
            #$Report | Add-Member -Name "Max vCores" -MemberType NoteProperty -Value $db_VCoreMax
            $OutData += $Report
        }
    }

    $OutData | ft

}

}

As you might have noticed your code got messed up by the forum software because you did not format it as code.
Please go back, edit your question again and fix the formating of your code.

When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

Please, use this occasion to remove code you commented out anyway.

Regardless of that and woithout further investigation:

You set the variable $subscriptionName twice in your code. Is that on purpose?

Instead of the very old approach of creating an object with Add-Member you should use a [PSCustomObject]. That would make your code much easier to read and probably even faster.

Hi Olaf,

No matter what I do, it is ignoring those lines as code.

AS for the PSCustomObject, how do I customize the output column, i.e.) “Subscription Name” with a space, instead of “SubscriptionName”.

Thanks,
Frank

What do you mean? :thinking:

That’s what’s on your mind right now? Spaces … really? :face_with_raised_eyebrow: … ok.

Whenever you want to use spaces in names you use quotes. :man_shrugging:t4:

I used the [PSCustomObject] method but it’s only returning one row of data.

Here’s the new code:

‘’’

Authenticate to Azure

Connect-AzAccount

Variables

$OutData = @()
$IgnoreDB = @(‘master’, ‘SSISDB’)

#$Subscription = Get-AzSubscription | Out-GridView -OutputMode ‘Single’
#$Subscription = Get-AzSubscription -SubscriptionName $SubscriptionName | Select-AzSubscription

Get-AzSubscription | ForEach-Object{
$subscriptionId = $.Id
$subscriptionName = $
.Name

Set-AzContext -SubscriptionId $subscriptionId
<#
if($Subscription){
$Subscription | Select-AzSubscription
#>

#$AzSqlServer = Get-AzSqlServer | Out-GridView -OutputMode Multiple
$AzSqlServer = Get-AzSqlServer
if($AzSqlServer)
{
    Foreach ($server in $AzSqlServer)
    {
        $SQLDatabase = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object { $_.DatabaseName -notin $IgnoreDB }
        Foreach ($database in $SQLDatabase)
        {
            $db_resource = Get-AzResource -ResourceId $database.ResourceId
            #$subscriptionName = Get-AzSubscription -SubscriptionName

            # Database maximum storage size
            $db_MaximumStorageSize = $database.MaxSizeBytes / 1GB

            # Database used space
            $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage'
            $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
            $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)

            # Database used space procentage
            $db_metric_storage_percent = $db_resource | Get-AzMetric -MetricName 'storage_percent'
            $db_UsedSpacePercentage = $db_metric_storage_percent.Data.Maximum | Select-Object -Last 1

            # Database allocated space
            $db_metric_allocated_data_storage = $db_resource | Get-AzMetric -MetricName 'allocated_data_storage'
            $db_AllocatedSpace = $db_metric_allocated_data_storage.Data.Average | Select-Object -Last 1
            $db_AllocatedSpace = [math]::Round($db_AllocatedSpace / 1GB, 2) 

            <#
            # Database VCore
            $db_VCoreMin = $db.MinimumCapacity
            $db_VCoreMax = $db.Capacity
            #>

            
            $report += [PSCustomObject]@{
                                         "Subscription Name"    = $subscriptionName
                                         "Server"               = $server.ServerName
                                         "Database"             = $database.DatabaseName
                                         "Used Space"           = $db_UsedSpace
                                         "Used Space %"         = $db_UsedSpacePercentage
                                         "Allocated Space (GB)" = $db_AllocatedSpace
                                         "Max Storage (GB)"     = $db_MaximumStorageSize
             }
             $OutData += $report
            
            <#
            $Report = New-Object PSObject
            #$Report | Add-Member -Name "Subscription" -MemberType NoteProperty -Value $subscriptionName.Name
            $Report | Add-Member -Name "Server" -MemberType NoteProperty -Value $server.ServerName
            $Report | Add-Member -Name "Database" -MemberType NoteProperty -Value $database.DatabaseName
            $Report | Add-Member -Name "Used Space (GB)" -MemberType NoteProperty -Value $db_UsedSpace
            $Report | Add-Member -Name "Used Space Percentage" -MemberType NoteProperty -Value $db_UsedSpacePercentage
            $Report | Add-Member -Name "Allocated Space (GB)" -MemberType NoteProperty -Value $db_AllocatedSpace
            $Report | Add-Member -Name "Max Storage Size (GB)" -MemberType NoteProperty -Value $db_MaximumStorageSize
            #$Report | Add-Member -Name "Min vCores" -MemberType NoteProperty -Value $db_VCoreMin
            #$Report | Add-Member -Name "Max vCores" -MemberType NoteProperty -Value $db_VCoreMax
            $OutData += $Report
            #>
        }
    }
    #$OutData | Out-GridView
    #$OutData | ft
    $report | FT
    
}

}
‘’’

I also don’t seem to have the option to highlight code and click the button as shown in your formatting video.

psforum_options

Simply place your cursor on an empty line, click the button </> and then paste your code where you’ve been told to do.

I don’t have the </> button as you can see from my screenshot.

But you already managed to get your code properly formatted in your older posts?!
:man_shrugging:t4:

Not sure why it’s missing from my menu bar. It doesn’t make sense to me.

You should explore. HINT: It’s there, just check all the buttons.

Haha, wow. Thanks for that. It’s funny cause I kept looking at that button thinking it was a Settings button but didn’t click. I think I need to slow things down. Thx!

Here’s the code:

# Authenticate to Azure
Connect-AzAccount

# Variables
$OutData = @()
$IgnoreDB = @('master', 'SSISDB')

Get-AzSubscription | ForEach-Object{
    $subscriptionId = $_.Id
    $subscriptionName = $_.Name

Set-AzContext -SubscriptionId $subscriptionId

    #$AzSqlServer = Get-AzSqlServer | Out-GridView -OutputMode Multiple
    $AzSqlServer = Get-AzSqlServer
    if($AzSqlServer)
    {
        Foreach ($server in $AzSqlServer)
        {
            $SQLDatabase = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object { $_.DatabaseName -notin $IgnoreDB }
            Foreach ($database in $SQLDatabase)
            {
                $db_resource = Get-AzResource -ResourceId $database.ResourceId
                #$subscriptionName = Get-AzSubscription -SubscriptionName

                # Database maximum storage size
                $db_MaximumStorageSize = $database.MaxSizeBytes / 1GB

                # Database used space
                $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage'
                $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
                $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)

                # Database used space procentage
                $db_metric_storage_percent = $db_resource | Get-AzMetric -MetricName 'storage_percent'
                $db_UsedSpacePercentage = $db_metric_storage_percent.Data.Maximum | Select-Object -Last 1

                # Database allocated space
                $db_metric_allocated_data_storage = $db_resource | Get-AzMetric -MetricName 'allocated_data_storage'
                $db_AllocatedSpace = $db_metric_allocated_data_storage.Data.Average | Select-Object -Last 1
                $db_AllocatedSpace = [math]::Round($db_AllocatedSpace / 1GB, 2) 


                $report += [PSCustomObject]@{
                                             "Subscription Name"    = $subscriptionName
                                             "Server"               = $server.ServerName
                                             "Database"             = $database.DatabaseName
                                             "Used Space"           = $db_UsedSpace
                                             "Used Space %"         = $db_UsedSpacePercentage
                                             "Allocated Space (GB)" = $db_AllocatedSpace
                                             "Max Storage (GB)"     = $db_MaximumStorageSize
                 }
                 $OutData += $report
                
                <#
                $Report = New-Object PSObject
                #$Report | Add-Member -Name "Subscription" -MemberType NoteProperty -Value $subscriptionName.Name
                $Report | Add-Member -Name "Server" -MemberType NoteProperty -Value $server.ServerName
                $Report | Add-Member -Name "Database" -MemberType NoteProperty -Value $database.DatabaseName
                $Report | Add-Member -Name "Used Space (GB)" -MemberType NoteProperty -Value $db_UsedSpace
                $Report | Add-Member -Name "Used Space Percentage" -MemberType NoteProperty -Value $db_UsedSpacePercentage
                $Report | Add-Member -Name "Allocated Space (GB)" -MemberType NoteProperty -Value $db_AllocatedSpace
                $Report | Add-Member -Name "Max Storage Size (GB)" -MemberType NoteProperty -Value $db_MaximumStorageSize
                #$Report | Add-Member -Name "Min vCores" -MemberType NoteProperty -Value $db_VCoreMin
                #$Report | Add-Member -Name "Max vCores" -MemberType NoteProperty -Value $db_VCoreMax
                $OutData += $Report
                #>
            }
        }
        #$OutData | ft
        $report | FT
        
    }
}

1 Like

Hehe I don’t think anyone here will say this is amazing forum software. It is what it is. :slight_smile:

Without digging into your logic to prepare the values for your output … that’s how we use a [PSCustomObject]:

Connect-AzAccount
$IgnoreDB = @('master', 'SSISDB')

$Report = 
Get-AzSubscription | 
ForEach-Object {
    $subscriptionName = $_.Name

    Set-AzContext -SubscriptionId $_.Id
    $AzSqlServer = Get-AzSqlServer
    if ($AzSqlServer) {
        Foreach ($server in $AzSqlServer) {
            $SQLDatabase = 
                Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | 
                    Where-Object { $_.DatabaseName -notin $IgnoreDB }
            Foreach ($database in $SQLDatabase) {
                $db_resource = Get-AzResource -ResourceId $database.ResourceId
                $db_MaximumStorageSize = $database.MaxSizeBytes / 1GB
                $db_metric_storage = $db_resource | Get-AzMetric -MetricName 'storage'
                $db_UsedSpace = $db_metric_storage.Data.Maximum | Select-Object -Last 1
                $db_UsedSpace = [math]::Round($db_UsedSpace / 1GB, 2)
                $db_metric_storage_percent = $db_resource | Get-AzMetric -MetricName 'storage_percent'
                $db_UsedSpacePercentage = $db_metric_storage_percent.Data.Maximum | Select-Object -Last 1
                $db_metric_allocated_data_storage = $db_resource | Get-AzMetric -MetricName 'allocated_data_storage'
                $db_AllocatedSpace = $db_metric_allocated_data_storage.Data.Average | Select-Object -Last 1
                $db_AllocatedSpace = [math]::Round($db_AllocatedSpace / 1GB, 2) 

                [PSCustomObject]@{
                    "Subscription Name"    = $subscriptionName
                    Server                 = $server.ServerName
                    Database               = $database.DatabaseName
                    "Used Space"           = $db_UsedSpace
                    "Used Space %"         = $db_UsedSpacePercentage
                    "Allocated Space (GB)" = $db_AllocatedSpace
                    "Max Storage (GB)"     = $db_MaximumStorageSize
                }
            }
        }
    }
}
$Report

… untested … :wink:

Thx, that returned the results I’m looking for just not in table format. I piped the output to FT but it’s still not formatted. I’ll see what I can do.

You should only pipe the $Report to Format-Table!! Don’t add the Format-Table into your loop.

I am, but it’s not working.

Do you run this?

$Report | Format-Table

What happens then? How does your output look like?

Subscription Name    : 
Server               : 
Database             : 
Used Space           : 0.04
Used Space %         : 2
Allocated Space (GB) : 0.05
Max Storage (GB)     : 2

I removed the values for the first 3.

minion-what

Try

Get-Process | Select-Object -First 5 | Format-Table

Does that show up as a table?

Did you change the code I suggested? :face_with_raised_eyebrow: