Pages

Monday, July 25, 2022

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:

image

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

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

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:

image

With the certificate uploaded, proceed to copy the Thumbprint:

image

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:

image

Select Application permissions:

image

Add the following Application permissions:

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

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.

image

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"

image

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 friend 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:

image

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.

image

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.

image

image

With the Function App created, proceed to configure 2 now 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
image

appId

image

WEBSITE_LOAD_CERTIFICATES

image

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:

image

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

image

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.*'

image

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

image

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

image

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

image

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

image

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

The client name:

image

The storage account URI:

image

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"

}

image

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

image

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

image

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.

image

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:

image

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

image

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:

image

Then edit the triggers section:

image

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

image

Select the Function App that was created:

image

Select the trigger that was created:

image

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

{

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

}

image

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

image

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

image

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

image

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.

No comments: