Office 365: Script to get detailed report of assigned licenses

It’s very common to see Office 365 administrators asking in the community “How can I get a detailed report of the licenses i have assigned on Office 365?

Well it will depend on how detailed you want the report. I’ll detail here two solutions.

1 – Get a report of all licensed users and the AccountSKUId name

To run this report you need to open the Windows Azure Active Directory Module for Windows Powershell, and connect to Office 365. Once connected run the following cmdlet:

Get-MSOLUser -All | select userprincipalname,islicensed,{$_.Licenses.AccountSkuId}| Export-CSV c:\userlist.csv -NoTypeInformation

The above command lists ALL users and not just the ones that have a license. See the output CSV file below. There are ways of filtering the output (i.e export only licensed users), but i will keep this post simple. Let me know if you need something more elaborated.

1

2 – Get a detailed report of the licenses enabled for each user

One of the other requirements, is to know in detail, how many licenses per product do you have enabled, and which users have that license. If you want a detailed list with the users that have Lync Online, Exchange Online, Office Pro Plus (just to give three examples), or any other product that you have on your subscription, enabled or disabled, all you need to do is use the “Export a Licence reconciliation report from Office 365 using Powershell” script available on the Microsoft Gallery.

Again to run this report you need to open the Windows Azure Active Directory Module for Windows Powershell, and connect to Office 365.

Once connected to Office 365 browse into the directory where you saved the script, and run it.

2

The script will prompt you for the Office 365 administrator credentials, and run against all licensed users. By default the script creates a file named “Office_365_Licenses.csv” that will be created on the same directory where the script is. If you want, you can change it by editing the script. There’s also some other things you can change on the script, such as export all users and not just the licensed users, or use the existing credentials cached on your powershell session, instead of prompting you for credentials each time you run it. But again I will keep it simple for now, and if you want to change something on how the script works, let me know.

Now let’s have a look at the detailed output of the script.

3

Let’s now take the user Antonio Vargas as an example. He has all licenses assigned. Let’s see the view from the portal.

4

As you can see the Yammer licenses are assigned by default (hence the “PendingInput” state on the property exported to csv), and all other licenses are assigned, which matches with the success property on the csv. Now below let’s have a look at the user Calvin, which only has the Exchange Online license enabled (and the Yammer by default). All the other licenses are disabled.

5

Again when looking to the licenses that the user Calvin has assigned, via the Office 365 portal, it matches the csv file.

If you want, and because usually the output you will get is a very large csv file, you can use filtering at the csv level to get smaller lists depending on the license type you want the report on.

Any questions let me know, and happy reporting! 🙂

Advertisements

