As we continue expanding our Office 365 deployment from OfficeProPlus to include Exchange Online and Archiving, it’s become increasingly necessary to reconcile and report on current AD Synced and cloud users with their specific subscription and licenses quickly and efficiently. I want to post a quick PowerShell one-liner that generates a report on current Office 365 users’ assigned licenses and exports to csv and/or MS Excel.

1) Connect to the Microsoft Online Service (Office 365 Tenant):

Use the below cmdlet to connect to the O 365 cloud service. Enter valid credentials at the prompt:

PS C:\Scripts> Connect-MsolService

2) Use the Get-MsolUser cmdlet to retrieve all current users and pipe the result to the where-object cmdlet:

PS C:\Scripts> Get-MsolUser | ? { $_.Licenses.AccountSkuid -ne $null} | Select-Object -Property DisplayName, @{Name="AccountSkuID";e={$_.licenses.AccountSkuid}}| Export-Csv -Path c:\Office365.csv -NoTypeInformation

The above script filters the result of the Get-MsolUser cmdlet by piping it to the where-object cmdlet. The resulting collection is formatted and displayed with the help of a calculated property (AccountSkuID) and exported to a CSV file. Below is a screenshot of the report:

I intend to expand the script in the coming weeks as we add new Office 365 services.