Detailed license report for Office 365 users.

How to get detailed License Reports for Office 365 users?

If you are reading this post, you were probably searching the web on how to get a detailed license report for your users in Office 365. Well you have come to the right place!

Although you can get a license report from the Office 365 Admin Center; this report will give you the license packs assigned to each user, and not a list of services that are enabled or disabled within each license pack. [https://admin.microsoft.com/Adminportal/Home?source=applauncher#/users]

Luckily, we have PowerShell to get his report. I have created a script that can get a detailed report for a list of users (.csv file required) and for all users who have licenses assigned to them.

About the Script and Output Sample:

This script was created for organizations that have multiple license packs (SKU with services disabled or enabled). It is a tedious task for an administrator to pull a report of or find out who has what license. If you assign licenses via Group-Based licenses, then it might be difficult to find out which group is responsible for assigning the custom license pack.

This script is simplified to a point that it requires minimal inputs and should be run as is.

The output file will have the following information: User’s UserPrincipalName, License Pack, Services within the license pack, Status of each service and if I you have assigned the license via Groups the ObjectID of the group (this column will be blank if you are not using group-based licenses). You can then filter the results using Excel’s built in filters.

Script Execution:

  • Run PowerShell as Admin and connect to MSOL service by running Connect-MsolService. For details on how to connect to MSOL reference the link: Connect to Office 365 PowerShell: https://docs.microsoft.com/en-us/office365/enterprise/powershell/connect-to-office-365-powershell
  • Once you run the script you will be prompted with 2 choices:
    1. All Users: Use this option if you want to get a detailed report for all users in your organization. The script would prompt you for a location to export the file.
    2. Select Users: Use this option if you want to run a report for a select set of users. To use this option, you would need a .csv file with a list of users. The column name would need to be UserPrincipalName. You would be prompted for the Input file location and the export location for the file. A sample input file is attached (file name: userlist)

About the Script. (how does it work):

For this is have skipped the beautification e.g. Adding the choice button, headers, write and read host etc.

The script first gets a list of users and saves it in a constant

$users = get-msoluser -All | Where-Object { $_.isLicensed -eq "TRUE" }
  • It then gets the license pack (SKU) for each user within $users
$users | foreach{
$user = Get-MsolUser -UserPrincipalName $_.userprincipalname
$Licenses = ($user).licenses
  • We then get a list of services within each license pack and the Groups responsible to assign the license if applicable.
foreach ($license in $Licenses)
{
$AccountSkuId = ($license).accountskuid
$GroupsAssigningLicense = ($License).GroupsAssigningLicense
$servicestatuses = $license.ServiceStatus
$Plans = ($license).ServiceStatus.serviceplan.ServiceName
$statuses = ($license).ServiceStatus.ProvisioningStatus
  • Lastly, we get the status of each service within the license pack.
Foreach($servicestatus in $servicestatuses)
{
$plan=$servicestatus.ServicePlan.ServiceName
$status= $servicestatus.ProvisioningStatus
  • We then output these results in a .csv file.
Out-File -FilePath $OutputFile -InputObject "$($user.UserPrincipalName),$($AccountSkuId),$($Plan),$($status),$($GroupsAssigningLicense)" -Encoding UTF8 -append
write-host "`t$($user.UserPrincipalName),$($AccountSkuId),"$($Plan)",$($status),$($GroupsAssigningLicense)"

Download the Script and .csv file:

Technet: https://gallery.technet.microsoft.com/How-to-get-detailed-0f4934f5