19 thoughts on “Office 365: Script to get detailed report of assigned licenses

  1. Yunar September 28, 2015 / 5:34 am

    Hi,
    I run script: Get-MSOLUser -All | select userprincipalname,islicensed,{$_.Licenses.AccountSkuId}| Export-CSV c:\userlist.csv -NoTypeInformation

    The $_.Licenses.AccountSkuId column is empty, UserPrincipalName IsLicensed columns are OK.
    Please advise.

  2. AMVargas September 30, 2015 / 11:49 pm

    Hi Yunar, what value do you have on the IsLicensed column? If its false then the $_.Licenses.AccountSkuID should be empty, as the user does not have any license assigned.

  3. carloswalker906525629 February 24, 2016 / 5:00 pm

    I downloaded the PowerSHell script mentioned in section 2, but it doesn’t return correct values. We have 129 E3 plans, 4 Visio, and 10 Project… it listed only 119 E3 users, 2 Visio and 8 Project… Seems to skip users once it has found 1 package, without listing what the other packages the user has licensed.

    • carloswalker906525629 February 24, 2016 / 5:13 pm

      I ran the script in section 1, and imagine what is happening is that when the script in section 2 finds a case like this: “username@domain.com TRUE tenant:ENTERPRISEPACK tenant:PROJECTCLIENT” it only lists the 1st package licensed, not both.

      • Peter March 3, 2016 / 12:54 pm

        What is the Solution then ?

  4. AMVargas March 9, 2016 / 12:36 am

    Hi Carlos, sorry to read you had those issues. In fact the script wasn’t designed for that number of plans. Did you managed to get an alternative working? If not I might try and find some time to look at the script and apply some changes. Thanks for the feedback

  5. Ville March 22, 2016 / 8:07 am

    Hello Amvargas,

    I am getting same results as Carlos, have you had time to check on the script?

    Best regards Ville

  6. Davidk May 12, 2016 / 12:38 am

    HI This report is great however I have a question, due to my company having limited licenses and being very frugal on buying new ones we have to swap licenses for users who leave and don’t require them, is it possible in the above report to include a column of the date and time the license was assigned and if possible by which admin user.

  7. DFS July 6, 2016 / 4:51 pm

    Hello,

    Do you have a script that will remove licenses(ex:I have E3 licenses assigned but want to keep Exchange Online (Plan 2) only. I have hundreds of users and I don’t want to do it manually in the portal.

    Thank you!

    • scar July 27, 2016 / 10:20 am

      DFS: you can use Set-MsolUserLicense -userprincipalname “joe@tenant.onmicrosoft.com” -RemoveLicenses “licensename” to remove a license I guess

      • Steve Rackham December 15, 2016 / 12:58 am

        Hi DFS,
        You can do this by disabling portions of the license. This is done with a combination of New-MSOLLicenseOptions and Set-MSOLUserLicenseOptions.

        Get all the service plans with this –
        Get-MsolAccountSku | Where-Object {$_.SkuPartNumber -eq “ENTERPRISEPACK”} | ForEach-Object {$_.ServiceStatus}

        Then disable all bar the EXCHANGE_S_ENTERPRISE
        Example,
        Set the license options,
        $LO = New-MsolLicenseOptions `
        -AccountSkuId “Fakey:ENTERPRISEPACK” `
        -DisabledPlans “FLOW_O365_P2”, “POWERAPPS_O365_P2”, “TEAMS1”, “SHAREPOINTWAC”, “SHAREPOINTENTERPRISE”, “MCOSTANDARD,INTUNE_O365,YAMMER_ENTERPRISE,SWAY”

        then apply,

        # Set the MSOL User License.
        Set-MsolUserLicense `
        -UserPrincipalName fakey.mcfakerson@fakey.domain `
        -AddLicenses “Fakey:ENTERPRISEPACK”,”Fakey:EMS” `
        -LicenseOptions $LO

        and check,

        (Get-MsolUser -UserPrincipalName fakey.mcfakerson@fakey.domain).Licenses.ServiceStatus

        Hope this helps,
        S.

  8. scar July 27, 2016 / 10:19 am

    DFS: you can use Set-MsolUserLicense -userprincipalname “joe@tenant.onmicrosoft.com” -RemoveLicenses “licensename” to remove a license I guess

  9. NIL August 19, 2016 / 12:56 pm

    From script 2, I need a report to extract E1 users for whom only OneDrive is enabled.
    Can you please help me with this.

    • Filip March 31, 2017 / 8:53 am

      Hi Nil, have you found an answer to your question ? I am interested too.

  10. Donna Smith February 1, 2017 / 11:03 am

    Is it possible to create a script to check which accounts are licensed….but it reads a list of users from csv file and checks these then provides the output.
    I have several different s/s which before removing accounts i need a script to tell me which accounts are on prem and which are 365 licensed

    • AMVargas February 2, 2017 / 12:58 am

      Hi Donna, so you want the script to read from a pre defined list of users you have on a Csv and list if those are licenses or not?

  11. ValSal June 7, 2017 / 6:36 pm

    Hi Guys – I am working to created PowerShell script to go and read UPN form an csv file and add new users on that list to a security group in O365 so they get EMS license. My issue is that csv file will have already proceeded users and I need my script to skip all processed users and move only new entries. Any ideas?
    Thx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s