Get email alerts about unused Office 365 licenses with Azure Functions, Azure Storage and Microsoft Flow

In this scenario, we’ll use an Azure Function, Azure Storage Tables and Queues, and Microsoft Flow to report and alert on unused Office 365 licenses in customer tenants.

These steps build on a previous post on connecting to Office 365 with an Azure Function via PowerShell.

See the bottom of this article for the complete script.

Create an Azure Function that can connect to Office 365

You’ll need to follow the steps in this article to create an Azure Function App Service, with a timer triggered PowerShell Azure function called GetUnusedLicenses.

Before moving on, you should have completed the following:

• Create a new Azure Function app service
• Created a new timer triggered PowerShell function – call this one GetUnusedLicenses
• Set the function to run as often as required (eg Monday – Friday 9:30AM GMT time)
• Downloaded the MSOnline PowerShell module locally, then uploaded it to your Azure Function via FTP
• Secured your Office 365 Credentials in your Azure Function properties

Your new GetUnusedLicenses function should contain this code:

Write-Output "PowerShell Timer trigger function executed at:$(get-date)";

$FunctionName = 'GetUnusedLicenses'
$ModuleName = 'MSOnline'
$ModuleVersion = '1.1.166.0'
$username = $Env:user
$pw = $Env:password
#import PS module
$PSModulePath = "D:\home\site\wwwroot\$FunctionName\bin\$ModuleName\$ModuleVersion\$ModuleName.psd1"
$res = "D:\home\site\wwwroot\$FunctionName\bin"

Import-module $PSModulePath

# Build Credentials
$keypath = "D:\home\site\wwwroot\$FunctionName\bin\keys\PassEncryptKey.key"
$secpassword = $pw | ConvertTo-SecureString -Key (Get-Content $keypath)
$credential = New-Object System.Management.Automation.PSCredential ($username, $secpassword)

# Connect to MSOnline

Connect-MsolService -Credential $credential

Working with Azure Storage in PowerShell

We’ll be using an Azure storage account for two things: reporting and alerts.

Since it’s not practical to output and retrieve CSVs in an Azure Function, we’ll be using an Azure Storage Table to store our results. This requires a little more setup than a standard CSV output, though it’s a very cheap storage service that’s also a native data source for Power BI.

Azure Storage Queues will be used for our alerts. In this scenario, when an unassigned license is detected, we’ll add it to an Azure Storage Queue that’s monitored by a Microsoft Flow.

When you create an Azure Function app service, a storage account is generated for you automatically. It’ll be located under Storage Accounts in the Azure Portal with the same name as your Azure Function. You can use this one, or you can create your own.

In our case, we’ll be making a new storage account that we’ll be using for all Office 365 reporting.

How to create a new Azure Storage account

1. While logged in to portal.azure.com, click on the Storage Accounts icon on the left menu
2. Click +Add to create a new Storage Account.
3. Give it a name, we’re calling this one gcitso365reports. Complete the other fields as you prefer, or use these values as a guide.
4. Wait for it to deploy, then open it up in the portal.

Using Azure Storage Explorer

I find it’s easier to browse Azure Storage accounts using the Azure Storage Explorer.

1. Download and install Azure Storage Explorer from www.storageexplorer.com
2. Click Open in Explorer from within your new Azure Storage account or sign in to Azure Storage Explorer and browse to your new storage account on the left menu.
3. Select the name of your Storage account, then retrieve your Azure Storage Account’s Primary Key from the bottom left of Azure Storage Explorer under Actions
4. This could also be retrieved via the Azure Portal under Access Keys

Connect to your Azure Storage Account in Azure function

Unlike the MSOnline PowerShell module, many Azure PowerShell modules are available out of the box in Azure Functions. Because of this, we can use the Azure Storage cmdlets right away.

In this step, we’ll do the following:

• Add Storage Account name and primary key to Azure Function
• Define Azure Storage Queue and Azure Storage Table
• Create Azure Storage Queue Message
• Create Azure Storage Table Entity

1. Return to your Azure Function and click the name of your function, then Platform features, then Application Settings.
2. Create two new key-value pairs under App Settings. One for your storage account name (storageAccount) and another for your primary key (storageAccountKey). Paste in your primary key and enter your storage account name.
3. Once the settings have applied, return to your Azure Function and add the new environment variables to the top of the script.

   $storageAccount = $Env:storageAccount
   $primaryKey = $Env:storageAccountKey
   

4. Then add in two new variables that we’ll use for your new Azure Storage queue and table names

   $QueueName = "unusedlicenses"
   $TableName = "licenseStatus"
   

   

5. Under these variables, copy and paste the following script to define your Azure storage context. This gets your storage queue and table – or creates them if they don’t exist.

   # Create storage account context
   
   $Ctx = New-AzureStorageContext $storageAccount -StorageAccountKey $primaryKey
   
   # Get Table or create it if it doesn't exist.
   
   $Table = Get-AzureStorageTable –Name $TableName -Context $Ctx -ErrorAction Ignore
   
   if ($Table -eq $null)
   {
   	$Table = New-AzureStorageTable –Name $TableName -Context $Ctx
   }
   
   # Get Queue or create it if it doesn't exist.
   
   $Queue = Get-AzureStorageQueue –Name $QueueName -Context $Ctx -ErrorAction Ignore
   
   if ($Queue -eq $null)
   {
   	$Queue = New-AzureStorageQueue –Name $QueueName -Context $Ctx
   }
   

6. To add entries to our Azure Storage Table, we need to create a PowerShell function that collects the data, defines the table entity and submits it to the table, overwriting any existing values for that customer.
   Paste the following code into your function below your queue and table definitions.

   # Define Table Entity and add it to the table
   
   function Add-Entity() {
   	[CmdletBinding()]
   	param(
   		$table,
   		[String]$partitionKey,
   		[String]$rowKey,
   		[String]$tenantId,
   		[String]$companyName,
   		[String]$AccountName,
   		[String]$SkuPartNumber,
   		[String]$AccountSkuId,
   		[int]$ActiveUnits,
   		[int]$ConsumedUnits,
   		[int]$UnusedUnits,
   		[int]$LockedOutUnits,
   		[int]$SuspendedUnits,
   		[int]$WarningUnits
   	)
   
   	$entity = New-Object -TypeName Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity -ArgumentList $partitionKey, $rowKey
   	$entity.Properties.Add("TenantId", $tenantId)
   	$entity.Properties.Add("CompanyName", $companyName)
   	$entity.Properties.Add("AccountName", $AccountName)
   	$entity.Properties.Add("SkuPartNumber", $SkuPartNumber)
   	$entity.Properties.Add("AccountSkuId", $AccountSkuId)
   	$entity.Properties.Add("ActiveUnits", $ActiveUnits)
   	$entity.Properties.Add("ConsumedUnits", $ConsumedUnits)
   	$entity.Properties.Add("UnusedUnits", $UnusedUnits)
   	$entity.Properties.Add("LockedOutUnits", $LockedOutUnits)
   	$entity.Properties.Add("SuspendedUnits", $SuspendedUnits)
   	$entity.Properties.Add("WarningUnits", $WarningUnits)
   
   	$result = $table.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::InsertOrReplace($entity))
   }
   

