[Function] Export Report of Unused Office 365 Licenses Across Multiple Tenants or a Single Tenant Using Friendly License Names

March 26, 2018 Brad Wyatt Comments 8 comments

Table of Contents

Even if a license is not assigned to an Exchange Online object does not mean it’s not being charged back to the tenant. If you work for a MSP or IT Consultant you may want to audit your clients Office 365 tenant for any licenses that are not assigned so they are not paying for licenses that they do not need.

While looking up ways to accomplish this task I came across this script from GCITS. If you have never read any of GCITS’s knowledge-base write ups I recommend you start following their blog posts as they have some of the best write ups, especially for MSP’s, and IT Consultants. The problem with the script is that it exported the unused license count for all licenses including licenses that had zero unused licenses. It also exported the license SKU instead of converting it to a friendly name. If you have to give this report to someone who hasn’t memorized all the SKU’s, and what license it correlates to, then the report isn’t as helpful as it could be. By converting it to it’s proper friendly name you can easily remove the licenses since Microsoft and other resellers like Sherweb also don’t display licenses in the portal by SKU.

I also wanted to make the script into a PowerShell Function instead of a flat script. This would allow me to save the function into my PowerShell profile, and also give it to others without having to modify anything in the script itself. The CSV output path would be a parameter instead of a set item in the script you would have to modify. It would also allow you to run it across a single tenant or all tenants using a switch parameter.

Using delegated administration in Office 365 you can quickly manage multiple tenants while only needing one login. This script will allow you to use delegated administration to get an unused license report across all your tenants or just a single tenant.

What You Need:

If you are running this across multiple tenants make sure you are a delegated admin on the tenants
PowerShell

The Script

If you just want to download and run the script without reading the inner-workings of it then feel free to scroll to the bottom of the page and copy and save the script.

Help

The Function we are creating is Get-UnusedLicenseReport. By saving this into my PowerShell profile I can call the function when launch PowerShell. The Function also comes with detailed Help.

Parameters

The script has 3 parameters with only 1 being mandatory.

CSVPath: The path where the csv report will be saved to; required.

Username: The Office 365 username; not required.

TenantReport: Switch parameter, Include this if you want to run against all tenants instead of a single tenant; not required.

Display

The shell will display the licenses for each tenant and the unused license count. If it’s green then there are 0 unused licenses and there is no more steps involved. If there are more than 0 unused licenses it will be displayed in magenta and will be exported to the result csv file. If the hash table cannot look up a SKU to a friendly name it will display red and export the SKU name to the result csv file.

The Shell when ran against a single tenant

The Shell when running against multiple tenants

CSV Report

The CSV report will include the company name, friendly license name, active units, warning units, consumed units, and unused units.

Script

