PowerShell collection exported to separate row in CSV

user_8333362 0
Viewed 108

I have the following script that will export all the Office 365 licenses per user that is enabled for a license to a CSV. The export will list each user and then the multiple licenses on the same row like this: There is not a set number of licenses for each user, users will have different licenses.

enter image description here

Instead of having them export on the same row, I need to have the license for each user on a separate row like this: How can I modify the script to make that happen?

enter image description here

    #### All lincesed users###

$JobCollection = @()

$mbx = Get-MsolUser -all | Where-Object { $_.isLicensed -eq "TRUE" }

$count=0
$fullcount = ($mbx | measure-object).count

foreach ($M in $MBX) {

$count++
$search = $m.userprincipalname.tostring()

Write-progress -activity "Getting Calendar $count out of $fullcount --- $search " -percentcomplete (($count / $fullcount)*100) -status "Processing"

$License = (((Get-MsolUser -UserPrincipalName $M.userprincipalname).Licenses.accountskuid) -join ",")

#$queuelink = $job.reportingqueueuri.absoluteuri
#$jobID = $Job.jobid

$Jobs = New-Object psobject
$JObs | Add-Member -MemberType NoteProperty -Name UserPrincipalName -Value $M.userprincipalname
$JObs | Add-Member -MemberType NoteProperty -Name License -Value $license

$JobCollection += $jobs

}

$JobCollection  | export-csv "C:\test\AllMBXlicensse.csv" -NoTypeInformation
powershell
1 Answers

You can create a nested loop to assign each value to it's corresponding userprincipalname in a PSCustomObject; like you're already doing:

$mbx = Get-MsolUser -all | Where-Object { $_.isLicensed -eq $true }
$mbxCount = $mbx.count
for ($i = 0; $i -lt $mbx.count; $i++)
{
    $UPN = $mbx[$i].userprincipalname
    $progressSplat = @{
        Activity = "Getting Calendar $i out of $mbx --- $UPN"
        PercentComplete = (($i / $mbxCount)*100)
        Status = "Processing"
    }
    Write-Progress @progressSplat
    $userLicenses = (Get-MsolUser -UserPrincipalName $UPN).Licenses.accountskuid
    foreach ($license in $userLicenses)
    {
        [PSCustomObject]@{
            UserPrincipalName = $UPN
            License = $license
        } | Export-Csv -Path "C:\test\AllMBXlicensse.csv" -Append -Force -NoTypeInformation
    } 
}

...or, as TheMadTechnician proposed, using a calculated property (very elegant might I add):

$mbx = Get-MsolUser -all | Where-Object { $_.isLicensed -eq $true }
$mbxCount = $mbx.count
for ($i = 0; $i -lt $mbx.count; $i++)
{
    $UPN = $mbx[$i].userprincipalname
    $progressSplat = @{
        Activity = "Getting Calendar $i out of $mbxCount --- $UPN"
        PercentComplete = (($i / $mbxCount)*100)
        Status = "Processing"
    }
    Write-Progress @progressSplat
    (Get-MsolUser -UserPrincipalName $UPN).Licenses.accountskuid | 
        Select-Object -Property @{
            Name = "UserPrincipalName"
            Expression = { $UPN }
        }, @{
            Name = "License"
            Expression = { $_ }
        } | Export-Csv -Path "C:\test\AllMBXlicensse.csv" -Append -Force -NoTypeInformation
}
user_149037540
Related