• Skip to primary navigation
  • Skip to main content
  • Skip to footer

Stephanos Constantinou Blog

PowerShell Scripting

  • Home
  • Blogs
    • PowerShell Tutorials
    • PowerShell Scripts
    • PowerShell Modules
      • Modules Cmdlets
    • Software Reviews
  • About
  • Contact
You are here: Home / PowerShell Scripts / Office 365 License Report – Part 2

Office 365 License Report – Part 2

25/04/2018 by Stephanos 13 Comments

Office 365 License Report – Part 2

Scenario:

In the first part, I have explained  how and why I have setup this script to get Office 365 License Report. Microsoft has added some new statuses for the licenses after I wrote the script, so I had to go back and update the script. The current statuses are:

  • Active: Number of licenses that you’ve purchased for a specific licensing plan
  • Consumed: The number of licenses that have been assigned already.
  • LockedOut: The LockedOutUnits is used for identifying subscriptions that are canceled by customers themselves
  • Suspended: The number of suspended licenses. These licenses are not available for assignment
  • Warning: Number of licenses in a licensing plan that you haven’t renewed, and that will expire after the 30-day grace period.

Changes:

  • Removed any back ticks where ever they were used.
  • Better code presentation.
  • Added new licenses statuses
  • Code optimization

Please note that you needs to change the below parts based on your system. in order to run the script correctly.

Credentials:

In the script you will need to use the name of the credentials that you have saved already in your Azure Automation. Below are the two lines that you have to change the names to the correct ones.

$Credentials = Get-AutomationPSCredential -Name 'Admin User'
$EmailCredentials = Get-AutomationPSCredential -Name 'Email User'

Sender and Recipients:

The email of the sender needs to be configured on $From variable and all recipients should be on $To variable, comma separated. Below are the lines that you have to adjust.

$To = 'User1@domain.com',
      'User2@domain.com',
      'User3@domain.com'
$From = 'Email-User@domain.com'

Licenses:

Please note the below list. You need to use the correct value of license under your tenant to receive the correct information. The “company” part needs to be changed to yours in order to have the correct values. Also note that you may note have some of the below licenses and you will needs to remove them from the list and report.

$P1 = "company:EXCHANGESTANDARD" #Exchange Online (Plan 1)
$P2 = "company:EXCHANGEENTERPRISE" #Exchange Online (Plan 2)
$E3 = "company:ENTERPRISEPACK" #Office365 Enterprise E3
$VisioP2 = "company:VISIOCLIENT" #Visio Online Plan 2
$BIFree = "company:POWER_BI_STANDARD" #Power BI (free)
$BIPro = "company:POWER_BI_PRO" #Power BI Pro
$BIPremium = "company:PBI_PREMIUM_EM1_ADDON"
$ATP = "company:ATP_ENTERPRISE" #Office 365 Advance Tread Protection
$CRMInstance = "company:CRMINSTANCE"
$ProjectEssentials = "company:PROJECTESSENTIALS" #Project Online Essentials
$ProjectProfessional = "company:PROJECTPROFESSIONAL" #Project Online Professional
$ProjectPremium = "company:PROJECTPREMIUM" #Project Online Premium
$Dyn365EnterpriseP1 = "company:DYN365_ENTERPRISE_PLAN1" #Dynamics 365 Customer Engagement Plan Enterprise Edition
$Dyn365EnterpriseTeam = "company:DYN365_ENTERPRISE_TEAM_MEMBERS" #Dynamics 365 for Team Members Enterprise Edition
$EMS = "company:EMS" #Enterprise Mobility + Security E3
$AADPremium = "company:AAD_PREMIUM" #Azure Active Directory Premium P1
$PowerApps = "company:POWERAPPS_VIRAL" #Microsoft PowerApp Plan 2 Trial
$Stream = "company:STREAM" #Microsoft Stream Trial
$Flow = "company:FLOW_FREE" #Microsoft Flow Free

You can download the script here or copy it from below. (Note that html code within the script might be wrong due to syntax highlighting.)

Hope you like it. If you have any questions or anything else please let me know in the comments below.

Related Links:

  • Send Email using PowerShell
  • Email error variable in PowerShell
  • Office 365 PowerShell License Report
  • Azure Automation Overview | Microsoft Docs
  • View account license and service details with Office 365 PowerShell
  • Credential assets in Azure Automation | Microsoft Docs
  • Get-PSSession – Microsoft Docs
  • Remove-PSSession – Microsoft Docs
  • Import-Module – Microsoft Docs
  • Connect-MsolService (MSOnline) | Microsoft Docs
  • New-PSSession – Microsoft Docs
  • Import-PSSession – Microsoft Docs
  • Get-Mailbox – TechNet – Microsoft
  • Get-MsolUser (MSOnline) | Microsoft Docs
  • Get-MsolAccountSku (MSOnline) | Microsoft Docs
  • Send-MailMessage – Microsoft Docs
  • Get-Date – Microsoft Docs

Solution / Script:

<#
.SYNOPSIS
    Name: Licenses_Report.ps1
    Get licenses report from Office 365.
  
.DESCRIPTION
    The script checks everyday the licenses that are available on Office 365.
    It will provide you with a report of Active, Consumed, LockedOut, Suspended,
    Warning and Available Licenses. It also checks if any user is assigned with
    duplicate licenses that may affect each other (Exchange Plan 1, Exchange
    Plan 2, Enterprise E3). The script checks also if there is any user that has
    no license and provide the UserPrincipalName of the user/s. It will send an
    email report including this information only if there is any user with
    duplicate or no license.
  