Function Get-UnusedLicenseReport
{
  <#
  .SYNOPSIS
  Gets a report of unused licenses in a single tenant or across all tenants you are a delegated administrator for 
  .DESCRIPTION
  Gathers a report of all unused licenses across multiple tenants using delegated admin or a single tenant. It converts the SkuID to a friendly name, instead of getting "LITEPACK" you will get "Office 365 (Plan P1)". 
  .EXAMPLE
  Get-UnusedLicenseReport -CSVPath "C:\Reports\LicenseReport.csv" 
  .EXAMPLE
  Get-UnusedLicenseReport -CSVPath "C:\Reports\LicenseReport.csv" -TenantReport
  .PARAMETER CSVPath
  The path where the CSV Report will be saved to.
  .PARAMETER Username
  Office 365 username
  .PARAMETER TenantReport
  A switch to either run on a single tenant or across all tenants you are a delegated administrator for. If you just want to run against a single tenant you do not need to include this parameter.
  .NOTES
	Author: Bradley Wyatt
	Date: 3/26/2018
	Version: 1.0.0
	Website: https://www.thelazyadministrator.com
  #>
	[CmdletBinding()]
	Param (
		[Parameter(Mandatory = $True, Position = 1, ValueFromPipeline = $false)]
		[String]$CSVPath,
		[Parameter(Mandatory = $False, Position = 2, ValueFromPipeline = $false)]
		[String]$Username,
		[Parameter(Mandatory = $False, Position = 3, ValueFromPipeline = $false)]
		[Switch]$TenantReport
	)
	$Sku = @{
		"O365_BUSINESS_ESSENTIALS"			     = "Office 365 Business Essentials"
		"O365_BUSINESS_PREMIUM"				     = "Office 365 Business Premium"
		"DESKLESSPACK"						     = "Office 365 (Plan K1)"
		"DESKLESSWOFFPACK"					     = "Office 365 (Plan K2)"
		"LITEPACK"							     = "Office 365 (Plan P1)"
		"EXCHANGESTANDARD"					     = "Office 365 Exchange Online Only"
		"STANDARDPACK"						     = "Enterprise Plan E1"
		"STANDARDWOFFPACK"					     = "Office 365 (Plan E2)"
		"ENTERPRISEPACK"						 = "Enterprise Plan E3"
		"ENTERPRISEPACKLRG"					     = "Enterprise Plan E3"
		"ENTERPRISEWITHSCAL"					 = "Enterprise 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)"
		"VISIOCLIENT"						     = "Visio Pro Online"
		"POWER_BI_ADDON"						 = "Office 365 Power BI Addon"
		"POWER_BI_INDIVIDUAL_USE"			     = "Power BI Individual User"
		"POWER_BI_STANDALONE"				     = "Power BI Stand Alone"
		"POWER_BI_STANDARD"					     = "Power-BI Standard"
		"PROJECTESSENTIALS"					     = "Project Lite"
		"PROJECTCLIENT"						     = "Project Professional"
		"PROJECTONLINE_PLAN_1"				     = "Project Online"
		"PROJECTONLINE_PLAN_2"				     = "Project Online and PRO"
		"ProjectPremium"						 = "Project Online Premium"
		"ECAL_SERVICES"						     = "ECAL"
		"EMS"								     = "Enterprise Mobility Suite"
		"RIGHTSMANAGEMENT_ADHOC"				 = "Windows Azure Rights Management"
		"MCOMEETADV"							 = "PSTN conferencing"
		"SHAREPOINTSTORAGE"					     = "SharePoint storage"
		"PLANNERSTANDALONE"					     = "Planner Standalone"
		"CRMIUR"								 = "CMRIUR"
		"BI_AZURE_P1"						     = "Power BI Reporting and Analytics"
		"INTUNE_A"							     = "Windows Intune Plan A"
		"PROJECTWORKMANAGEMENT"				     = "Office 365 Planner Preview"
		"ATP_ENTERPRISE"						 = "Exchange Online Advanced Threat Protection"
		"EQUIVIO_ANALYTICS"					     = "Office 365 Advanced eDiscovery"
		"AAD_BASIC"							     = "Azure Active Directory Basic"
		"RMS_S_ENTERPRISE"					     = "Azure Active Directory Rights Management"
		"AAD_PREMIUM"						     = "Azure Active Directory Premium"
		"MFA_PREMIUM"						     = "Azure Multi-Factor Authentication"
		"STANDARDPACK_GOV"					     = "Microsoft Office 365 (Plan G1) for Government"
		"STANDARDWOFFPACK_GOV"				     = "Microsoft Office 365 (Plan G2) for Government"
		"ENTERPRISEPACK_GOV"					 = "Microsoft Office 365 (Plan G3) for Government"
		"ENTERPRISEWITHSCAL_GOV"				 = "Microsoft Office 365 (Plan G4) for Government"
		"DESKLESSPACK_GOV"					     = "Microsoft Office 365 (Plan K1) for Government"
		"ESKLESSWOFFPACK_GOV"				     = "Microsoft Office 365 (Plan K2) for Government"
		"EXCHANGESTANDARD_GOV"				     = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
		"EXCHANGEENTERPRISE_GOV"				 = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
		"SHAREPOINTDESKLESS_GOV"				 = "SharePoint Online Kiosk"
		"EXCHANGE_S_DESKLESS_GOV"			     = "Exchange Kiosk"
		"RMS_S_ENTERPRISE_GOV"				     = "Windows Azure Active Directory Rights Management"
		"OFFICESUBSCRIPTION_GOV"				 = "Office ProPlus"
		"MCOSTANDARD_GOV"					     = "Lync Plan 2G"
		"SHAREPOINTWAC_GOV"					     = "Office Online for Government"
		"SHAREPOINTENTERPRISE_GOV"			     = "SharePoint Plan 2G"
		"EXCHANGE_S_ENTERPRISE_GOV"			     = "Exchange Plan 2G"
		"EXCHANGE_S_ARCHIVE_ADDON_GOV"		     = "Exchange Online Archiving"
		"EXCHANGE_S_DESKLESS"				     = "Exchange Online Kiosk"
		"SHAREPOINTDESKLESS"					 = "SharePoint Online Kiosk"
		"SHAREPOINTWAC"						     = "Office Online"
		"YAMMER_ENTERPRISE"					     = "Yammer for the Starship Enterprise"
		"EXCHANGE_L_STANDARD"				     = "Exchange Online (Plan 1)"
		"MCOLITE"							     = "Lync Online (Plan 1)"
		"SHAREPOINTLITE"						 = "SharePoint Online (Plan 1)"
		"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ"	 = "Office ProPlus"
		"EXCHANGE_S_STANDARD_MIDMARKET"		     = "Exchange Online (Plan 1)"
		"MCOSTANDARD_MIDMARKET"				     = "Lync Online (Plan 1)"
		"SHAREPOINTENTERPRISE_MIDMARKET"		 = "SharePoint Online (Plan 1)"
		"OFFICESUBSCRIPTION"					 = "Office ProPlus"
		"YAMMER_MIDSIZE"						 = "Yammer"
		"DYN365_ENTERPRISE_PLAN1"			     = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
		"ENTERPRISEPREMIUM_NOPSTNCONF"		     = "Enterprise E5 (without Audio Conferencing)"
		"ENTERPRISEPREMIUM"					     = "Enterprise E5 (with Audio Conferencing)"
		"MCOSTANDARD"						     = "Skype for Business Online Standalone Plan 2"
		"PROJECT_MADEIRA_PREVIEW_IW_SKU"		 = "Dynamics 365 for Financials for IWs"
		"STANDARDWOFFPACK_IW_STUDENT"		     = "Office 365 Education for Students"
		"STANDARDWOFFPACK_IW_FACULTY"		     = "Office 365 Education for Faculty"
		"EOP_ENTERPRISE_FACULTY"				 = "Exchange Online Protection for Faculty"
		"EXCHANGESTANDARD_STUDENT"			     = "Exchange Online (Plan 1) for Students"
		"OFFICESUBSCRIPTION_STUDENT"			 = "Office ProPlus Student Benefit"
		"STANDARDWOFFPACK_FACULTY"			     = "Office 365 Education E1 for Faculty"
		"STANDARDWOFFPACK_STUDENT"			     = "Microsoft Office 365 (Plan A2) for Students"
		"DYN365_FINANCIALS_BUSINESS_SKU"		 = "Dynamics 365 for Financials Business Edition"
		"DYN365_FINANCIALS_TEAM_MEMBERS_SKU"	 = "Dynamics 365 for Team Members Business Edition"
		"FLOW_FREE"							     = "Microsoft Flow Free"
		"POWER_BI_PRO"						     = "Power BI Pro"
		"O365_BUSINESS"						     = "Office 365 Business"
		"DYN365_ENTERPRISE_SALES"			     = "Dynamics Office 365 Enterprise Sales"
		"RIGHTSMANAGEMENT"					     = "Rights Management"
		"PROJECTPROFESSIONAL"				     = "Project Professional"
		"VISIOONLINE_PLAN1"					     = "Visio Online Plan 1"
		"EXCHANGEENTERPRISE"					 = "Exchange Online Plan 2"
		"DYN365_ENTERPRISE_P1_IW"			     = "Dynamics 365 P1 Trial for Information Workers"
		"DYN365_ENTERPRISE_TEAM_MEMBERS"		 = "Dynamics 365 For Team Members Enterprise Edition"
		"CRMSTANDARD"						     = "Microsoft Dynamics CRM Online Professional"
		"EXCHANGEARCHIVE_ADDON"				     = "Exchange Online Archiving For Exchange Online"
		"EXCHANGEDESKLESS"					     = "Exchange Online Kiosk"
		"SPZA_IW"							     = "App Connect"
		"WINDOWS_STORE"						     = "Windows Store for Business"
		"MCOEV"								     = "Microsoft Phone System"
		"VIDEO_INTEROP"						     = "Polycom Skype Meeting Video Interop for Skype for Business"
		"SPE_E5"								 = "Microsoft 365 E5"
		"SPE_E3"								 = "Microsoft 365 E3"
		"ATA"								     = "Advanced Threat Analytics"
		"MCOPSTN2"							     = "Domestic and International Calling Plan"
		"FLOW_P1"							     = "Microsoft Flow Plan 1"
		"FLOW_P2"							     = "Microsoft Flow Plan 2"
	}
	$Cred = Get-Credential -UserName $Username -Message "Please enter your Office 365 credentials"
	Connect-MSOLService -Credential $Cred
	Import-Module MSOnline
	
	#Global Vars
	$msolAccountSkuCsv = $CSVPath
	
	If ($PSBoundParameters.ContainsKey('TenantReport'))
	{
		
		Write-Host "Getting unused license report for all tenants..." -ForegroundColor Yellow
		$clients = Get-MsolPartnerContract -All
		
		$licenses = Get-MsolAccountSku
		ForEach ($client in $clients)
		{
			$licenses = Get-MsolAccountSku -TenantId $client.TenantId
			ForEach ($license in $licenses)
			{
				$UnusedUnits = $license.ActiveUnits - $license.ConsumedUnits
				$LicenseItem = $License.AccountSkuId -split ":" | Select-Object -Last 1
				$LicenseFallBackName = $License.AccountSkuId
				$TextLic = $Sku.Item("$LicenseItem")
				$ClientName = $client.Name
				If (!($TextLic))
				{
					Write-Host "Error: The Hash Table has no match for $LicenseItem for $ClientName!" -ForegroundColor Red
					If ($UnusedUnits -gt 0)
					{
						Write-Host "$LicenseFallBackName for $ClientName has $UnusedUnits unused licenses" -ForegroundColor Magenta
						
						$license | Select-Object @{ Name = "Client"; Expression = { "$ClientName" } }, @{ Name = "License"; Expression = { "$LicenseFallBackName" } }, ActiveUnits, WarningUnits, ConsumedUnits, @{ Name = "Unused"; Expression = { "$UnusedUnits" } } | Export-Csv -Path $msolAccountSkuCsv -Append -NoTypeInformation
					}
					
					Else
					{
						Write-Host "$LicenseFallBackName for $ClientName has $UnusedUnits unused licenses" -ForegroundColor Green
						
					}
				}
				Else
				{
					If ($UnusedUnits -gt 0)
					{
						Write-Host "$TextLic for $ClientName has $UnusedUnits unused licenses" -ForegroundColor Magenta
						
						$license | Select-Object @{ Name = "Client"; Expression = { "$ClientName" } }, @{ Name = "License"; Expression = { "$TextLic" } }, ActiveUnits, WarningUnits, ConsumedUnits, @{ Name = "Unused"; Expression = { "$UnusedUnits" } } | Export-Csv -Path $msolAccountSkuCsv -Append -NoTypeInformation
					}
					
					Else
					{
						Write-Host "$TextLic for $ClientName has $UnusedUnits unused licenses" -ForegroundColor Green
						
					}
				}
			}
		}
	}
	Else
	{
		Write-Host "Getting unused license report..." -ForegroundColor Yellow
		$licenses = Get-MsolAccountSku
		ForEach ($license in $licenses)
		{
			$UnusedUnits = $license.ActiveUnits - $license.ConsumedUnits
			$LicenseItem = $License.AccountSkuId -split ":" | Select-Object -Last 1
			$LicenseFallBackName = $License.AccountSkuId
			$TextLic = $Sku.Item("$LicenseItem")
			If (!($TextLic))
			{
				Write-Host "Error: The Hash Table has no match for $LicenseItem!" -ForegroundColor Red
				If ($UnusedUnits -gt 0)
				{
					Write-Host "$LicenseFallBackName has $UnusedUnits unused licenses" -ForegroundColor Magenta
					
					$license | Select-Object @{ Name = "License"; Expression = { "$LicenseFallBackName" } }, ActiveUnits, WarningUnits, ConsumedUnits, @{ Name = "Unused"; Expression = { "$UnusedUnits" } } | Export-Csv -Path $msolAccountSkuCsv -Append -NoTypeInformation
				}
				
				Else
				{
					Write-Host "$LicenseFallBackName has $UnusedUnits unused licenses" -ForegroundColor Green
					
				}
			}
			Else
			{
				If ($UnusedUnits -gt 0)
				{
					Write-Host "$TextLic has $UnusedUnits unused licenses" -ForegroundColor Magenta
					
					$license | Select-Object @{ Name = "License"; Expression = { "$TextLic" } }, ActiveUnits, WarningUnits, ConsumedUnits, @{ Name = "Unused"; Expression = { "$UnusedUnits" } } | Export-Csv -Path $msolAccountSkuCsv -Append -NoTypeInformation
				}
				
				Else
				{
					Write-Host "$TextLic has $UnusedUnits unused licenses" -ForegroundColor Green
					
				}
			}
		}
	}
	
}

