Updated: Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App (using a system assigned managed identity) and Logic Apps

One of my colleagues who used my previous blog post:

Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App and Logic Apps
https://terenceluk.blogspot.com/2022/07/create-automated-report-for-office-365.html

… recently informed me that his Function App would fail to run every couple of months due to the issue I mentioned in my other blog post:

Azure Function App fails with: “ERROR: Assembly with same name is already loaded”
https://terenceluk.blogspot.com/2022/10/azure-function-app-fails-with-error.html

After giving the problem a bit of thought, what I recommended was to change the authentication for connecting to Microsoft Graph (Connect-MgGraph) in the function app from certificate authentication to using a Managed Identity instead. This post serves to provide the alternate steps for the setup.

Step Configurations that are no longer needed

The following are the changes to the steps in my previous blog post:

Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App and Logic Apps
https://terenceluk.blogspot.com/2022/07/create-automated-report-for-office-365.html

Step #1 - Creating a Service Principal for App-Only authentication for Microsoft Graph PowerShell SDK (Connect-MgGraph)
This step will no longer be required.

Step #3 – Create a Function App that will retrieve Office 365 / Microsoft 365 license usage with friendly names and return it in HTML format
As we will no longer be using certificate based authentication, we will not need to create the application settings appID and WEBSITE_LOAD_CERTIFICATES.

Step Configurations Additions and Changes

Step #2 – Create a Storage Account Container that will provide the CSV file containing the product friendly names
The reason why I opted to download the CSV file that Microsoft provides which contain the product friendly names is because the link may change over time but for this blog post, I will use the direct link to download.microsoft.com (https://download.microsoft.com/download/e/3/e/e3e9faf2-f28b-490a-9ada-c6089a1fc5b0/Product%20names%20and%20service%20plan%20identifiers%20for%20licensing.csv)

Step #3 – Create a Function App that will retrieve Office 365 / Microsoft 365 license usage with friendly names and return it in HTML format
Since we’ll be using Managed Identity for authentication, we’ll be adding the steps here.

The following are the steps for the new configuration.

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

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

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

Proceed to create the new Function App as described in my previous post:

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.

Step #4 – Create a System assigned managed identity for the Function App

With the Function App created, navigate to Settings > Identity and turn on the System assigned status to create a managed identity:

You should now see a Object (principal) ID created for the function app:

Make a note of the Object (principal) ID as well be using it to confirm an Enterprise application is created.

Next, navigate to Azure Active Directory > Enterprise applications, then search for the Object (principal) ID to look for the service principal created:

Step #5 – Use PowerShell to grant Graph API permissions to the System assigned managed identity for the Function App

Note the returned result of the service principal and the associated Application ID in the returned result. What normally happens now is when we navigate to the App Registration blade, locate the object with the Application ID then configure the API Permissions blade. However, given that this is a managed identity, you will not find the entry in the App Registration list and navigating to the Permissions blade of the Enterprise Application will not allow you to grant API permissions:

A quick search will return the following Microsoft blog post explaining the process:

Grant Graph API Permission to Managed Identity Object
https://techcommunity.microsoft.com/t5/integrations-on-azure-blog/grant-graph-api-permission-to-managed-identity-object/ba-p/2792127

As described in the blog post above, PowerShell is required to assign the permissions but while the instructions are fairly straight forward, the cmdlets did not work for me as of December 25, 2022 (the blog post was published on Sept 28, 2021). The problem appears to be due to line:

$AppRole = $GraphServicePrincipal.AppRoles | Where-Object {$_.Value -eq $PermissionName -and $_.AllowedMemberTypes -contains "Application"}

… because the Where-Object is not able to successfully filter the AppRole with the Value and AllowedMemberTypes parameters. This led me down the path to explore using Connect-MgGraph cmdlets that another user provided in the comments below the post but that did not work either because I was not able to use them to configure Managed Identities (they can configure other Enterprise Applications). Here are 2 additional useful sources I found while troubleshooting:

The complete list only shows applications configured in Azure AD as EnterpriseApplications, not Managed Identities:
https://github.com/Azure/azure-powershell/issues/18412

Using New-MgServicePrincipalAppRoleAssignment to configure permissions:
https://copyprogramming.com/howto/how-to-set-microsoft-graph-api-permissions-on-azure-managed-service-identity-with-powershell-7

So after trying numerous changes, what worked is to use both:

1. Get-AzureADServicePrincipal to retrieve the ObjectId of the Graph API
2. Get-AzADServicePrincipal to retrieve the App Roles of Graph API so we can assign them using the role’s ID

With the above challenges described, let’s proceed with how we can grant the following API permissions required for the Managed Identity:

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

If we were only granting one API permissions then the PowerShell script variables that are required to be defined are:

$TenantID="provide the tenant ID"

$GraphAppId = "00000003-0000-0000-c000-000000000000" <-- Note that this parameter is optional and the provided value here does not need to be changed because this corresponds to Graph API GUID.

$DisplayNameOfMSI="Provide the Function App name"

$PermissionName = "Directory.Read.All"

Since we have more than one permission to assign, we’ll be storing the required permissions in an array as such:

$TenantID="provide the tenant ID"

$GraphAppId = "00000003-0000-0000-c000-000000000000"

$DisplayNameOfMSI="Provide the Function App name"

$permissionsNames = @(

'Directory.Read.All'

'Directory.ReadWrite.All'

'Organization.Read.All'

'Organization.ReadWrite.All'

)

The script to configure the appropriate managed identity permissions can be found at my GitHub here: https://github.com/terenceluk/Azure/blob/main/PowerShell/Configure-Managed-Identity-API-Permissions.ps1

The following is a sample output once the permissions successfully granted via the PowerShell cmdlets:

The required permissions should now be listed in the Permissions blade of the Enterprise Application representing the Managed Identity:

Step #6 – Configure Function App to download required dependencies

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.

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

Navigate to the profile.ps1 and verify that the following lines are uncommented:

if ($env:MSI_SECRET) {

Disable-AzContextAutosave -Scope Process | Out-Null

Connect-AzAccount -Identity

}

Step #7 – Create a HTTP trigger Function that will generate the report and turn on Application Insights

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 from my GitHub repo into run.ps1:
https://github.com/terenceluk/Microsoft-365/blob/main/Administration/Get-M365-License-Report-Function-v2.ps1

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

<h2>Client: Contoso Limited</h2>

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:

Note that I have experienced an odd behavior where running the Test/Run feature displays a 503 Service Unavailable HTTP response code as shown in the screenshot below:

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

I won’t go into the details for the Logic App setup as it would be repeat from my previous post so please refer to it to set up the triggering of the Function App and send the report out via email.

Create an automated report for Office 365 / Microsoft 365 license usage with friendly names using Azure a Function App and Logic Apps
https://terenceluk.blogspot.com/2022/07/create-automated-report-for-office-365.html