.RELATED LINKS
    
Home
.NOTES Version: 1.1 Update: 25-04-2018 - Added new Licenses statuses - Updated Report - Code Optimisation Release Date: 24-05-2017 Author: Stephanos Constantinou .EXAMPLE Licenses_Report.ps1 #> $Credentials = Get-AutomationPSCredential -Name 'Admin User' $EmailCredentials = Get-AutomationPSCredential -Name 'Email User' $To = 'User1@domain.com', 'User2@domain.com', 'User3@domain.com' $From = 'Email-User@domain.com' Get-PSSession | Remove-PSSession Import-Module MSOnline Connect-MsolService -Credential $Credentials $SessionParams = @{ ConfigurationName = "Microsoft.Exchange" ConnectionUri = "https://outlook.office365.com/powershell-liveid/" Credential = $Credentials Authentication = "Basic" AllowRedirection = $true} $Session = New-PSSession @SessionParams Import-PSSession -Session $Session -DisableNameChecking:$true -AllowClobber:$true | Out-Null $AllUsers = Get-MsolUser -All $AllUserMailboxes = (Get-Mailbox -ResultSize Unlimited | where {$_.RecipientTypeDetails -eq "UserMailbox"}).UserPrincipalName $AllNoLicenseUsers = ($AllUsers | where {$_.isLicensed -eq $false}).UserPrincipalName $NoLicenseMailboxes = $AllUserMailboxes | where {$AllNoLicenseUsers -contains $_} $AllLicenses = Get-MsolAccountSku | select AccountSkuId,ActiveUnits,ConsumedUnits,LockedOutUnits,SuspendedUnits,WarningUnits $P1 = "company:EXCHANGESTANDARD" #Exchange Online (Plan 1) $P2 = "company:EXCHANGEENTERPRISE" #Exchange Online (Plan 2) $E3 = "company:ENTERPRISEPACK" #Office365 Enterprise E3 $VisioP2 = "company:VISIOCLIENT" #Visio Online Plan 2 $BIFree = "company:POWER_BI_STANDARD" #Power BI (free) $BIPro = "company:POWER_BI_PRO" #Power BI Pro $BIPremium = "company:PBI_PREMIUM_EM1_ADDON" $ATP = "company:ATP_ENTERPRISE" #Office 365 Advance Tread Protection $CRMInstance = "company:CRMINSTANCE" $ProjectEssentials = "company:PROJECTESSENTIALS" #Project Online Essentials $ProjectProfessional = "company:PROJECTPROFESSIONAL" #Project Online Professional $ProjectPremium = "company:PROJECTPREMIUM" #Project Online Premium $Dyn365EnterpriseP1 = "company:DYN365_ENTERPRISE_PLAN1" #Dynamics 365 Customer Engagement Plan Enterprise Edition $Dyn365EnterpriseTeam = "company:DYN365_ENTERPRISE_TEAM_MEMBERS" #Dynamics 365 for Team Members Enterprise Edition $EMS = "company:EMS" #Enterprise Mobility + Security E3 $AADPremium = "company:AAD_PREMIUM" #Azure Active Directory Premium P1 $PowerApps = "company:POWERAPPS_VIRAL" #Microsoft PowerApp Plan 2 Trial $Stream = "company:STREAM" #Microsoft Stream Trial $Flow = "company:FLOW_FREE" #Microsoft Flow Free $DuplicateLicenseUsers = ($AllUsers | where {($_.isLicensed -eq "TRUE") -and ((($_.Licenses.AccountSKUID -eq $E3) -and ($_.Licenses.AccountSKUID -eq $P2)) -or (($_.Licenses.AccountSKUID -eq $E3) -and ($_.Licenses.AccountSKUID -eq $P1)) -or (($_.Licenses.AccountSKUID -eq $P2) -and ($_.Licenses.AccountSKUID -eq $P1)))}).UserPrincipalName $TotalP1 = ($AllLicenses | where {$_.AccountSkuId -eq $P1}).ActiveUnits $UsedP1 = ($AllLicenses | where {$_.AccountSkuId -eq $P1}).ConsumedUnits $LockedP1 = ($AllLicenses | where {$_.AccountSkuId -eq $P1}).LockedOutUnits $SuspendedP1 = ($AllLicenses | where {$_.AccountSkuId -eq $P1}).SuspendedUnits $WarningP1 = ($AllLicenses | where {$_.AccountSkuId -eq $P1}).WarningUnits $AvailableP1 = $TotalP1 - $UsedP1 - $LockedP1 - $SuspendedP1 - $WarningP1 $TotalP2 = ($AllLicenses | where {$_.AccountSkuId -eq $P2}).ActiveUnits $UsedP2 = ($AllLicenses | where {$_.AccountSkuId -eq $P2}).ConsumedUnits $LockedP2 = ($AllLicenses | where {$_.AccountSkuId -eq $P2}).LockedOutUnits $SuspendedP2 = ($AllLicenses | where {$_.AccountSkuId -eq $P2}).SuspendedUnits $WarningP2 = ($AllLicenses | where {$_.AccountSkuId -eq $P2}).WarningUnits $AvailableP2 = $TotalP2 - $UsedP2 - $LockedP2 - $SuspendedP2 - $WarningP2 $TotalE3 = ($AllLicenses | where {$_.AccountSkuId -eq $E3}).ActiveUnits $UsedE3 = ($AllLicenses | where {$_.AccountSkuId -eq $E3}).ConsumedUnits $LockedE3 = ($AllLicenses | where {$_.AccountSkuId -eq $E3}).LockedOutUnits $SuspendedE3 = ($AllLicenses | where {$_.AccountSkuId -eq $E3}).SuspendedUnits $WarningE3 = ($AllLicenses | where {$_.AccountSkuId -eq $E3}).WarningUnits $AvailableE3 = $TotalE3 - $UsedE3 - $LockedE3 - $SuspendedE3 - $WarningE3 $TotalVisioP2 = ($AllLicenses | where {$_.AccountSkuId -eq $VisioP2}).ActiveUnits $UsedVisioP2 = ($AllLicenses | where {$_.AccountSkuId -eq $VisioP2}).ConsumedUnits $LockedVisioP2 = ($AllLicenses | where {$_.AccountSkuId -eq $VisioP2}).LockedOutUnits $SuspendedVisioP2 = ($AllLicenses | where {$_.AccountSkuId -eq $VisioP2}).SuspendedUnits $WarningVisioP2 = ($AllLicenses | where {$_.AccountSkuId -eq $VisioP2}).WarningUnits $AvailableVisioP2 = $TotalVisioP2 - $UsedVisioP2 - $LockedVisioP2 - $SuspendedVisioP2 - $WarningVisioP2 $TotalBIFree = ($AllLicenses | where {$_.AccountSkuId -eq $BIFree}).ActiveUnits $UsedBIFree = ($AllLicenses | where {$_.AccountSkuId -eq $BIFree}).ConsumedUnits $LockedBIFree = ($AllLicenses | where {$_.AccountSkuId -eq $BIFree}).LockedOutUnits $SuspendedBIFree = ($AllLicenses | where {$_.AccountSkuId -eq $BIFree}).SuspendedUnits $WarningBIFree = ($AllLicenses | where {$_.AccountSkuId -eq $BIFree}).WarningUnits $AvailableBIFree = $TotalBIFree - $UsedBIFree - $LockedBIFree - $SuspendedBIFree - $WarningBIFree $TotalBIPro = ($AllLicenses | where {$_.AccountSkuId -eq $BIPro}).ActiveUnits $UsedBIPro = ($AllLicenses | where {$_.AccountSkuId -eq $BIPro}).ConsumedUnits $LockedBIPro = ($AllLicenses | where {$_.AccountSkuId -eq $BIPro}).LockedOutUnits $SuspendedBIPro = ($AllLicenses | where {$_.AccountSkuId -eq $BIPro}).SuspendedUnits $WarningBIPro = ($AllLicenses | where {$_.AccountSkuId -eq $BIPro}).WarningUnits $AvailableBIPro = $TotalBIPro - $UsedBIPro - $LockedBIPro - $SuspendedBIPro - $WarningBIPro $TotalBIPremium = ($AllLicenses | where {$_.AccountSkuId -eq $BIPremium}).ActiveUnits $UsedBIPremium = ($AllLicenses | where {$_.AccountSkuId -eq $BIPremium}).ConsumedUnits $LockedBIPremium = ($AllLicenses | where {$_.AccountSkuId -eq $BIPremium}).LockedOutUnits $SuspendedBIPremium = ($AllLicenses | where {$_.AccountSkuId -eq $BIPremium}).SuspendedUnits $WarningBIPremium = ($AllLicenses | where {$_.AccountSkuId -eq $BIPremium}).WarningUnits $AvailableBIPremium = $TotalBIPremium - $UsedBIPremium - $LockedBIPremium - $SuspendedBIPremium - $WarningBIPremium $TotalATP = ($AllLicenses | where {$_.AccountSkuId -eq $ATP}).ActiveUnits $UsedATP = ($AllLicenses | where {$_.AccountSkuId -eq $ATP}).ConsumedUnits $LockedATP = ($AllLicenses | where {$_.AccountSkuId -eq $ATP}).LockedOutUnits $SuspendedATP = ($AllLicenses | where {$_.AccountSkuId -eq $ATP}).SuspendedUnits $WarningATP = ($AllLicenses | where {$_.AccountSkuId -eq $ATP}).WarningUnits $AvailableATP = $TotalATP - $UsedATP - $LockedATP - $SuspendedATP - $WarningATP $TotalCRMInstance = ($AllLicenses | where {$_.AccountSkuId -eq $CRMInstance}).ActiveUnits $UsedCRMInstance = ($AllLicenses | where {$_.AccountSkuId -eq $CRMInstance}).ConsumedUnits $LockedCRMInstance = ($AllLicenses | where {$_.AccountSkuId -eq $CRMInstance}).LockedOutUnits $SuspendedCRMInstance = ($AllLicenses | where {$_.AccountSkuId -eq $CRMInstance}).SuspendedUnits $WarningCRMInstance = ($AllLicenses | where {$_.AccountSkuId -eq $CRMInstance}).WarningUnits $AvailableCRMInstance = $TotalCRMInstance - $UsedCRMInstance - $LockedCRMInstance - $SuspendedCRMInstance - $WarningCRMInstance $TotalProjectEssentials = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectEssentials}).ActiveUnits $UsedProjectEssentials = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectEssentials}).ConsumedUnits $LockedProjectEssentials = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectEssentials}).LockedOutUnits $SuspendProjectEssentials = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectEssentials}).SuspendedUnits $WarningProjectEssentials = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectEssentials}).WarningUnits $AvailableProjectEssentials = $TotalProjectEssentials - $UsedProjectEssentials - $LockedProjectEssentials - $SuspendProjectEssentials - $WarningProjectEssentials $TotalProjectProfessional = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectProfessional}).ActiveUnits $UsedProjectProfessional = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectProfessional}).ConsumedUnits $LockedProjectProfessional = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectProfessional}).LockedOutUnits $SuspendedProjectProfessional = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectProfessional}).SuspendedUnits $WarningProjectProfessional = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectProfessional}).WarningUnits $AvailableProjectProfessional = $TotalProjectProfessional - $UsedProjectProfessional - $LockedProjectProfessional - $SuspendedProjectProfessional - $WarningProjectProfessional $TotalProjectPremium = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectPremium}).ActiveUnits $UsedProjectPremium = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectPremium}).ConsumedUnits $LockedProjectPremium = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectPremium}).LockedOutUnits $SuspendedProjectPremium = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectPremium}).SuspendedUnits $WarningProjectPremium = ($AllLicenses | where {$_.AccountSkuId -eq $ProjectPremium}).WarningUnits $AvailableProjectPremium = $TotalProjectPremium - $UsedProjectPremium - $LockedProjectPremium - $SuspendedProjectPremium - $WarningProjectPremium $TotalDyn365EnterpriseP1 = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseP1}).ActiveUnits $UsedDyn365EnterpriseP1 = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseP1}).ConsumedUnits $LockedDyn365EnterpriseP1 = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseP1}).LockedOutUnits $SuspendedDyn365EnterpriseP1 = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseP1}).SuspendedUnits $WarningDyn365EnterpriseP1 = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseP1}).WarningUnits $AvailableDyn365EnterpriseP1 = $TotalDyn365EnterpriseP1 - $UsedDyn365EnterpriseP1 - $LockedDyn365EnterpriseP1 - $SuspendedDyn365EnterpriseP1 - $WarningDyn365EnterpriseP1 $TotalDyn365EnterpriseTeam = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseTeam}).ActiveUnits $UsedDyn365EnterpriseTeam = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseTeam}).ConsumedUnits $LockedDyn365EnterpriseTeam = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseTeam}).LockedOutUnits $SuspendedDyn365EnterpriseTeam = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseTeam}).SuspendedUnits $WarningDyn365EnterpriseTeam = ($AllLicenses | where {$_.AccountSkuId -eq $Dyn365EnterpriseTeam}).WarningUnits $AvailableDyn365EnterpriseTeam = $TotalDyn365EnterpriseTeam - $UsedDyn365EnterpriseTeam - $LockedDyn365EnterpriseTeam - $SuspendedDyn365EnterpriseTeam - $WarningDyn365EnterpriseTeam $TotalEMS = ($AllLicenses | where {$_.AccountSkuId -eq $EMS}).ActiveUnits $UsedEMS = ($AllLicenses | where {$_.AccountSkuId -eq $EMS}).ConsumedUnits $LockedEMS = ($AllLicenses | where {$_.AccountSkuId -eq $EMS}).LockedOutUnits $SuspendedEMS = ($AllLicenses | where {$_.AccountSkuId -eq $EMS}).SuspendedUnits $WarningEMS = ($AllLicenses | where {$_.AccountSkuId -eq $EMS}).WarningUnits $AvailableEMS = $TotalEMS - $UsedEMS - $LockedEMS - $SuspendedEMS - $WarningEMS $TotalAADPremium = ($AllLicenses | where {$_.AccountSkuId -eq $AADPremium}).ActiveUnits $UsedAADPremium = ($AllLicenses | where {$_.AccountSkuId -eq $AADPremium}).ConsumedUnits $LockedAADPremium = ($AllLicenses | where {$_.AccountSkuId -eq $AADPremium}).LockedOutUnits $SuspendedAADPremium = ($AllLicenses | where {$_.AccountSkuId -eq $AADPremium}).SuspendedUnits $WarningAADPremium = ($AllLicenses | where {$_.AccountSkuId -eq $AADPremium}).WarningUnits $AvailableAADPremium = $TotalAADPremium - $UsedAADPremium - $LockedAADPremium - $SuspendedAADPremium - $WarningAADPremium $TotalPowerApps = ($AllLicenses | where {$_.AccountSkuId -eq $PowerApps}).ActiveUnits $UsedPowerApps = ($AllLicenses | where {$_.AccountSkuId -eq $PowerApps}).ConsumedUnits $LockedPowerApps = ($AllLicenses | where {$_.AccountSkuId -eq $PowerApps}).LockedOutUnits $SuspendedPowerApps = ($AllLicenses | where {$_.AccountSkuId -eq $PowerApps}).SuspendedUnits $WarningPowerApps = ($AllLicenses | where {$_.AccountSkuId -eq $PowerApps}).WarningUnits $AvailablePowerApps = $TotalPowerApps - $UsedPowerApps - $LockedPowerApps - $SuspendedPowerApps - $WarningPowerApps $TotalStream = ($AllLicenses | where {$_.AccountSkuId -eq $Stream}).ActiveUnits $UsedStream = ($AllLicenses | where {$_.AccountSkuId -eq $Stream}).ConsumedUnits $LockedStream = ($AllLicenses | where {$_.AccountSkuId -eq $Stream}).LockedOutUnits $SuspendedStream = ($AllLicenses | where {$_.AccountSkuId -eq $Stream}).SuspendedUnits $WarningStream = ($AllLicenses | where {$_.AccountSkuId -eq $Stream}).WarningUnits $AvailableStream = $TotalStream - $UsedStream - $LockedStream - $SuspendedStream - $WarningStream $TotalFlow = ($AllLicenses | where {$_.AccountSkuId -eq $Flow}).ActiveUnits $UsedFlow = ($AllLicenses | where {$_.AccountSkuId -eq $Flow}).ConsumedUnits $LockedFlow = ($AllLicenses | where {$_.AccountSkuId -eq $Flow}).LockedOutUnits $SuspendedFlow = ($AllLicenses | where {$_.AccountSkuId -eq $Flow}).SuspendedUnits $WarningFlow = ($AllLicenses | where {$_.AccountSkuId -eq $Flow}).WarningUnits $AvailableFlow = $TotalFlow - $UsedFlow - $LockedFlow - $SuspendedFlow - $WarningFlow $Email = @" <style> body { font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; color:#434242;} TABLE { font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} TR {border-width: 1px;padding: 10px;border-style: solid;border-color: white; } TD {font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; border-width: 1px;padding: 10px;border-style: solid;border-color: white; background-color:#C3DDDB;} .colorm {background-color:#58A09E; color:white;} .colort{background-color:#58A09E; padding:20px; color:white; font-weight:bold;} .colorn{background-color:transparent;} </style> <body> <h3>Licensing report</h3> <h4>Licenses Issues:</h4> <table> <tr> <td class="colorm">Mailboxes with duplicate license:</td> <td >$DuplicateLicenseUsers</td> </tr> <tr> <td class="colorm">Mailboxes with no license:</td> <td>$NoLicenseMailboxes</td> </tr> </table> <h4>Totals of licenses we have:</h4> <table> <tr> <td class="colorn"></td> <td class="colort">We have:</td> <td class="colort">Used:</td> <td class="colort">LockedOut:</td> <td class="colort">Suspended:</td> <td class="colort">Warning:</td> <td class="colort">Available:</td> </tr> <tr> <td class="colorm">Exchange Online (Plan1):</td> <td style="text-align:center">$TotalP1</td> <td style="text-align:center">$UsedP1</td> <td style="text-align:center">$LockedP1</td> <td style="text-align:center">$SuspendedP1</td> <td style="text-align:center">$WarningP1</td> <td style="text-align:center">$AvailableP1</td> </tr> <tr> <td class="colorm">Exchange Online (Plan2):</td> <td style="text-align:center">$TotalP2</td> <td style="text-align:center">$UsedP2</td> <td style="text-align:center">$LockedP2</td> <td style="text-align:center">$SuspendedP2</td> <td style="text-align:center">$WarningP2</td> <td style="text-align:center">$AvailableP2</td> </tr> <tr> <td class="colorm">Office365 Enterprise E3:</td> <td style="text-align:center">$TotalE3</td> <td style="text-align:center">$UsedE3</td> <td style="text-align:center">$LockedE3</td> <td style="text-align:center">$SuspendedE3</td> <td style="text-align:center">$WarningE3</td> <td style="text-align:center">$AvailableE3</td> </tr> <tr> <td class="colorm">Visio Online Plan 2:</td> <td style="text-align:center">$TotalVisioP2</td> <td style="text-align:center">$UsedVisioP2</td> <td style="text-align:center">$LockedVisioP2</td> <td style="text-align:center">$SuspendedVisioP2</td> <td style="text-align:center">$WarningVisioP2</td> <td style="text-align:center">$AvailableVisioP2</td> </tr> <tr> <td class="colorm">Power BI (free):</td> <td style="text-align:center">$TotalBIFree</td> <td style="text-align:center">$UsedBIFree</td> <td style="text-align:center">$LockedBIFree</td> <td style="text-align:center">$SuspendedBIFree</td> <td style="text-align:center">$WarningBIFree</td> <td style="text-align:center">$AvailableBIFree</td> </tr> <tr> <td class="colorm">Power BI Pro:</td> <td style="text-align:center">$TotalBIPro</td> <td style="text-align:center">$UsedBIPro</td> <td style="text-align:center">$LockedBIPro</td> <td style="text-align:center">$SuspendedBIPro</td> <td style="text-align:center">$WarningBIPro</td> <td style="text-align:center">$AvailableBIPro</td> </tr> <tr> <td class="colorm">Power BI Premium EM1:</td> <td style="text-align:center">$TotalBIPremium</td> <td style="text-align:center">$UsedBIPremium</td> <td style="text-align:center">$LockedBIPremium</td> <td style="text-align:center">$SuspendedBIPremium</td> <td style="text-align:center">$WarningBIPremium</td> <td style="text-align:center">$AvailableBIPremium</td> </tr> <tr> <td class="colorm">Office 365 Advance Tread Protection:</td> <td style="text-align:center">$TotalATP</td> <td style="text-align:center">$UsedATP</td> <td style="text-align:center">$LockedATP</td> <td style="text-align:center">$SuspendedATP</td> <td style="text-align:center">$WarningATP</td> <td style="text-align:center">$AvailableATP</td> </tr> <tr> <td class="colorm">Microsoft Dynamics CRM Online Instance:</td> <td style="text-align:center">$TotalCRMInstance</td> <td style="text-align:center">$UsedCRMInstance</td> <td style="text-align:center">$LockedCRMInstance</td> <td style="text-align:center">$SuspendedCRMInstance</td> <td style="text-align:center">$WarningCRMInstance</td> <td style="text-align:center">$AvailableCRMInstance</td> </tr> <tr> <td class="colorm">Project Online Essentials:</td> <td style="text-align:center">$TotalProjectEssentials</td> <td style="text-align:center">$UsedProjectEssentials</td> <td style="text-align:center">$LockedProjectEssentials</td> <td style="text-align:center">$SuspendedProjectEssentials</td> <td style="text-align:center">$WarningProjectEssentials</td> <td style="text-align:center">$AvailableProjectEssentials</td> </tr> <tr> <td class="colorm">Project Online Professional:</td> <td style="text-align:center">$TotalProjectProfessional</td> <td style="text-align:center">$UsedProjectProfessional</td> <td style="text-align:center">$LockedProjectProfessional</td> <td style="text-align:center">$SuspendedProjectProfessional</td> <td style="text-align:center">$WarningProjectProfessional</td> <td style="text-align:center">$AvailableProjectProfessional</td> </tr> <tr> <td class="colorm">Project Online Premium:</td> <td style="text-align:center">$TotalProjectPremium</td> <td style="text-align:center">$UsedProjectPremium</td> <td style="text-align:center">$LockedProjectPremium</td> <td style="text-align:center">$SuspendedProjectPremium</td> <td style="text-align:center">$WarningProjectPremium</td> <td style="text-align:center">$AvailableProjectPremium</td> </tr> <tr> <td class="colorm">Dynamics 365 Customer Engagement Plan Enterprise Edition:</td> <td style="text-align:center">$TotalDyn365EnterpriseP1</td> <td style="text-align:center">$UsedDyn365EnterpriseP1</td> <td style="text-align:center">$LockedDyn365EnterpriseP1</td> <td style="text-align:center">$SuspendedDyn365EnterpriseP1</td> <td style="text-align:center">$WarningDyn365EnterpriseP1</td> <td style="text-align:center">$AvailableDyn365EnterpriseP1</td> </tr> <tr> <td class="colorm">Dynamics 365 for Team Members Enterprise Edition:</td> <td style="text-align:center">$TotalDyn365EnterpriseTeam</td> <td style="text-align:center">$UsedDyn365EnterpriseTeam</td> <td style="text-align:center">$LockedDyn365EnterpriseTeam</td> <td style="text-align:center">$SuspendedDyn365EnterpriseTeam</td> <td style="text-align:center">$WarningDyn365EnterpriseTeam</td> <td style="text-align:center">$AvailableDyn365EnterpriseTeam</td> </tr> <tr> <td class="colorm">Enterprise Mobility + Security E3:</td> <td style="text-align:center">$TotalEMS</td> <td style="text-align:center">$UsedEMS</td> <td style="text-align:center">$LockedEMS</td> <td style="text-align:center">$SuspendedEMS</td> <td style="text-align:center">$WarningEMS</td> <td style="text-align:center">$AvailableEMS</td> </tr> <tr> <td class="colorm">Azure Active Directory Premium P1:</td> <td style="text-align:center">$TotalAADPremium</td> <td style="text-align:center">$UsedAADPremium</td> <td style="text-align:center">$LockedAADPremium</td> <td style="text-align:center">$SuspendedAADPremium</td> <td style="text-align:center">$WarningAADPremium</td> <td style="text-align:center">$AvailableAADPremium</td> </tr> <tr> <td class="colorm">Microsoft PowerApp Plan 2 Trial:</td> <td style="text-align:center">$TotalPowerApps</td> <td style="text-align:center">$UsedPowerApps</td> <td style="text-align:center">$LockedPowerApps</td> <td style="text-align:center">$SuspendedPowerApps</td> <td style="text-align:center">$WarningPowerApps</td> <td style="text-align:center">$AvailablePowerApps</td> </tr> <tr> <td class="colorm">Microsoft Stream Trial:</td> <td style="text-align:center">$TotalStream</td> <td style="text-align:center">$UsedStream</td> <td style="text-align:center">$LockedStream</td> <td style="text-align:center">$SuspendedStream</td> <td style="text-align:center">$WarningStream</td> <td style="text-align:center">$AvailableStream</td> </tr> <tr> <td class="colorm">Microsoft Flow Free:</td> <td style="text-align:center">$TotalFlow</td> <td style="text-align:center">$UsedFlow</td> <td style="text-align:center">$LockedFlow</td> <td style="text-align:center">$SuspendedFlow</td> <td style="text-align:center">$WarningFlow</td> <td style="text-align:center">$AvailableFlow</td> </tr> </table> </body> "@ if (($NoLicenseMailboxes -ne $null) -or ($DuplicateLicenseUsers -ne $null)){ $EmailParams = @{ To = $To Subject = "Licensing Report $(Get-Date -format dd/MM/yyyy)" Body = $Email BodyAsHtml = $True Priority = "High" UseSsl = $True Port = "587" SmtpServer = "smtp.office365.com" Credential = $EmailCredentials From = $From} send-mailmessage @EmailParams} if ($error -ne $null){ foreach ($value in $error){ $ErrorEmailTemp = @" <tr> <td class="colorm">$value</td> </tr> "@ $ErrorEmailResult = $ErrorEmailResult + "`r`n" + $ErrorEmailTemp} $ErrorEmailUp = @" <style> body {font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; color:#434242;} TABLE {font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} TR {border-width: 1px;padding: 10px;border-style: solid;border-color: white; } TD {font-family:Segoe, "Segoe UI", "DejaVu Sans", "Trebuchet MS", Verdana, sans-serif !important; border-width: 1px;padding: 10px;border-style: solid;border-color: white; background-color:#C3DDDB;} .colorm {background-color:#58A09E; color:white;} h3 {color:#BD3337 !important;} </style> <body> <h3 style="color:#BD3337 !important;"> WARNING!!!</h3> <p>There were errors during users attributes changes check</p> <p>Please check the errors and act accordingly</p> <table> "@ $ErrorEmailDown = @" </table> </body> "@ $ErrorEmail = $ErrorEmailUp + $ErrorEmailResult + $ErrorEmailDown $ErrorEmailParams = @{ To = $To Subject = "Licensing Report $(Get-Date -format dd/MM/yyyy) - WARNING" Body = $Email BodyAsHtml = $True Priority = "High" UseSsl = $True Port = "587" SmtpServer = "smtp.office365.com" Credential = $EmailCredentials From = $From} send-mailmessage @ErrorEmailParams}
Summary
Office 365 License Report - Part 2
Article Name
Office 365 License Report - Part 2
Description
Office 365 License Report Part 2. Users are added and removed from the system almost on a daily basis if you are a big company. With this script you will be able to know when your Office 365 licenses are running out and you need to proceed with a new order. Stephanos Constantinou Blog - PowerShell Scripting
Author
Stephanos
Publisher Name
Stephanos Constantinou Blog
Publisher Logo
Stephanos Constantinou Blog

