Powershell script to display O365 licenses

Question

Hello,

I would like to ask Powershell gurus here for help regarding a little PS script I want to finalize. The script should give me a report regarding Office 365 licenses utilized within our tenant, and whether they are being assigned via license group, or directly. So far it goes like this:

Connect-MsolService
$report = @()
$usersGroup = @()
$userNames2check = get-content <txt file with UPNs of users I need to check>
foreach ($username in $userNames2check)
{
    $usersGroup += Get-MsolUser -UserPrincipalName $username
}
foreach ($user in $usersGroup)
{
  if ($user.Licenses -ne "$null")    
  {
    foreach ($license in $user.Licenses)
    {
        if($license.GroupsAssigningLicense[0].ToString() -eq $user.ObjectId)
        {
            Write-Host -foregroundcolor Cyan $user.UserPrincipalName " " -NoNewline; write-host -foregroundcolor Green $license.AccountSkuId -NoNewline; write-host -ForegroundColor White " Direct assignment"
            $report += $user.UserPrincipalName; $license.AccountSkuId; "Direct assignment"
        }
    elseif ($license.GroupsAssigningLicense[0].ToString() -ne $user.ObjectId)
        {
            write-host -foregroundcolor Cyan $user.UserPrincipalName " " -NoNewline; write-host -foregroundcolor Yellow $license.AccountSkuId -NoNewline; write-host -ForegroundColor White " Assigned by group"
            $report += $user.UserPrincipalName, $license.AccountSkuId, "Assinged by group"
        }
    }
  }
  else
  {
        write-host -ForegroundColor Red $user.UserPrincipalName "has no license"
        $report += $user.UserPrincipalName "has no license"
  }
}

Problem is that the output gives me a list that looks this way:

<user1>@<domain>  <tenant>:SPE_E3 Assigned by group
<user1>@<domain> <tenant>:Microsoft_Teams_Audio_Conferencing_select_dial_out Assigned by group
<user1>@<domain>   
<tenant>:AAD_PREMIUM Assigned by group
<user2>@<domain>   
<tenant>:FLOW_FREE Direct assignment

<tenant>:FLOW_FREE
Direct assignment
<user2>@<domain>  <tenant>:STANDARDPACK Assigned by group
<user2>@<domain>  
<tenant>:Microsoft_Teams_Audio_Conferencing_select_dial_out Assigned by group
<user2>@<domain>  
<tenant>:AAD_PREMIUM Assigned by group
<user2>@<domain>  
<tenant>:MICROSOFT_BUSINESS_CENTER Direct assignment

<tenant>:MICROSOFT_BUSINESS_CENTER
Direct assignment
<user3>@<domain>  <tenant>:ENTERPRISEPACK Assigned by group
<user3>@<domain>   
<tenant>:Microsoft_Teams_Audio_Conferencing_select_dial_out Assigned by group
<user3>@<domain>   
<tenant>:AAD_PREMIUM Assigned by group
<user3>@<domain>   
<tenant>:MICROSOFT_BUSINESS_CENTER Direct assignment

Can anyone help me to somehow sort out the output so it is in csv shape, like with columns
USER,LICENSE,ASSIGNMENT

user1@domain,tenant:plan,<direct or group assignment>

Right now when I export it to csv its a huge mess.
Thank you for any hints.

Tomas

Answer 1

Hi,

You can create a PSCustomObject with the given column headers.

Connect-MsolService
$report = @()
$usersGroup = @()
$userNames2check = get-content <txt file with UPNs of users I need to check>
foreach ($username in $userNames2check)
{
    $usersGroup += Get-MsolUser -UserPrincipalName $username
}
foreach ($user in $usersGroup)
{
  if ($user.Licenses -ne "$null")    
  {
    foreach ($license in $user.Licenses)
    {
        if($license.GroupsAssigningLicense[0].ToString() -eq $user.ObjectId)
        {
            Write-Host -foregroundcolor Cyan $user.UserPrincipalName " " -NoNewline; write-host -foregroundcolor Green $license.AccountSkuId -NoNewline; write-host -ForegroundColor White " Direct assignment"
            $report += [pscustomobject]@{
                    USER = $user.UserPrincipalName
                    LICENSE = $license.AccountSkuId
                    ASSIGNMENT = "Direct assignment"
                }
        }
        else{
            write-host -foregroundcolor Cyan $user.UserPrincipalName " " -NoNewline; write-host -foregroundcolor Yellow $license.AccountSkuId -NoNewline; write-host -ForegroundColor White " Assigned by group"
            $report += [pscustomobject]@{
                    USER = $user.UserPrincipalName
                    LICENSE = $license.AccountSkuId
                    ASSIGNMENT = "Assinged by group"
                }
        }
    }
  }
}

Best Regards,

Ian Xue

---

If the Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello Ian,

many thanks for your help, this works exactly as I needed.

Best regards,

Tomas

Answer 3

Hi Tomas,

Can you please guide me how you ran this script to get output.#

Thanks in advance,

Manu