PowerShell List all O365 Users and Associated Licenses

0
votes

I am trying to get a list of all O365 users from a tenant, and the individual licenses that are assigned to them. The output needs to be in the following format (note that users with more than one license are listed multiple times):

UPN | License user1 | standardpack user1 | plannerstandalone user2 | enterprisepack user2 | power_bi_standard user2 | ems

I can get the information using this:

$groupOfUsers = Get-MsolUser -all | where { $_.IsLicensed -eq $True }
$licenses = foreach ($individual in $groupOfUsers) 
{ 
    $allLicenses = $individual.licenses | Select -ExpandProperty AccountSkuId
        foreach ($lic in $allLicenses)
            { write-host = $individual.UserPrincipalName $lic }
}

What I would like to do is have this output in a format that I can eventually import into SQL - so a table format or something that I can export to csv would be a great start.

Any help would be appreciated.

2
powershelloffice365azure-powershell

2 Answers

2
votes

try this

$groupOfUsers = Get-MsolUser -all | where { $_.IsLicensed -eq $True }

$results = foreach ($user in $groupOfUsers) {
    $licenses = $user.licenses.accountskuid
    foreach ($license in $licenses) {
        [pscustomobject]@{
            UPN = $user.userprincipalname
            License = $license
        }
    }
}

$results

$results | Export-Csv c:\temp\o365licenses.csv -NoTypeInformation

start c:\temp\0365licenses.csv
0
votes

get the list of licenses using powershell Get-MsolAccountSku

AND Get-MsolUser | Where-Object {($_.licenses).AccountSkuId -match "EnterprisePremium"}