office365 - Office 365 Powershell - Formatting the output of a foreach-object loop

Question

Good evening all,

Still pretty new to Powershell but I've been trying to get some scripts together to make some of the reporting easier for a lot of the information that we need to pull from our clients' Office 365 tenants.

The script already does what I need it to (grab the subscriptions from the tenant, grab the friendly name of the subscription from my CSV, then write to the console), however my question is about the formatting: I would like to format this output in a table with the column headers Subscriptions, Active, Suspended, Assigned.

$sku = Get-MsolAccountSku | select-object skupartnumber,ActiveUnits,suspendedUnits,ConsumedUnits | sort-object -property skupartnumber
$skudata = import-csv -Header friendlyname,skupartnumber "C:PShellSKUs.csv" | where-object {$sku.skupartnumber -eq $_.skupartnumber} | sort-object -property skupartnumber

$skudata | foreach-object {$n = 0}{write-host $skudata.friendlyname[$n], $sku.ActiveUnits[$n], $sku.SuspendedUnits[$n], $sku.ConsumedUnits[$n]; $n = $n + 1}

## Output:

POWER BI (FREE) 1000000 0 1
MICROSOFT TEAMS EXPLORATORY 100 0 6

Here's the script and output, I'm using write-host right now because this formats the data in the same way that we already want it and I can easily copy it out of the console and in to our tickets. When I use write-output, however, it puts each cell on its own line and I can't seem to figure out how to pipe this in to format-table .

$skudata | foreach-object {$n = 0}{write-output $skudata.friendlyname[$n], $sku.ActiveUnits[$n], $sku.SuspendedUnits[$n], $sku.ConsumedUnits[$n]; $n = $n + 1}

# Output:
POWER BI (FREE)
1000000
0
1
MICROSOFT TEAMS EXPLORATORY
100
0
6

I'm already able to get everything I need using the below hash table and simple script, the only problem is that it can't pull the common subscription name and Microsoft's skupartnumber identifier is the best option which isn't always very descriptive. I'm sure there's some very simple solution I'm just missing, but if anyone could point me in the right direction for either solution I've tried it would be greatly appreciated!

$subs = @{Label="Subscription"; Expression={$_.skupartnumber}; Alignment = "right";}
$active = @{Label="Active"; Expression={$_.ActiveUnits}; Alignment = "right"}
$assigned = @{Label="Assigned"; Expression={$_.ConsumedUnits}; Alignment = "right"}
$suspended = @{Label="Suspended"; Expression={$_.SuspendedUnits}; Alignment = "right"}

Get-MsolAccountSku | Format-Table $subs, $active, $suspended, $assigned -autosize

# Output:
     Subscription  Active Suspended Assigned
     ------------  ------ --------- --------
POWER_BI_STANDARD 1000000         0        1
TEAMS_EXPLORATORY     100         0        6

question from:https://stackoverflow.com/questions/65650651/office-365-powershell-formatting-the-output-of-a-foreach-object-loop

Answer 1

PowerShell is an object oriented language and what you need is to gather the info you want in objects for further processing or output.

Try

$result = for ($n = 0; $n -lt $skudata.Count; $n++) {
    # output the data as PSObject that gets collected in variable $result
    [PsCustomObject]@{
        Subscription = $skudata.friendlyname[$n]
        Active       = $sku.ActiveUnits[$n]
        Suspended    = $sku.SuspendedUnits[$n]
        Assigned     = $sku.ConsumedUnits[$n]
    }
}

# output on screen
$result | Format-Table -AutoSize

# output to CSV
$result | Export-Csv -Path "C:PShellSkuUsage.csv" -NoTypeInformation