Post Notifications About Unused Office 365 Licenses to Teams using Azure Runbooks
Table of Contents
I have written several articles on using PowerShell to send alerts and notifications to Microsoft Teams, but up until now they were set up using only the task scheduler. As more and more companies move to the cloud I wanted to see how I could do cloud infrastructure alerting as well. In this article I am using an Azure RunBook to connect to my Office 365 tenant, parse my licenses, and return any that need reconciliation. If you get your Office 365 licenses from a CSP or any other kind of reseller, you may get charged for all of your licenses, applied or not. So it’s a good thing to make sure you don’t have any extra ones lying around.
Set Up the Azure Environment
Resource Group, Runbook and Automation Account Creation
I created a script that you can just change the variables for and it will create the following in your Azure tenant:
- Automation Account
- Runbook
- Resource Group
- Automation Account Credential (Used to connect to Office 365)
In my example, I am creating a new resource group called “rg-automation” that will contain my runbook and automation account. Also, I am making an Automation credential that the runbook will use to connect to Office 365. All of my assets will be in the North Central US region.
#Automation account name [System.String]$automationAccountName = "PSAutomationAccount" #Runbook name [System.String]$runbookName = "Office_365_Runbook" #Resource group name to store everything in [System.String]$RGName = "rg-automation" #Location [System.String]$Location = "North Central US" #Account the runbook will use to connect to Office 365 [System.String]$AutomationCredUser = "[email protected]" #The Accounts password that the runbook will use to connect to Office 365 [System.Security.SecureString]$AutomationCredPassword = ConvertTo-SecureString "P@ssw0rd!" -AsPlainText -Force #Automation account's credential name [System.String]$AutomationAccountCredName = "Office 365 Creds" #Connect-AzAccount #Create the Resource Group New-AzResourceGroup -Name $RGName -Location $Location #Make the automation account New-AzAutomationAccount -ResourceGroupName $RGName -Location $Location -Name $automationAccountName -Plan "Free" #Create new automation runbook New-AzAutomationRunbook -AutomationAccountName $automationAccountName -Name $runbookName -ResourceGroupName $RGName -Type PowerShell #Create and store automation account credentials [System.Management.Automation.PSCredential]$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $AutomationCredUser, $AutomationCredPassword New-AzAutomationCredential -AutomationAccountName $automationAccountName -Name $AutomationAccountCredName -Value $cred -ResourceGroupName $RGName
Import AzureAD Module to the Runbook
Next, we must import the AzureAD module to the automation account so we can use all the cmdlets available in the module.
In the Azure Portal, go to your newly create Automation Account and select Modules
Click Browse Gallery and search for AzureAD and select Import
Configure Incoming Webhook
To allow PowerShell to send data to your Teams Channel you will need to configure an incoming Webhook.
- In your Team, click on the channel you want the messages to be sent to and click the ellipses (three dots) and select Connectors
- In the Incoming WebHook, click Add. If you do not see it on the main page you will have to search for it
- Give you webhook a good name. This is what users will see in the Teams chat. Upload an image and then press “Create”
- Copy the URL and save it for later, it will be needed. Click “Done” when you have saved the URL in a safe spot.
- Back in the Teams channel you can see that the webhook has been created.
Create the Azure Runbook
In the Azure Portal, go to your newly create Automation Account and select Runbooks, you will see your newly created RunBook you created earlier
Click on your Runbook and then select Edit
Modify the script to best fit your needs
- The $IgnoreSkus variable will contain any SKUs you don’t want to alert on. Many times a tenant will contain SKUs that they are trialing or are free so it will always report that there are more available than assigned.
- The $URI will contain the webhook URL we got above
- The $AutomationAccounrCredName is the credential we created earlier for our Automation Account
- The $ItemImage is the image that will be in your webhook, in my example I have a light blue license icon
- The $Sku is a hash table that contains SKUs and friendly names. The script will attempt to convert the SKU in Office 365 to a user friendly name
The script will also not send anything if there are 0 licenses that need reconsiliation.
<#
.NOTES
===========================================================================
Created on: 9/17/2019 1:46 AM
Created by: Bradley Wyatt
Organization: The Lazy Administrator
===========================================================================
.DESCRIPTION
Runbook script to send message to Teams for any extra Office 365 licenses that need to be reconciled
#>
[System.String]$AutomationAccountCredName = "Office 365 Creds"
[System.Management.Automation.PSCredential]$Credential = Get-AutomationPSCredential -Name $AutomationAccountCredName
#Any SKUs you don't want to report on, usually trial SKUs which have 10k or so free licenses
[System.Array]$IgnoreSkus = ("MS_TEAMS_IW", "FLOW_FREE", "WINDOWS_STORE")
[System.String]$ItemImage = 'https://cdn.pixabay.com/photo/2017/09/27/21/05/license-icon-2793454_960_720.png'
[System.String]$uri = "https://outlook.office.com/webhook/eee03-4fae-add9-17bf369d1101@6438b2c9-54e9f00c24b5dc1f/IncomingWebhook/aaed1267dd9147789fbed43075f4c3d4/5bcffade-48a2-8096-390a9090555c"
[System.Int32]$IntCount = 0
$ArrayTable = New-Object 'System.Collections.Generic.List[System.Object]'
[System.Collections.Hashtable]$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" = "CRM for Partners"
"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 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"
"CRMSTORAGE" = "Microsoft Dynamics CRM Online Additional Storage"
"SMB_APPS" = "Microsoft Business Apps"
"MICROSOFT_BUSINESS_CENTER" = "Microsoft Business Center"
"DYN365_TEAM_MEMBERS" = "Dynamics 365 Team Members"
"STREAM" = "Microsoft Stream Trial"
"EMSPREMIUM" = "Enterprise Mobility + Security E5"
"AAD_PREMIUM_P2" = "Azure Active Directory P2"
"AAD_PREMIUM_P1" = "Azure Active Directory P1"
"Win10_VDA_E3" = "Windows 10 Enterprise E3"
"NBPROFESSIONALFORCRM" = "Microsoft Social Listening Professional"
"PROJECT_ESSENTIALS" = "Project Lite"
"SQL_IS_SSIM" = "Power BI Information Services"
"WACONEDRIVESTANDARD" = "OneDrive Pack"
"EXCHANGEARCHIVE" = "Exchange Online Archiving"
}
Connect-AzureAD -Credential $Credential
Get-AzureADSubscribedSku | ForEach-Object {
If ((($_.PrepaidUnits).Enabled) -gt ($_.ConsumedUnits) -and ($IgnoreSkus -notcontains $_.SkuPartNumber))
{
$IntCount++
$TextLic = $Sku.Item($_.SkuPartNumber)
If ($Null -eq $TextLic)
{
$TextLic = $_.SkuPartNumber
}
$AvailableCount = ((($_.PrepaidUnits).Enabled) - ($_.ConsumedUnits))
$Section = @{
activityTitle = "License: $TextLic"
activitySubtitle = "-----------------------------------------------"
activityText = "The license, `'$TextLic`' has $AvailableCount outstanding licenses that can be removed"
activityImage = $ItemImage
facts = @(
@{
name = 'License Name'
value = $TextLic
},
@{
name = 'Sku'
value = $_.SkuPartNumber
},
@{
name = 'Total Licenses'
value = ($_.PrepaidUnits).Enabled
},
@{
name = 'Assigned'
value = $_.ConsumedUnits
},
@{
name = 'Available'
value = $AvailableCount
}
)
}
$ArrayTable.add($section)
}
}
If ($IntCount -eq 1)
{
$Text = "There is 1 license that need reconciliation"
}
else
{
$Text = "There are $IntCount licenses that need reconciliation"
}
$body = ConvertTo-Json -Depth 8 @{
title = "Office 365 Unused License Report"
text = $Text
sections = $ArrayTable
}
If ($IntCount -gt 0)
{
Invoke-RestMethod -uri $uri -Method Post -body $body -ContentType 'application/json'
}
Disconnect-AzureAD -Confirm:$False
You can test it by selecting the Test Pane button when you are editing your runbook. In my case I can see it worked and I have my message in Teams
Once your’ve finished, press Publish to publish your runbook. Then go to Schedules and create a schedule for your runbook. In my examply I have it running 1 time a day at 5PM
Things to Remember
The webhook in Teams has a message size limited to 25 KB. If your message exceeds the limit, Teams responds with a HTTP 413
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.