Stephanos Constantinou Blog

PowerShell Scripting

Home » Blogs » Office 365 PowerShell License Report

Office 365 PowerShell License Report

by 8 Comments

Office 365 PowerShell License Report

Scenario:

Office 365 PowerShell License Report.

When you are in an environment that changes to licenses are happening every day or a lot of users are activated and deactivated everyday it is very difficult to keep track of the licenses and when it is needed to order new licenses for you subscription.

Under azure portal you are able to see how many license left but again is not a convenient way. I have came up with the below script to get a report everyday for the licenses that the company has and have a report at any time with the number of licenses assigned to the users and how many are free. In general the script gathers the information from MS Online and create an HTML email report to be sent. The script is configured to run on Azure Automation on a schedule basis and manually.

Azure Automation

This next part is provided by Microsoft:

Azure Automation delivers a cloud-based automation and configuration service that provides consistent management across your Azure and non-Azure environments. It consists of process automation, update management, and configuration features. Azure Automation provides complete control during deployment, operations, and decommissioning of workloads and resources.

Let’s check the script in more details

First the script will save in two variables the credentials needed to perform the operation. The first one is the administrator credentials that will be used to gather he information from Azure. The second credentials are the ones that will be used to send the HTML email report to the intended users.

After the script has retrieved the credentials it will ensure that the session is clear and is not conflicting with any previous failed attempt of the script that may has stuck. The script check for any active sessions and if there is any it removes the sessions found. Next, The script will import MSOnline module that is needed to connect to the services, import the commands and gather the information.

When everything has been setup, the script will first check if there are any users without license. Users without license have a grace period of 30 days and after that they will stop to work. So to avoid such situation we need to find out if there are any users without Enterprise E3 or Exchage Online Plan 2 license. If there are any, their UserPrincipalName will be saved in a variable and include it in the email report.

The next part of the script, is another check that it may affect you. The script will check if a user has duplicate licenses. What I mean by this? In Enterprise E3 license, it include Exchange Online Plan 2. So if the company has also Exchange Online Plan 2 License separated, then you might come across this issue. So after the script actually checks if a users has been assigned with Enterpise E3 license and Exchange Online Plan 2 license.

Checks for unlicensed and duplicate licensed users completed

When the checks above will be completed, the script will continue to gather the information that we need. It gathers the total number of each license that is under the subscription, the number of the used licenses and the number of free licenses. The above procedure is done for each license and we save the information in a variable for each license, so the script will provide it on the HTML email report at the end.

Reporting

All the information that has been gathered, is imported in the HTML email report and sent to the intended users. If there is an error during the process, or exception, the script will sent and error email report to inform the intended recipient that something went wrong during the process and what was the error so it will act accordingly.

You can download the script here or copy it from below.

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

Related Links:

Solution / Script:

$Credentials = Get-AutomationPSCredential -Name 'Admin-User'
$EmailCredentials = Get-AutomationPSCredential -Name 'Email-User'
$To = '[email protected]','[email protected]'
$From = '[email protected]'

Get-PSSession | Remove-PSSession

Import-Module MSOnline

Connect-MsolService -Credential $Credentials

$Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credentials -Authentication Basic -AllowRedirection

Import-PSSession -Session $Session -DisableNameChecking:$true -AllowClobber:$true | Out-Null

$AllUserMailboxes = (Get-Mailbox -ResultSize Unlimited | where {$_.RecipientTypeDetails -eq "UserMailbox"}).UserPrincipalName
$AllNoLicenseUsers = (Get-MSolUser -All | where {$_.isLicensed -eq $false}).UserPrincipalName

$NoLicenseMailboxes = $AllUserMailboxes | where {$AllNoLicenseUsers -contains $_}

Get-MsolAccountSku
$DuplicateLicenseUsers = (Get-MsolUser -All | where {$_.isLicensed -eq "TRUE" -and $_.Licenses.AccountSKUID -eq "company:ENTERPRISEPACK" -and $_.Licenses.AccountSKUID -eq "company:EXCHANGEENTERPRISE"}).UserPrincipalName

