Weekend Scripter: Use PowerShell to Manage Office 365

Summary: Use Windows PowerShell to manage your Office 365 environment.

Microsoft Scripting Guy, Ed Wilson, is here. Today we have as our guest blogger, Alan Byrne.

Photo of Alan Byrne

Alan is the cofounder of Cogmotive Reports, an Office 365 Reporting and Analytics web application that offers a range of automated Office 365 reports on mailbox sizes, user log-in times, mobile devices, license usage, and several other Office 365 metrics. He has an extensive background in design and administration for Windows and Exchange Server, and he tries to script every administration task he comes across. More recently, Alan has been applying these skills to Microsoft Office 365.

Twitter: @cogmotive
Website: Cogmotive Reports

Export a license reconciliation report

As more companies move to Microsoft Office 365, IT managers need to find a way to reconcile their cloud-based licenses with their actual user numbers. No one wants to pay for licenses that they aren’t using!

If you log in to the Microsoft Office 365 portal, you can see how many licenses you’ve purchased and how many are in use. Unfortunately, there is no way to easily tell which license has been assigned to which user account.

Image of tab

To get a detailed license usage report we need to do some fancy Office 365 Windows PowerShell scripting, which I’ll explain here today.

Before we do that, we need to understand how licenses work in Office 365. An Office 365 tenant can hold many different license types; each license type is called a subscription. This allows administrators to assign a feature set to one group of users that is different than those of another group. On the Microsoft Office 365 Plans and Pricing page, you can find an explanation of what you can access with each subscription.

Within each subscription there are a set of services that you can turn on or off for each user.

Image of tab

So if you have a lot of users, how can you tell which features are enabled for each person? Well, you can click each user one-by-one and note it—or you can script it.

Microsoft allows you to administer Office 365 remotely by using Windows PowerShell. The script that I am presenting today uses this functionality to generate a CSV file that you can import into Microsoft Excel to view and filter your license types.

For this script to work, you need to install the Microsoft Online Services Module for Office 365. This allows your local Windows PowerShell to utilize the Microsoft Office 365-specific cmdlets.

To download this module, click the link that matches your operating system version (32-bit or 64-bit): Install the Windows Azure AD Module

The script

The script begins by defining a look-up table that turns the Microsoft Subscription SKUs into something that we mere mortals can understand. We use this hash table when building the CSV file later in the script.

# Define Hashtables for lookup

$Sku = @{

            "DESKLESSPACK" = "Office 365 (Plan K1)"

            "DESKLESSWOFFPACK" = "Office 365 (Plan K2)"

            "LITEPACK" = "Office 365 (Plan P1)"

            "EXCHANGESTANDARD" = "Office 365 Exchange Online Only"

            "STANDARDPACK" = "Office 365 (Plan E1)"

            "STANDARDWOFFPACK" = "Office 365 (Plan E2)"

            "ENTERPRISEPACK" = "Office 365 (Plan E3)"

            "ENTERPRISEPACKLRG" = "Office 365 (Plan E3)"

            "ENTERPRISEWITHSCAL" = "Office 365 (Plan E4)"

            "STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"

            "STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"

            "ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"

            "ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"

            "STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"

            "STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"

            "ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"

            "ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"

            "ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"

            "STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"


We also set the name and path of the file we will use for our License Usage Report CSV file. You can change this part if you want the file to be named or saved differently.

# The Output will be written to this file in the current working directory

$LogFile = "Office_365_Licenses.csv"

Now it’s time to import the Office 365 Windows PowerShell cmdlets and prompt you for your Office 365 user name and password. You should enter the details for an Administrator account for your tenant, which the script will then use to connect to Office 365.

# Connect to Microsoft Online

Import-Module MSOnline

Connect-MsolService -Credential $Office365credentials

When we’re connected to Office 365, we request a list of the subscriptions that you’ve got attached to your tenant. We use the Office 365 Get-MSOLAccountSku cmdlet for this, and we’re only interested in subscriptions that have been assigned to at least one user.

$licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1}

The subscriptions are stored in the $licensetype variable, which we loop through to find which subscriptions are assigned to each user.

foreach ($license in $licensetype)

Before we get started on the users, we write a header line into the CSV file, which explains what each column means. This header will vary depending on which subscriptions you have attached to your tenant. Each subscription gets a new header in the output file. We use a switch statement with the wildcard parameter to make sure that we’re displaying the correct product. This is one of the most powerful switch statements I’ve come across in any scripting language.

# Build and write the header for the CSV file

            $headerstring = "DisplayName,UserPrincipalName,AccountSku"    

            foreach ($row in $($license.ServiceStatus)) {


                        # Build header string

                        switch -wildcard ($($row.ServicePlan.servicename))


                                    "EXC*" { $thisLicense = "Exchange Online" }

                                    "MCO*" { $thisLicense = "Lync Online" }

                                    "LYN*" { $thisLicense = "Lync Online" }

                                    "OFF*" { $thisLicense = "Office Profesional Plus" }

                                    "SHA*" { $thisLicense = "Sharepoint Online" }

                                    "*WAC*" { $thisLicense = "Office Web Apps" }

                                    "WAC*" { $thisLicense = "Office Web Apps" }

                                    default { $thisLicense = $row.ServicePlan.servicename }



                        $headerstring = ($headerstring + "," + $thisLicense)         }

            Out-File -FilePath $LogFile -InputObject $headerstring -Encoding UTF8 –append

We’re now ready to start collecting the user data. We use the Get-MSOLUser cmdlet to return a collection of user objects of all the licensed users within the subscription we’re currently processing.

$users = Get-MsolUser -all | where {$_.isLicensed -eq "True" -and $_.licenses[0].accountskuid.tostring() -eq $license.accountskuid}

With the $users variable now full of users, we loop through each one of them and write their details to the CSV report. This is where we use the hash table we created earlier to write the licenses in English.

# Loop through all users and write them to the CSV file

            foreach ($user in $users) {


                        $datastring = ($user.displayname + "," + $user.userprincipalname + "," + $Sku.Item($user.licenses[0].AccountSku.SkuPartNumber))


                        foreach ($row in $($user.licenses[0].servicestatus)) {


                                    # Build data string

                                    $datastring = ($datastring + "," + $($row.provisioningstatus))



                        Out-File -FilePath $LogFile -InputObject $datastring -Encoding UTF8 –append


To use the script for yourself:

  1. Run the Windows PowerShell script.
  2. Enter your Office 365 Admin credentials.
  3. Open the licencing report CSV file that we generated in Excel, and you should see something like the following spreadheet.

Image of spreadsheet

Armed with this license report, you could potentially save your company money by identifying incorrectly assigned licenses, such as users who have left the organization or accidentally licensed shared mailboxes.

The full script is available to view and download in the Script Center Repository.


Thank you Alan, this is great information.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy