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

Automating the creation of a new user in Duo with Azure Automation Account and Admin API

I was recently asked if I had written any scripts for provisioning Duo accounts with the Admin API and realized that I had but never wrote a blog post so I decided to write this post to demonstrate the following:

1. Edit Matt Egan’s Duo PowerShell module (https://github.com/mbegan/Duo-PSModule) to include a duoSendSMSActivation function that is a copy of duoCreateActivationCode with the URL /activation_url changed to /send_sms_activation so it can be used to send an SMS activation to a user account’s phone
2. Create an Automation Account that will accept the following inputs in JSON format via a webhook:
   1. samAccountName
   2. email
   3. fullname
   4. mobile
3. Upload modified Duo module into Automation Account
4. Create a Protected Application in Duo and add authentication information as Automation Account encrypted variables
5. Create a runbook to host a PowerShell script that will take the input from the webhook to:
   1. Create the user account in Duo
   2. If a mobile number is passed, create a new phone
   3. If a mobile number is passed, associate new phone to the new Duo user account
   4. If a mobile number is passed, send a SMS txt message to phone number for Duo activation
6. Create a webhook for the Automation Account
7. Test Automation Account with PowerShell

Note that I won’t go into as much detail for some components but feel free to have a look at one of my previous posts where I set up an Automation Account to generate and send out a report for Duo accounts:

Using an Azure Automation Account Runbook to create and email a Duo report with SendGrid

http://terenceluk.blogspot.com/2022/09/using-azure-automation-account-runbook.html

Modifying Matt Egan’s Duo PowerShell module to include a function that will send an SMS activation

Matt Egan’s Duo PowerShell module does not have a function that allows us to send an SMS activation message so the easiest way provide this functionality is to open the Duo.psm1 file, duplicate the duoCreateActivationCode function, then edit the URL /activation_url and change it to /send_sms_activation as shown in the following screenshot:

The modified module can be found here at my GitHub repo: https://github.com/terenceluk/Azure/tree/main/Automation%20Runbook/Duo

Download the two files and create a Duo.zip package that we will be uploading later.

Create an Automation Account that will accept the following inputs in JSON format via a webhook

Create an Automation Account that we’ll be using to host the Runbook that will help us provision the new Duo user account:

Upload modified Duo module into Automation Account

Rather than attempting to write the PowerShell code required to authenticate with the Duo Admin API (https://duo.com/docs/adminapi) with a HMAC signature, then call the API methods, we’ll be using Matt Egan’s PowerShell module he has shared with the community years ago that still works today https://github.com/mbegan/Duo-PSModule

The Duo PowerShell module Matt Egan provided does not simply upload into Azure Automation’s Modules blade as the psd1 file references to the Duo_org.ps1 file that is mean to store the information required to connect to the Duo API.

Neil Sabol has a great write up that explains this and how to workaround the issue so I’ll be using his method to demonstrate the configuration: https://blog.neilsabol.site/post/importing-duo-psmodule-mfa-powershell-module-azure-automation/

The method I’ll be using is not to upload a blank Duo_org.ps1 file but rather comment all references to it in the Duo.psd1 file. You can find the updated file here in my GitHub: https://github.com/terenceluk/Azure/blob/main/Automation%20Runbook/Duo/Duo.psd1

Proceed to import the Duo.zip package we created earlier into the Automation Account Modules:

Select the package, leave the name configured as Duo, select 5.1 as the Runtime version then click Import:

Confirm the module has successfully imported:

One of the ways to check and see if the module imported properly is by clicking into the module and verify that the available cmdlets are displayed:

Create a Protected Application in Duo and add authentication information as Automation Account encrypted variables

Using the Duo Admin API requires authentication so we’ll need to create a protected application in the Duo Admin portal as described in the document here: https://duo.com/docs/adminapi

Copy the Integration key, Secret key, and API hostname as we’ll need them to create the encrypted variables in the following steps, and grant the application the required permissions:

Proceed to the Automation Account, navigate to create the following variables:

1. MyDuoDirectoryID
2. MyDuoIntegrationKey
3. MyDuoSecretKey
4. MyDuoAPIHostname

**Note that the Duo Directory ID can be located by navigating to: Users > Directory Syncs, select the configured directory then copy the key under the heading Admin API directory key:

Create a runbook to host a PowerShell script that will take the input from the webhook to provision the new user account

With all the components configured, create the runbook and put the code in that will provision the Duo user account. From within the Automation Account, navigate to the Runbooks blade and click on Create a runbook:

Fill in the required fields:

The following PowerShell Runbook will be displayed where we can paste the PowerShell script to be executed:

The script I will be using to generate and email the report can be found here: https://github.com/terenceluk/Azure/blob/main/Automation%20Runbook/Create-New-Duo-User.ps1

Proceed to publish the Runbook:

Create a webhook for the Automation Account

Proceed to create a webhook for the Automation Account by clicking on the Add webhook button in the runbook:

Click on Create new webhook:

Fill in a name for the webhook, enable it, set an expiry date for the webhook, and then copy the URL. Note that the URL will not get displayed again so if you forget or misplace the URL then you’ll need to create a new one.

I noticed that the Create button would remain greyed out until I clicked into the Configure parameters and run settings options even if I wasn’t going to change any of the configuration:

Click OK to exit the parameters and run settings:

Proceed to create the webhook:

Test Automation Account with PowerShell

Rather than using the test feature in the runbook, I prefer to use the following PowerShell cmdlets to test the webhook:

https://github.com/terenceluk/Azure/blob/main/Automation%20Runbook/Test-Web-Hook.ps1

$uri = 'https://d36f1e53-eabe-4b85-82d1-4710b90d5b52.webhook.eus.azure-automation.net/webhooks?token=x1WEKX%2f%2bL%2f%2fz2pX%2fBcJx3UqNii7GTU3T8lxAVIhA0PU%3d'

$headerMessage = @{ message = "Testing Webhook"}

$data = @(

@{ samAccountName="jsmith"},

@{ email = "jsmith@contoso.com"},

@{ fullname = "John Smith"},

@{ mobile = "+14165553445"}

)

$body = ConvertTo-Json -InputObject $data

$response = Invoke-Webrequest -method Post -uri $uri -header $headerMessage -Body $body -UseBasicParsing

$response

The following is a status code of 202 accepted from the test:

You should be able to find the newly created account in the Duo Admin console.

Installing and importing SharePoint Online Management Shell to remove a Deleted SharePoint site

I typically do not work within the SharePoint Online space but would periodically be asked to assist with administrative tasks and one of the issues I constantly face is not finding the right cmdlets to perform what I need to do but rather installing and importing the SharePoint Online Management Shell. My colleagues seem to encounter this as well so I thought I’d write a short blog post to outline the steps to get the module installed and demonstrate how to delete a deleted SharePoint site as an example.

Problem - Installing and Importing the SharePoint Online Management Shell

The official Microsoft document demonstrates the process of installing the module here:

Get started with SharePoint Online Management Shell
https://learn.microsoft.com/en-us/powershell/sharepoint/sharepoint-online/connect-sharepoint-online

The article provides the following cmdlets:

Get-Module -Name Microsoft.Online.SharePoint.PowerShell -ListAvailable | Select Name,Version

Install-Module -Name Microsoft.Online.SharePoint.PowerShell

What this article does not provide is the cmdlet to import the module after installing it as it will then provide the following cmdlet to connect to SharePoint Online:

Connect-SPOService -Url https://contoso-admin.sharepoint.com -Credential admin@contoso.com

Attempting to immediately connect will throw the following error:

Connect-SPOService: The term 'Connect-SPOService' is not recognized as a name of a cmdlet, function, script file, or executable program.

Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Attempting to import the module with the following cmdlet will yield no output in the PowerShell console:

Import-Module Microsoft.Online.Sharepoint.Powershell –DisableNameChecking

Then trying to use the Connect-SPOService again will display the following error:

Connect-SPOService: The remote server returned an error: (400) Bad Request.

Solution

Before attempting to connect to SharePoint Online, the following cmdlet needs to be executed import the module:

Import-Module Microsoft.Online.Sharepoint.Powershell -UseWindowsPowerShell

Once authenticated and connected, you should now be able to execute cmdlets such as the following to remove a deleted SharePoint site that has its associated Microsoft 365 group removed:

Remove-SPODeletedSite -Identity https://contoso.sharepoint.com/sites/ContosoLtd

The site can’t be permanently deleted because it’s connected to a Microsoft 365 group

Hope this helps anyone who might be looking for a quick answer to install, import, and connect to SharePoint Online via PowerShell.