• 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 PowerShell License Report

Office 365 PowerShell License Report

31/01/2018 by Stephanos 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:

  • Send Email using PowerShell
  • Email error variable in PowerShell
  • 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:

$Credentials = Get-AutomationPSCredential -Name 'Admin-User'
$EmailCredentials = Get-AutomationPSCredential -Name 'Email-User'
$To = 'User1@domain.com','User2@domain.com'
$From = 'Email-User@domain.com'
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 = @"
<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">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 = @"
    <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 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
}

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. CapCowboy says

    25/10/2018 at 20:44

    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
  2. CapCowboy says

    25/10/2018 at 21:01

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

    Reply
    • CapCowboy says

      25/10/2018 at 22:31

      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
      • CapCowboy says

        26/10/2018 at 00:15

        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
        • Stephanos says

          26/10/2018 at 17:33

          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
  3. pd679 says

    14/11/2018 at 07:09

    Hi , How to use this script if the account being used is MFA enabled ?

    Reply
    • Stephanos says

      14/11/2018 at 09:59

      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

  1. Office 365 PowerShell License Report - How to Code .NET says:
    31/01/2018 at 19:06

    […] by /u/SConstantinou [link] […]

    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