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

Connect Azure Function to Office 365, 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 menuClick Azure Storage Accounts
  2. Click +Add to create a new Storage Account.Add Azure 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.Create New Azure Storage Account
  4. Wait for it to deploy, then open it up in the portal.Wait For Azure Storage Account To Deploy

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.Open Azure Storage Account In Storage ExplorerBrowse Azure Storage Account
  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 ActionsCopy Primary Key From Azure Storage Account
  4. This could also be retrieved via the Azure Portal under Access KeysRetrieve Azure Storage Account Primary Key Via Azure Portal

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.Browse To Application Settings Via Platform Features In Azure Function
  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.Add Storage Account Key And Name To Application Settings
  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"
    

    Add Variables To Azure Function

  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 functionSave And Run Azure Function
  10. Switch to Azure Storage Explorer to view your new storage queue and tableView New Azure Storage Table And Queue
  11. The licenseStatus table will contain license information for all of your customers.See Office 365 Licensing In Azure Storage Table
  12. The unusedlicenses queue will contain messages alerting you to unused Office 365 licenses.See Unused Licenses In Azure Storage Queue
  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 credentialsGo To Microsoft Flow Website
  2. Click My Flows, then Create from BlankClick My Flows Create From Blank Microsoft Flow
  3. Give your Flow a name, search for ‘Queue‘, then select ‘When there are messages in a queueCreate New Microsoft Azure Storage Queue Connector
  4. Define a name for your Azure Storage Account connection, paste in your storage account name and key, then click CreateDefine Microsoft Azure Storage Queue Connection
  5. Make sure the unusedlicenses queue is selected, then click New stepSpecify Unused Licenses Azure Storage Queue
  6. Click Add an actionCreate New Step In Microsoft Flow
  7. Select Office 365 OutlookCreate New Action In Microsoft Flow
  8. Choose Send an emailSend An Email Via Office 365 Outlook
  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.Use Message Text To Add Queue Message To Office 365 Email
  11. Click New Step, Add an action, then search for ‘queue‘ again. Choose ‘Delete message‘ from the available actionsChoose Delete From Queue Option To End Microsoft Flow
  12. Enter the Message Id and POP message to make sure the messages are removed from the queue after the flow runs.Specify Message ID And Pop Message In Microsoft Flow
  13. Next, click Create flow on the top right, and wait for it to create.Microsoft Flow Is Running
  14. You can now manage your new flow via the browser, or using the Microsoft Flow mobile apps.Manage Microsoft Flow

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)
			}
 
		}

	}
 
}

Was this article helpful?

Related Articles