Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App and Logic Apps

I received quite a few requests about how to automate the process of my previous post where I described how to use Microsoft Graph PowerShell SDK to generate a Office 365 / Microsoft 365 license report that had friendly names:

Using Microsoft Graph PowerShell SDK to retrieve Office 365 / Microsoft 365 license usage with friendly names
http://terenceluk.blogspot.com/2022/07/using-microsoft-graph-powershell-sdk-to.html

I have to be honest that I haven’t actually converted my old reports with Msol online so I took the time over the weekend to create a new report and will now demonstrate the process.

Step #1 - Creating a Service Principal for App-Only authentication for Microsoft Graph PowerShell SDK (Connect-MgGraph)

The first step is to set up a App Registration / Service Principal so the Azure Function App can authenticate and sign into Microsoft Graph. The following documentation describes the process of registering the application:

Use app-only authentication with the Microsoft Graph PowerShell SDK
https://docs.microsoft.com/en-us/powershell/microsoftgraph/app-only?view=graph-powershell-1.0&tabs=azure-portal

… but does not include the process of creating a certificate so I will include a few other posts I’ve written which will demonstrate how to create a self-signed certificate (both the .cert and .pfx) for application authentication:

Step #3 – Generate a self-signed certificate for the application that will be authenticating
http://terenceluk.blogspot.com/2021/05/setting-up-app-only-authentication-for.html

Step #2 – Create a self-signed certificate on the local Windows desktop and export it to PFX with the private key
http://terenceluk.blogspot.com/2022/02/creating-service-principal-to-connect.html

The following are screenshots of the process:

Create a new App Registration as such:

Copying the following configuration once the App Registration has been created:

• Application (client) ID
• Directory (tenant) ID

Navigate to the Certificates & secrets blade, click on Upload certificate, select the .cer export of the certificate (this file does not contain the private key), add a description, and upload:

With the certificate uploaded, proceed to copy the Thumbprint:

The new App Registration will need to be configured with the required permissions by navigating to API permissions, click on App a permission, and select Microsoft Graph:

Select Application permissions:

Add the following Application permissions:

• Directory.Read.All
• Directory.ReadWrite.All
• Organization.Read.All
• Organization.ReadWrite.All

The configured permissions should be listed as shown in the screenshot below and before proceeding, make sure you click on Grant admin consent for organization or else the permissions will be come into effect.

**Note that User.Read is a Delegate permission and should already be added by default.

The App Registration / Service Principal that will be used to connect to Microsoft Graph should now be in place. You should be able to test authentication on a workstation that has the certificate along with its private key by using the following cmdlet:

Connect-MgGraph -ClientID "xxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxx" -TenantId "xxxxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxxxxxx" -CertificateThumbprint "3548c8xxxxxxxxxxxxxxxxxxxxxb2f8affa214"

Step #2 – Create a Storage Account Container that will provide the CSV file containing the product friendly names

Microsoft provides a CSV file that contains the product friendly names from the following document: https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference

Proceed to download the CSV file and place the file into a storage account as such:

The function app will retrieve this CSV file and use it to map the friendly names to the licenses.

Step #3 – Create a Function App that will retrieve Office 365 / Microsoft 365 license usage with friendly names and return it in HTML format

With the App Registration / Service Principal in place, the next step is to create a Function App that will retrieve Office 365 / Microsoft 365 license usage with friendly names and return it in HTML format. This Function App collects the data that will in turn be call by a Logic App to generate an email and send the report off to an email address.

Proceed to create a Function App with the following parameters:

Publish: Code
Runtime stack: PowerShell Core
Version: 7.2
Operating System: Windows

Configure the rest of the parameters as required by the environment.

With the Function App created, proceed to configure 2 new application settings that will contain the value of the previously copied Application (client) ID of the service principal and the thumbprint of the certificate we’ll be using to authentication to Microsoft Graph. The names I’ll use for these applications will be:

• appID
• WEBSITE_LOAD_CERTIFICATES

appId

WEBSITE_LOAD_CERTIFICATES

With the application settings configured, proceed to upload certificate file which contains the private key (.pfx) that the Function App will use to authenticate and sign into Microsoft Graph:

Confirm that the certificate has successfully uploaded and is in a healthy state:

Next proceed to configure the requirements.psd1 file in the App files blade so the appropriate modules will be loaded for the PowerShell code in the function app. Note that I choose to import the specific modules required for the cmdlets Connect-MgGraph (Microsoft.Graph.Authentication) and Get-MgSubscribedSku (Microsoft.Graph.Identity.DirectoryManagement) because the Microsoft.Graph modules has 38 sub modules in it and I was not able to get the function app code to run by importing that.

'JoinModule' = '3.*'
'Microsoft.Graph.Identity.DirectoryManagement' = '1.*'
'Microsoft.Graph.Authentication' = '1.*'

---------------------------------------------------------------------------------------------------------------------------

Update – Sept 5, 2022

The versions of the modules below use at the time of this original post was 1.10.0 and I realized that later versions such as 1.11.1 no longer worked with certificate authentication:

• Microsoft.Graph.Identity.DirectoryManagement
• Microsoft.Graph.Authentication

To correct the issue, please refer to this post:

Azure Function App using certificate authentication falls to authenticate when executing Connect-MgGraph
http://terenceluk.blogspot.com/2022/09/azure-function-app-using-certificate.html

---------------------------------------------------------------------------------------------------------------------------

Application Insights is extremely useful for troubleshooting issues with the Function App so I would highly recommend turning it on:

With the prerequisites configured for the Function App, proceed to create the actual function trigger:

Select HTTP trigger as the template and provide a meaningful name:

With the trigger created, navigate to Code + Test and paste the following code into run.ps1:

https://github.com/terenceluk/Microsoft-365/blob/main/Administration/Get-M365-License-Report-Function.ps1

The following are changes you’ll need to apply to the code:

The client name:

The storage account URI:

With the function app code in place, proceed to use the Test/Run feature to test it. Note that the function app expects the tenant ID to be passed to it so the Body of the test should include the following:

{

"tenant": "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

}

The following log entries will be displayed if Application Insights is turned on:

Confirm the HTTP response code of 200 OK and the HTTP response content results:

Step #4 – Create a Logic App that is scheduled and will call the Azure Function App to retrieve the license report and then send it out

With the Azure Function App created and tested, proceed to create the Logic App that will be scheduled, calls the Function App for the HTML license report and then email it out.

Navigate to the Logic app designer blade and begin to configure the steps for the Logic App. The following are the steps we’ll be configuring:

The first is the Recurrence step that will schedule this logic app to run on the last day of each month:

Note that the GUI doesn’t provide the required controls so we’ll be using the JSON code provided in this document for the configuration: https://docs.microsoft.com/en-us/azure/logic-apps/concepts-schedule-automated-recurring-tasks-workflows#run-once-at-last-day-of-the-month

Click on Code View:

Then edit the triggers section:

Create an additional step by clicking on the + button and select Add an action then type in Function:

Select the Function App that was created:

Select the trigger that was created:

Place the body containing the tenant ID into the Request Body:

{

"tenant": "xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

}

Proceed to create two additional steps:

1. Initialize variable
2. Set variable

These two steps will place the retrieved HTML report into the body of the email:

Initialize variable

Name: EmailBody
Type: String
Value: <leave blank>

Set variable

Name: EmailBody
Value: Select the Body

Add the last step that will email this report to the email address required:

Proceed to use the Run Trigger feature to execute the Logic App and confirm that the report is generated and sent:

I hope this helps anyone who may be looking for instructions on how to configure automated reports. The task may seem trivial but it took me a few hours to troubleshoot the issues.