Brad Wyatt

My name is Bradley Wyatt; I am a 4x Microsoft Most Valuable Professional in Cloud and Datacenter Management. I have given talks at many different conferences, user groups, and companies throughout the United States ranging from PowerShell to DevOps Security best practices and am the 2022 North American Outstanding Contribution to the Microsoft Community winner.

8 thoughts on “[Function] Export Report of Unused Office 365 Licenses Across Multiple Tenants or a Single Tenant Using Friendly License Names”

Darren Kattan says:

April 8, 2018 at 9:54 pm

You should find a way to make it exclude trial licenses

Brad Wyatt says:

April 9, 2018 at 3:16 pm

Hi Darren –
Any suggestions? I looked into it but the SKU doesnt always say free or trial in it, I was thinking of excluding AccountSkus with greater than 99999 licenses

Peter Cooney says:

May 29, 2018 at 7:41 am

Great script and yes those GCITS guys are amazing. Some addtional SKUs for your script below

“WINDOWS_STORE” = “Windows Store for Business”
“MCOEV” = “Microsoft Phone System”
“VIDEO_INTEROP” = “Polycom Skype Meeting Video Interop for Skype for Business”
“SPE_E5” = “Microsoft 365 E5”
“SPE_E3” = “Microsoft 365 E3”
“ATA” = “Advanced Threat Analytics”
“MCOPSTN2” = “Domestic and International Calling Plan”
“FLOW_P1” = “Microsoft Flow Plan 1”
“FLOW_P2” = “Microsoft Flow Plan 2”

1. Brad Wyatt says:
  
  June 21, 2018 at 8:00 pm
  
  Thank you I have added it to the Hash Table!
  
Michael Brissenden says:

September 27, 2023 at 4:51 am

Wondering if anyone has been able to successfully update this script to function with GDAP? I conducted a test today and encountered the following error, indicating that GDAP (even with GA GDAP Role) didn’t work. This report has been a valuable monthly tool for our client base, and I’d like to ensure it continues to function seamlessly. We plan to further investigate this shortly.

“GDAP” Get-MsolUser : Access Denied. You do not have permissions to call this cmdlet.

1. Mike says:
  
  October 5, 2023 at 2:50 am
  
  Same problem here! We can’t get it working with GDAP. Only DAP clients are added to the list.
  
Rams says:

October 11, 2023 at 9:43 pm

Great Script! did you try to use Graph API to get the license info using a logic app?

Romain Guillaume says:

December 6, 2023 at 11:03 am

Hi,

So helpfull script ! Same probleme since GDAP deployment and still looking for an alternative …

Regards !

The Lazy Administrator

Finding ways to do the most work with the least effort possible