$TotalP1 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:EXCHANGESTANDARD"}).ActiveUnits
$UsedP1 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:EXCHANGESTANDARD"}).ConsumedUnits
$AvailableP1 = $TotalP1 - $UsedP1

$TotalP2 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:EXCHANGEENTERPRISE"}).ActiveUnits
$UsedP2 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:EXCHANGEENTERPRISE"}).ConsumedUnits
$AvailableP2 = $TotalP2 - $UsedP2

$TotalE1 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:STANDARDPACK"}).ActiveUnits
$UsedE1 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:STANDARDPACK"}).ConsumedUnits
$AvailableE1 = $TotalE1 - $UsedE1

$TotalE3 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:ENTERPRISEPACK"}).ActiveUnits
$UsedE3 = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:ENTERPRISEPACK"}).ConsumedUnits
$AvailableE3 = $TotalE3 - $UsedE3

$TotalCRMBasic = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMPLAN2"}).ActiveUnits
$UsedCRMBasic = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMPLAN2"}).ConsumedUnits
$AvailableCRMBasic = $TotalCRMBasic - $UsedCRMBasic

$TotalCRMPro = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMSTANDARD"}).ActiveUnits
$UsedCRMPro = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMSTANDARD"}).ConsumedUnits
$AvailableCRMPro = $TotalCRMPro - $UsedCRMPro

$TotalCRMInstance = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMINSTANCE"}).ActiveUnits
$UsedCRMInstance = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:CRMINSTANCE"}).ConsumedUnits
$AvailableCRMInstance = $TotalCRMInstance - $UsedCRMInstance

$TotalBIFree = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWER_BI_STANDARD"}).ActiveUnits
$UsedBIFree = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWER_BI_STANDARD"}).ConsumedUnits
$AvailableBIFree = $TotalBIFree - $UsedBIFree

$TotalBIPro = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWER_BI_PRO"}).ActiveUnits
$UsedBIPro = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWER_BI_PRO"}).ConsumedUnits
$AvailableBIPro = $TotalBIPro - $UsedBIPro

$TotalATP = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:ATP_ENTERPRISE"}).ActiveUnits
$UsedATP = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:ATP_ENTERPRISE"}).ConsumedUnits
$AvailableATP = $TotalATP - $UsedATP

$TotalProjectEssentials = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:PROJECTESSENTIALS"}).ActiveUnits
$UsedProjectEssentials = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:PROJECTESSENTIALS"}).ConsumedUnits
$AvailableProjectEssentials = $TotalProjectEssentials - $UsedProjectEssentials

$TotalProjectPremium = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:PROJECTPREMIUM"}).ActiveUnits
$UsedProjectPremium = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:PROJECTPREMIUM"}).ConsumedUnits
$AvailableProjectPremium = $TotalProjectPremium - $UsedProjectPremium

$TotalPowerApps = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWERAPPS_VIRAL"}).ActiveUnits
$UsedPowerApps = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:POWERAPPS_VIRAL"}).ConsumedUnits
$AvailablePowerApps = $TotalPowerApps - $UsedPowerApps

$TotalStream = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:STREAM"}).ActiveUnits
$UsedStream = (Get-MsolAccountSku | where {$_.AccountSkuId -eq "company:STREAM"}).ConsumedUnits
$AvailableStream = $TotalStream - $UsedStream

