donderdag 6 oktober 2016

Get-MsolUserLicense.ps1 : Generate a detailed report license types and service plans in use for every user in Office 365

This script generates a detailed report of all license types and service plans in use by every user in Office 365. You will get one Excel sheet (CSV) per available license type in use.

    makes a report of license type and service plans per use , and saves one Excel-sheet (CSV) per uses license type.

    Original source:

    created by Alan Byrne
    modified 20161006 by

    Changed separator from comma to semi-comma, fixes formatting errors whene displayname contains comma (very common).
    Changed: separate output file , one for each license type.
    Changed: added timestamp to filename. 
    Changed: Fetching all users once, instead of every license type, givea huge speed boost.

$VerbosePreference = 'Continue'    # Makes verbose meldingen zichtbaar : Modify to your needs
# The Reports will be written to files in the current working directory

# Connect to Microsoft Online IF NEEDED
#write-host "Connecting to Office 365..."
#Import-Module MSOnline
#Connect-MsolService -Credential $Office365credentials

# Get a list of all licences that exist within the tenant
$licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1}

Write-Verbose "License types are:" 
$lts = $licensetype| select -expandproperty accountskuid | Format-Table -Autosize | Out-String
Write-Verbose $lts

Write-Verbose "Getting all users (may take a while) ..."
$allusers = Get-MsolUser -all 
Write-Verbose ("There are " + $allusers.count + " users in total")

# Loop through all licence types found in the tenant
foreach ($license in $licensetype) 
 # Build and write the Header for the CSV file
    $LicenseTypeReport = "Office365_" + ($license.accountskuid -replace ":","_") + "_" + (Get-Date -Format "yyyyMMdd-HHmmss") + ".csv"
    Write-Verbose ("New file: "+ $LicenseTypeReport)

 $headerstring = "DisplayName;UserPrincipalName;JobTitle;Office;AccountSku"
 foreach ($row in $($license.ServiceStatus)) 
  $headerstring = ($headerstring + ";" + $row.ServicePlan.servicename)
 Out-File -FilePath $LicenseTypeReport -InputObject $headerstring -Encoding UTF8 -append
 write-Verbose ("Gathering users with the following subscription: " + $license.accountskuid)

 # Gather users for this particular AccountSku
 $users = $allusers | where {$_.isLicensed -eq "True" -and $_.licenses.accountskuid -contains $license.accountskuid}

 # Loop through all users and write them to the CSV file
 foreach ($user in $users) {
        $thislicense = $user.licenses | Where-Object {$_.accountskuid -eq $license.accountskuid}
        $datastring = (($user.displayname -replace ","," ") + ";" + $user.userprincipalname + ";" + $user.Title + ";" + $user.Office + ";" + $license.SkuPartNumber)
  foreach ($row in $($thislicense.servicestatus)) {   
   # Build data string
   $datastring = ($datastring + ";" + $($row.provisioningstatus))
  Out-File -FilePath $LicenseTypeReport -InputObject $datastring -Encoding UTF8 -append

write-Verbose ("Script Completed.")

6 opmerkingen:

  1. Hi Sikkepitje, I have tried your script but all csv files are blank. This it the output from my PowerShell:

    DETAILED: License types are:
    DETAILED: Getting all users (may take a while) ...
    DETAILED: There are 1223 users in total
    DETAILED: New file: C:\temp\Office365_contoso_VISIOCLIENT_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:VISIOCLIENT
    DETAILED: New file: C:\temp\Office365_contoso_PROJECTCLIENT_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:PROJECTCLIENT
    DETAILED: New file: C:\temp\Office365_contoso_POWER_BI_PRO_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:POWER_BI_PRO
    DETAILED: New file: C:\temp\Office365_contoso_ENTERPRISEPACK_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:ENTERPRISEPACK
    DETAILED: New file: C:\temp\Office365_contoso_EXCHANGESTANDARD_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:EXCHANGESTANDARD
    DETAILED: New file: C:\temp\Office365_contoso_POWER_BI_STANDARD_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:POWER_BI_STANDARD
    DETAILED: New file: C:\temp\Office365_contoso_STANDARDPACK_20161117-124312.csv
    DETAILED: Gathering users with the following subscription: contoso:STANDARDPACK
    DETAILED: Script Completed.

    When I open one of the CSV extracted, this is the result:
    DisplayName UserPrincipalName JobTitle Office AccountSku INTUNE_O365 EXCHANGE_S_STANDARD

    Any ideas?
    Thanks in advance.

    1. I really don't know. I based this script on the one on and just modified that one to output one file per licensetype, because I found that a bit handier. Try to run that one and see if you have success there.

    2. I tried also the "original one" but without success. Extracted reports are "a full of blank fields" :-\
      Thanks anyway.

  2. Awesome script, works perfectly, many thanks.........