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; that 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.
Luckily, we have PowerShell to get his report. Using this script you can get a detailed report for: A list of users (.csv file required). 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. 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.
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 Read the full article