$Email = @"
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cstyle%3E%0A%0Abody%20%7B%20font-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20color%3A%23434242%3B%7D%0ATABLE%20%7B%20font-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20border-width%3A%201px%3Bborder-style%3A%20solid%3Bborder-color%3A%20black%3Bborder-collapse%3A%20collapse%3B%7D%0ATR%20%7Bborder-width%3A%201px%3Bpadding%3A%2010px%3Bborder-style%3A%20solid%3Bborder-color%3A%20white%3B%20%7D%0ATD%20%7Bfont-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20border-width%3A%201px%3Bpadding%3A%2010px%3Bborder-style%3A%20solid%3Bborder-color%3A%20white%3B%20background-color%3A%23C3DDDB%3B%7D%0A.colorm%20%7Bbackground-color%3A%2358A09E%3B%20color%3Awhite%3B%7D%0A.colort%7Bbackground-color%3A%2358A09E%3B%20padding%3A20px%3B%20color%3Awhite%3B%20font-weight%3Abold%3B%7D%0A.colorn%7Bbackground-color%3Atransparent%3B%7D%0A%3C%2Fstyle%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="<style>" title="<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">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">$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">$AvailableP2</td>
</tr>
<tr>
   <td class="colorm">Office365 Enterprise E1:</td>
   <td style="text-align:center">$TotalE1</td>
   <td style="text-align:center">$UsedE1</td>
   <td style="text-align:center">$AvailableE1</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">$AvailableE3</td>
</tr>
<tr>
   <td class="colorm">Microsoft Dynamics CRM Online Basic:</td>
   <td style="text-align:center">$TotalCRMBasic</td>
   <td style="text-align:center">$UsedCRMBasic</td>
   <td style="text-align:center">$AvailableCRMBasic</td>
</tr>
<tr>
   <td class="colorm">Microsoft Dynamics CRM Online Professional:</td>
   <td style="text-align:center">$TotalCRMPro</td>
   <td style="text-align:center">$UsedCRMPro</td>
   <td style="text-align:center">$AvailableCRMPro</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">$AvailableCRMInstance</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">$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">$AvailableBIPro</td>
</tr>
<tr>
   <td class="colorm">Exchange Online Advance Thread Protection:</td>
   <td style="text-align:center">$TotalATP</td>
   <td style="text-align:center">$UsedATP</td>
   <td style="text-align:center">$AvailableATP</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">$AvailableProjectEssentials</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">$AvailableProjectPremium</td>
</tr>
<tr>
   <td class="colorm">Microsoft Power Apps and Flow:</td>
   <td style="text-align:center">$TotalPowerApps</td>
   <td style="text-align:center">$UsedPowerApps</td>
   <td style="text-align:center">$AvailablePowerApps</td>
</tr>
<tr>
   <td class="colorm">Microsoft Stream:</td>
   <td style="text-align:center">$TotalStream</td>
   <td style="text-align:center">$UsedStream</td>
   <td style="text-align:center">$AvailableStream</td>
</tr>

</table>

</body>
"@

if (($NoLicenseMailboxes -ne $null) -or ($DuplicateLicenseUsers -ne $null)){
   send-mailmessage `
      -To $To `
      -Subject "Licensing Report $(Get-Date -format dd/MM/yyyy)" `
      -Body $Email `
      -BodyAsHtml `
      -Priority high `
      -UseSsl `
      -Port 587 `
      -SmtpServer 'smtp.office365.com' `
      -From $From `
      -Credential $EmailCredentials
}

if ($error -ne $null){
   foreach ($value in $error){
      $ErrorEmailTemp = @"
<tr>
   <td class="colorm">$value</td>
</tr>
"@

      $ErrorEmailResult = $ErrorEmailResult + "`r`n" + $ErrorEmailTemp
   }

   $ErrorEmailUp = @"
<img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" data-wp-preserve="%3Cstyle%3E%0Abody%20%7Bfont-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20color%3A%23434242%3B%7D%0ATABLE%20%7Bfont-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20border-width%3A%201px%3Bborder-style%3A%20solid%3Bborder-color%3A%20black%3Bborder-collapse%3A%20collapse%3B%7D%0ATR%20%7Bborder-width%3A%201px%3Bpadding%3A%2010px%3Bborder-style%3A%20solid%3Bborder-color%3A%20white%3B%20%7D%0ATD%20%7Bfont-family%3ASegoe%2C%20%22Segoe%20UI%22%2C%20%22DejaVu%20Sans%22%2C%20%22Trebuchet%20MS%22%2C%20Verdana%2C%20sans-serif%20!important%3B%20border-width%3A%201px%3Bpadding%3A%2010px%3Bborder-style%3A%20solid%3Bborder-color%3A%20white%3B%20background-color%3A%23C3DDDB%3B%7D%0A.colorm%20%7Bbackground-color%3A%2358A09E%3B%20color%3Awhite%3B%7D%0Ah3%20%7Bcolor%3A%23BD3337%20!important%3B%7D%0A%3C%2Fstyle%3E" data-mce-resize="false" data-mce-placeholder="1" class="mce-object" width="20" height="20" alt="<style>" title="<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

   send-mailmessage `
      -To $To `
      -Subject "Licensing Report $(Get-Date -format dd/MM/yyyy) - WARNING" `
      -Body $ErrorEmail `
      -BodyAsHtml `
      -Priority high `
      -UseSsl `
      -Port 587 `
      -SmtpServer 'smtp.office365.com' `
      -From $From `
      -Credential $EmailCredentials
}

