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.
<#

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

    Original source: https://gallery.technet.microsoft.com/scriptcenter/Export-a-Licence-b200ca2a?tduid=(26fc5a009171934296bd78c7f4dd6590)(256380)(2459594)(TnL5HPStwNw-0Z3.3otQ5VeALpBrI1CXBg)()

    created by Alan Byrne
    modified 20161006 by p.wiegmans@bonhoeffer.nl/sikkepitje@hotmail.com

    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.")

4 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: contoso:VISIOCLIENT
    contoso:PROJECTCLIENT
    contoso:POWER_BI_PRO
    contoso:ENTERPRISEPACK
    contoso:EXCHANGESTANDARD
    contoso:POWER_BI_STANDARD
    contoso:STANDARDPACK
    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
    EXCHANGESTANDARD

    Any ideas?
    Thanks in advance.

    BeantwoordenVerwijderen
    Reacties
    1. I really don't know. I based this script on the one on https://gallery.technet.microsoft.com/scriptcenter/Export-a-Licence-b200ca2a?tduid=(26fc5a009171934296bd78c7f4dd6590)(256380)(2459594)(TnL5HPStwNw-0Z3.3otQ5VeALpBrI1CXBg)() 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.

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

      Verwijderen