by Lou Goban
IT Application Professional at Syntax

Get licensed users with particular license assigned

Today I had a task to get all licensed users, who have Teams license assigned and their SamAccountName start with “C”.

For export I used Import-Excel module which you can find on PowerShell gallery.

To change license or service plan you just need to change the 5th line from “TEAMS” to something else.


Write-Progress -Activity "Getting users.."
$allTeamsUsers = Get-MsolUser -All | `
                    Where-Object {($_.isLicensed -eq $true) -and ($_.Licenses.ServiceStatus.ServicePlan.ServiceName -match "TEAMS")} | `
                    Select-Object DisplayName,UserPrincipalName
$allData = @()
foreach ($user in $allTeamsUsers) {
    Write-Progress -Activity "User:" $user.DisplayName
    $mbxName = Get-Mailbox $user.UserPrincipalName | Select-Object Name, UserPrincipalName
    If ($mbxName.Name -like "C*") {
        $cUser = @()
        Write-Progress -Activity "Processing.." $mbxName.Name
        # Name
        $cUser += $mbxName.Name
        # Get Alias
        $mbxAlias = Get-Mailbox $user.UserPrincipalName | Select-Object Alias
        $cUser += $mbxAlias.Alias
        # Get License
        $licAssigned = Get-MsolUser -UserPrincipalName $user.UserPrincipalName | `
                        Select-Object Licenses -ExpandProperty Licenses | `
                        Select-Object AccountSkuId
        # Create a string from a License property
        $lic = [string]$licAssigned.AccountSkuId
        $cUser += $lic
        $cUserTable = [pscustomobject]@{
            Name = $mbxName.Name
            Alias = $mbxAlias.Alias
            License = $licAssigned.AccountSkuId
        $allData += $cUserTable
$allData | Export-Excel -Path "$env:USERPROFILE\Desktop\export-data.xlsx" -FreezeTopRow -BoldTopRow -AutoSize -WorksheetName "Users with Teams"

Leave the first comment

Related posts