Reader Interactions

Comments

  2. Appreciate the post but there was an easier way.

    $listGetMsolAccountSku = Get-MsolAccountSku
    “”
    “Licenses Available”
    $listGetMsolAccountSku | % {
    $LicensesLeft = $_.ActiveUnits – $_.ConsumedUnits
    “$_ = $LicensesLeft “}

    and just pick to output it to an email. Oye.

    Reply

  4. One sec, didnt show the licenses but did the math. I will update it so it shows everything.

    Reply

    • Here is what you are looking for. This will automatically inject the AccountSkuId in every calculation. I’m probably going add some variation to this myself. Where it creates an array and asks me what of the available options of licenses that are available would i like to use when i’m on-boarding someone. 😉 for now i use this as a stopping point in my script if i have licenses to use.

      Enjoy.

      $listGetMsolAccountSku = Get-MsolAccountSku | Select-Object -property AccountSkuId, ActiveUnits, ConsumedUnits
      “”
      “Licenses Available”
      $listGetMsolAccountSku | % {
      $LicensesLeft = $_.ActiveUnits – $_.ConsumedUnits
      $licesnsename = $_.AccountSkuId
      “$licesnsename = $LicensesLeft”

      }

      Reply

      • And because when it comes to Powershell i have way to much fun. This is for the beginning of your on-board script, It will grab all the possibilities of licenses you have show you how many you have left. Let you select which one you want to use and then all you have to do is apply it to the user. Love Powershell.

        Excuse the crappy variable names as i just whipped this up and will make it prettier for colleagues later, but since you help me see the start i figured i would share.

        Remove-Variable iflictest
        #make your own array off of a list you created (if a-b is greater then 1 create list from list and add to list.)
        $listGetMsolAccountSku = Get-MsolAccountSku | Select-Object -property AccountSkuId, ActiveUnits, ConsumedUnits
        $listGetMsolAccountSku | % {
        $LicensesLeft = $_.ActiveUnits – $_.ConsumedUnits
        $licesnsename = $_.AccountSkuId
        $iflic = $Licensesleft -ge 1
        if ($iflic -eq ‘True’) {
        $iflictest += @(“$licesnsename | $LicensesLeft – Licenses Left”)}}

        $liclistchoice=$iflictest
        for ($i=0; $i – $liclistchoice.Length; $i++){
        Write-Host ‘Press’ $i ‘for’ $liclist[$i]
        }
        $licChoiceNumbers = Read-Host
        ## change string into character array to be able to iterate throught each selection
        $licChoiceNumbers1 = [int[]](($licChoiceNumbers -split ”) -ne ”)

        $liclistchoice = Get-MsolAccountSku | Select-Object -expandproperty AccountSkuId
        $licChoiceToEnter = $liclistChoice[$licChoiceNumbers1[0]]

        Reply

        • Hello CapCowboy,

          This is great that you like it.
          You can change the script as much as you want to match your like. This just one solution with a specific outcome.

          Thanks
          Stephanos

          Reply

    • Hello,

      Currently the script is written to work on Azure Automation. I haven’t checked the process for MFA enabled account. It is included in my plans to check on how to run scheduled scripts with MFA accounts.

      Reply

Trackbacks

Leave a 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.