Is there a more elegant way to code this?

PowerShell Help
1

Hi.

I’m trying to create a .csv report of O365 mail boxes by department, showing all the O365 licenses applied to each mailbox. The licenses are in a table.

I have the following code which works fine, but I don’t like having 4 or 5 separate headings for each license entry.

Is there a way to have a foreach-object loop within the PSCustomObject?

# Report on users, departments and O365 licenses
$report = @()
$accts  = Get-MsolUser -All | Select DisplayName,UserPrincipalName,Department,IsLicensed,Licenses
$report += $accts |
    ForEach-Object  {
        [PSCustomObject]@{
            Name       = $_.DisplayName
            UPN        = $_.UserPrincipalName
            Department = $_.Department
            Licensed   = $_.IsLicensed
            License    = $_.Licenses[0].AccountSkuId
            License2   = $_.Licenses[1].AccountSkuId
            License3   = $_.Licenses[2].AccountSkuId
            License4   = $_.Licenses[3].AccountSkuId
            License5   = $_.Licenses[4].AccountSkuId
        }
    }
$report | Export-Csv .\Test.csv -NoTypeInformation

Thank you.

2

If I got it right this should do the trick:

$report =
Get-MsolUser -All | 
ForEach-Object {
    foreach ($License in $_.Licenses) {
        [PSCustomObject]@{
            Name       = $_.DisplayName
            UPN        = $_.UserPrincipalName
            Department = $_.Department
            Licensed   = $_.IsLicensed
            License    = $License.AccountSkuId
        }
    }
}
$report | 
    Export-Csv .\Test.csv -NoTypeInformation
3

That works. Only difference is that it generates a new row for each user that has more than one license, while the code I use puts them in a column.

The drawback with my method is that it only looks at 5 licenses and might overlook those mailboxes that have more.

Thanks.

4

TOTALLY untested … but what about something like this:

Change this:

License    = $_.Licenses[0].AccountSkuId
            License2   = $_.Licenses[1].AccountSkuId
            License3   = $_.Licenses[2].AccountSkuId
            License4   = $_.Licenses[3].AccountSkuId
            License5   = $_.Licenses[4].AccountSkuId

to this:

License(s) = foreach($license in $_.Licenses){Write-Output "$($license.AccountSkuId) "}
1 Like
5

That’s the most common way when you want to have partly hierarchical data mapped to a structured output. Another way would be to put all licenses into one field delimitted by a particular charachter like this:

$report =
    Get-MsolUser -All | 
        ForEach-Object {
            [PSCustomObject]@{
                Name       = $_.DisplayName
                UPN        = $_.UserPrincipalName
                Department = $_.Department
                Licensed   = $_.IsLicensed
                License    = $($_.Licenses.AccountSkuId) -join ', '
            }
        }
$report | 
    Export-Csv .\Test.csv -NoTypeInformation
2 Likes