Filed Under: PowerShell Scripts Tagged With: Azure Automation, Connect-MsolService, Get-AutomationPSCredential, Get-Date, Get-Mailbox, Get-MsolAccountSku, Get-MsolUser, Get-PSSession, Import-Module, Import-PSSession, Microsoft Azure, Microsoft Office 365, New-PSSession, Remove-PSSession, Send-MailMessage

Reader Interactions

Comments

  1. maxcoder88 says

    26/04/2018 at 13:31

    Hi ,

    Very nice job! I have short question. I have been using Exchange Server 2010 & Office 365 Hybrid environment. is it possible to use on-premises mailbox user to send mail? Or Do I have to use office365 mailbox user for it ?

    Thans in advance for your pointers

    Reply
    • Stephanos says

      26/04/2018 at 17:06

      Hello maxcoder88,

      You can use an on-premise mailbox to send the email. There will be no issue with it. In order to do it you will need to set the correct values for the below:

      $ErrorEmailParams = @{
      To = $To
      Subject = "Licensing Report $(Get-Date -format dd/MM/yyyy) - WARNING"
      Body = $Email
      BodyAsHtml = $True
      Priority = "High"
      UseSsl = $True
      Port = "587"
      SmtpServer = "smtp.office365.com"
      Credential = $EmailCredentials
      From = $From}

      Reply
  2. Marcelo Souza says

    22/10/2018 at 17:26

    Hello! The script is terrific but my report is coming blank… Just Available column has zero number. I really don´t know what is wrong. Could you give me a idea?!

    Reply
    • Stephanos says

      22/10/2018 at 17:55

      Hello Marcelo,

      Have you changed the values that are needed based on your setup? For example accounts to be used, licenses name etc?

      Reply
  3. Marcelo Souza says

    23/10/2018 at 15:34

    I changed only: $Credentials, $EmailCredentials, $To and $From. Need I change other value?

    Reply
    • Stephanos says

      23/10/2018 at 16:07

      Dear Marcelo,

      You need to change the below also:

      $P1 = "company:EXCHANGESTANDARD" #Exchange Online (Plan 1)
      $P2 = "company:EXCHANGEENTERPRISE" #Exchange Online (Plan 2)
      $E3 = "company:ENTERPRISEPACK" #Office365 Enterprise E3
      $VisioP2 = "company:VISIOCLIENT" #Visio Online Plan 2
      $BIFree = "company:POWER_BI_STANDARD" #Power BI (free)
      $BIPro = "company:POWER_BI_PRO" #Power BI Pro
      $BIPremium = "company:PBI_PREMIUM_EM1_ADDON"
      $ATP = "company:ATP_ENTERPRISE" #Office 365 Advance Tread Protection
      $CRMInstance = "company:CRMINSTANCE"
      $ProjectEssentials = "company:PROJECTESSENTIALS" #Project Online Essentials
      $ProjectProfessional = "company:PROJECTPROFESSIONAL" #Project Online Professional
      $ProjectPremium = "company:PROJECTPREMIUM" #Project Online Premium
      $Dyn365EnterpriseP1 = "company:DYN365_ENTERPRISE_PLAN1" #Dynamics 365 Customer Engagement Plan Enterprise Edition
      $Dyn365EnterpriseTeam = "company:DYN365_ENTERPRISE_TEAM_MEMBERS" #Dynamics 365 for Team Members Enterprise Edition
      $EMS = "company:EMS" #Enterprise Mobility + Security E3
      $AADPremium = "company:AAD_PREMIUM" #Azure Active Directory Premium P1
      $PowerApps = "company:POWERAPPS_VIRAL" #Microsoft PowerApp Plan 2 Trial
      $Stream = "company:STREAM" #Microsoft Stream Trial
      $Flow = "company:FLOW_FREE" #Microsoft Flow Free
      

      The word company in front of each license is a generic one. You need to specify to correct name for your company.

      Thanks
      Stephanos

      Reply
  4. Marcelo Souza says

    23/10/2018 at 20:46

    OMG! It´s working like a champ!! Tks a lot Stephanos, your script is beautiful!! Hugs

    Reply
    • Stephanos says

      24/10/2018 at 17:51

      Hello Marcelo,

      I’m glad that my script help you and you are happy with it.

      Thanks
      Stephanos

      Reply
  5. lucky says

    26/10/2018 at 17:12

    I get this error message; please advise

    WARNING: [localhost]:AzureAutomationAuthoringToolkit: Warning – Local value for PSCredential asset ‘ljayasek’ not found.
    WARNING: [localhost]:AzureAutomationAuthoringToolkit: Warning – Local value for PSCredential asset ‘ljayasek’ not found.
    New-PSSession : Cannot process argument transformation on parameter ‘Credential’. userName
    At C:\Users\ljayasek\Downloads\Licenses_Report.ps1:55 char:26
    + $Session = New-PSSession @SessionParams
    + ~~~~~~~~~~~~~~
    + CategoryInfo : InvalidData: (:) [New-PSSession], ParameterBindingArgumentTransformationException
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Microsoft.PowerShell.Commands.NewPSSessionCommand

    Import-PSSession : Cannot validate argument on parameter ‘Session’. The argument is null. Provide a valid value for the argument, and then try running the command again.
    At C:\Users\ljayasek\Downloads\Licenses_Report.ps1:57 char:27
    + Import-PSSession -Session $Session -DisableNameChecking:$true -AllowC …
    + ~~~~~~~~
    + CategoryInfo : InvalidData: (:) [Import-PSSession], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ImportPSSessionCommand

    Get-Mailbox : The term ‘Get-Mailbox’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
    that the path is correct and try again.
    At C:\Users\ljayasek\Downloads\Licenses_Report.ps1:61 char:22
    + $AllUserMailboxes = (Get-Mailbox -ResultSize Unlimited |
    + ~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Get-Mailbox:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    Reply
    • Stephanos says

      26/10/2018 at 17:38

      Hello lucky,

      From the error I can see that you are trying to run the script from your PC. Currently the script is written to run on Azure automation. You will need to perform few modifications in order to run it locally.
      If you run it on Azure Automation you will need to create a Credential on Azure automation and then specify that in the script.

      Thanks
      Stephanos

      Reply
  6. Anil Chauhan says

    14/11/2018 at 16:29

    Hi Stephanos,

    Great script, but what amendments do I need to make to run it locally on my pc instead of creating a Azure automation account.

    Thanks

    Reply
    • Stephanos says

      14/11/2018 at 16:37

      Hello Anil,

      You need to change the first two lines and save in those two variables the credentials of your on-prem accounts. You can check some of my other scripts that I wrote for on-prem use on how to save those credentials.

      Thanks
      Stephanos

      Reply
  7. Brenkster says

    12/11/2020 at 16:20

    Hi,

    Do you have a method of running this with an MFA enabled account on Azure Automation?
    Or how to run it from on-premises with an MFA enabled account as a scheduled task?

    Other question, did you post a blog about how to use Azure Automation, haven’t found a clear explanation so far.

    Thnx

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Footer

Recent Posts

  • ICS Cube Product Review 26/04/2019
  • PowerShell Module SysInfo v1.2.0 15/03/2019
  • PowerShell Module SysInfo v1.1.2 13/11/2018
  • PowerShell Module SysInfo 24/10/2018
  • Get-VoltageProbe 24/10/2018
  • Get-VideoController 24/10/2018
  • Get-USBController 24/10/2018
  • Get-TrackPoint 24/10/2018
  • Get-TrackBall 24/10/2018
  • Get-TouchScreen 24/10/2018
Planet PowerShell

Categories

  • Modules Cmdlets (57)
  • PowerShell Modules (5)
  • PowerShell Scripts (38)
  • PowerShell Tutorials (35)
  • Software Reviews (2)

Archives

  • April 2019 (1)
  • March 2019 (1)
  • November 2018 (1)
  • October 2018 (56)
  • September 2018 (13)
  • August 2018 (9)
  • July 2018 (6)
  • June 2018 (8)
  • May 2018 (7)
  • April 2018 (9)
  • March 2018 (4)
  • February 2018 (6)
  • January 2018 (12)
  • December 2017 (4)
Top 10 PowerShell 2018

Blogroll

  • Planet PowerShell
  • Reddit – PowerShell
  • PowerShell Magazine
  • PowerShell.org
  • PowerShell Team Blog
  • Hey, Scripting Guy! Blog
  • Mike F Robbins
  • PowerShell Explained with Kevin Marquette
  • Mike Kanakos – Network Admin
  • The Lonely Administrator
  • AskME4Tech
PowerShell Blogs Sysadmin Blogs Banners for Top 20 Programming Blogs

© 2023 · Stephanos Constantinou Blog

  • Home
  • Blogs
  • About
  • Contact