7. Now paste in the rest of the function under the Connect-MsolService cmdlet:

   $clients = Get-MsolPartnerContract -All
    
   foreach ($client in $clients) {
    
   	$licenses = Get-MsolAccountSku -TenantId $client.TenantId
    
   	foreach ($license in $licenses){
    
   		$UnusedUnits = $license.ActiveUnits - $license.ConsumedUnits
   
   		Add-Entity -Table $Table -PartitionKey $client.TenantId -RowKey $license.AccountSkuId -TenantId $client.TenantId -CompanyName $client.Name `
   			-AccountName $license.AccountName -SkuPartNumber $license.SkuPartNumber -AccountSkuId $license.AccountSkuId `
   			-ActiveUnits $license.ActiveUnits -ConsumedUnits $license.ConsumedUnits -UnusedUnits $UnusedUnits `
   			-LockedOutUnits $license.LockedOutUnits -SuspendedUnits $license.SuspendedUnits -WarningUnits $license.WarningUnits
   
   		# Modify this IF statement to exclude customers or sku types
   
   		if($UnusedUnits -gt 0 -and $UnusedUnits -lt 50 -and $license.SkuPartNumber -notmatch "SKU you want to exclude" -and $client.Name -notmatch "Customer you want to exclude") {
   
   			$QueueMessageText = "$($Client.Name) ($($Client.defaultdomainname)) has $UnusedUnits unused $($license.SkuPartNumber) license(s)"
   
   			if ($Queue -ne $null) {
   
   				# Create a new message using a constructor of the CloudQueueMessage class.
    
   				$QueueMessage = New-Object -TypeName Microsoft.WindowsAzure.Storage.Queue.CloudQueueMessage -ArgumentList $QueueMessageText
   
   				# Add a new message to the queue.
   
   				$Queue.CloudQueue.AddMessage($QueueMessage)
   			}
    
   		}
   
   	}
    
   }
   

8. Note: if there are some SKU types or customers that you don’t want to receive notifications for, you can modify the first IF statement in the above script block to exclude these.
9. Click Save and Run to start the function
10. Switch to Azure Storage Explorer to view your new storage queue and table
11. The licenseStatus table will contain license information for all of your customers.
12. The unusedlicenses queue will contain messages alerting you to unused Office 365 licenses.
13. In the next section, we’ll use Microsoft Flow to send these messages via email.

Set up Microsoft Flow for email alerts

What is Microsoft Flow?

Microsoft Flow is an automation service within Office 365. It’s similar to IFTTT, Zapier, or Azure Logic Apps. It’s a user-friendly way to automate tasks using triggers and connectors across a variety of Microsoft and third-party services.

In this step we’ll set up a flow that executes whenever a message is added to our Azure Storage Queue.

How to set up a Microsoft Flow that connects to an Azure Storage Queue

1. Go to flow.microsoft.com and sign in with your Office 365 credentials
2. Click My Flows, then Create from Blank
3. Give your Flow a name, search for ‘Queue‘, then select ‘When there are messages in a queue‘
4. Define a name for your Azure Storage Account connection, paste in your storage account name and key, then click Create
5. Make sure the unusedlicenses queue is selected, then click New step
6. Click Add an action
7. Select Office 365 Outlook
8. Choose Send an email
9. You may need to sign into your Office 365 account, or wait for it to complete sign in for you.
10. Enter the email address or select the contact that you would to be notified, then complete the other fields. Use the queue message text by clicking a field, then selecting Message text.
11. Click New Step, Add an action, then search for ‘queue‘ again. Choose ‘Delete message‘ from the available actions
12. Enter the Message Id and POP message to make sure the messages are removed from the queue after the flow runs.
13. Next, click Create flow on the top right, and wait for it to create.
14. You can now manage your new flow via the browser, or using the Microsoft Flow mobile apps.

Complete script for the Azure Function

Write-Output "PowerShell Timer trigger function executed at:$(get-date)";


$FunctionName = 'GetUnusedLicenses'
$ModuleName = 'MSOnline'
$ModuleVersion = '1.1.166.0'
$username = $Env:user
$pw = $Env:password
$storageAccount = $Env:storageAccount
$primaryKey = $Env:storageAccountKey
$QueueName = "unusedlicenses"
$TableName = "licenseStatus"

# Create storage account context

$Ctx = New-AzureStorageContext $storageAccount -StorageAccountKey $primaryKey

# Get Table or create it if it doesn't exist.
$Table = Get-AzureStorageTable –Name $TableName -Context $Ctx -ErrorAction Ignore

if ($Table -eq $null)
{
	$Table = New-AzureStorageTable –Name $TableName -Context $Ctx
}

# Get Queue or create it if it doesn't exist.

$Queue = Get-AzureStorageQueue –Name $QueueName -Context $Ctx -ErrorAction Ignore

if ($Queue -eq $null)
{
	$Queue = New-AzureStorageQueue –Name $QueueName -Context $Ctx
}

# Define Table Entity and add it to the table

function Add-Entity() {
	[CmdletBinding()]
	param(
		$table,
		[String]$partitionKey,
		[String]$rowKey,
		[String]$tenantId,
		[String]$companyName,
		[String]$AccountName,
		[String]$SkuPartNumber,
		[String]$AccountSkuId,
		[int]$ActiveUnits,
		[int]$ConsumedUnits,
		[int]$UnusedUnits,
		[int]$LockedOutUnits,
		[int]$SuspendedUnits,
		[int]$WarningUnits
	)

	$entity = New-Object -TypeName Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity -ArgumentList $partitionKey, $rowKey
	$entity.Properties.Add("TenantId", $tenantId)
	$entity.Properties.Add("CompanyName", $companyName)
	$entity.Properties.Add("AccountName", $AccountName)
	$entity.Properties.Add("SkuPartNumber", $SkuPartNumber)
	$entity.Properties.Add("AccountSkuId", $AccountSkuId)
	$entity.Properties.Add("ActiveUnits", $ActiveUnits)
	$entity.Properties.Add("ConsumedUnits", $ConsumedUnits)
	$entity.Properties.Add("UnusedUnits", $UnusedUnits)
	$entity.Properties.Add("LockedOutUnits", $LockedOutUnits)
	$entity.Properties.Add("SuspendedUnits", $SuspendedUnits)
	$entity.Properties.Add("WarningUnits", $WarningUnits)

	$result = $table.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::InsertOrReplace($entity))
}



#import PS module

$PSModulePath = "D:\home\site\wwwroot\$FunctionName\bin\$ModuleName\$ModuleVersion\$ModuleName.psd1"
$res = "D:\home\site\wwwroot\$FunctionName\bin"

Import-module $PSModulePath

# Build Credentials

$keypath = "D:\home\site\wwwroot\$FunctionName\bin\keys\PassEncryptKey.key"
$secpassword = $pw | ConvertTo-SecureString -Key (Get-Content $keypath)
$credential = New-Object System.Management.Automation.PSCredential ($username, $secpassword)


# Connect to MSOnline

Connect-MsolService -Credential $credential

 
$clients = Get-MsolPartnerContract -All
 
foreach ($client in $clients) {
 
	$licenses = Get-MsolAccountSku -TenantId $client.TenantId
 
	foreach ($license in $licenses){
 
		$UnusedUnits = $license.ActiveUnits - $license.ConsumedUnits

		Add-Entity -Table $Table -PartitionKey $client.TenantId -RowKey $license.AccountSkuId -TenantId $client.TenantId -CompanyName $client.Name `
			-AccountName $license.AccountName -SkuPartNumber $license.SkuPartNumber -AccountSkuId $license.AccountSkuId `
			-ActiveUnits $license.ActiveUnits -ConsumedUnits $license.ConsumedUnits -UnusedUnits $UnusedUnits `
			-LockedOutUnits $license.LockedOutUnits -SuspendedUnits $license.SuspendedUnits -WarningUnits $license.WarningUnits

		# Modify this IF statement to exclude customers or sku types

		if($UnusedUnits -gt 0 -and $UnusedUnits -lt 50 -and $license.SkuPartNumber -notmatch "SKU you want to exclude" -and $client.Name -notmatch "Customer you want to exclude") {

			$QueueMessageText = "$($Client.Name) ($($Client.defaultdomainname)) has $UnusedUnits unused $($license.SkuPartNumber) license(s)"

			if ($Queue -ne $null) {

				# Create a new message using a constructor of the CloudQueueMessage class.
 
				$QueueMessage = New-Object -TypeName Microsoft.WindowsAzure.Storage.Queue.CloudQueueMessage -ArgumentList $QueueMessageText

				# Add a new message to the queue.

				$Queue.CloudQueue.AddMessage($QueueMessage)
			}
 
		}

	}
 
}