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.

How to determine the master image of a Machine Catalog in Citrix Virtual Apps and Desktops / Citrix DaaS

While not very frequent, some of my ex-colleagues ask me would ask me about Citrix Virtual Apps and Desktops from time to time and one of the most common question is how to determine the master image of a Machine Catalog in Citrix Virtual Apps and Desktops / DaaS because there still does not seem to be a way to find this information from the GUI. Those who are familiar with the Citrix portal will be aware of the Template Properties tab shown here:

… and while it displays what snapshot the master image virtual machine for this machine catalogue is currently using, it does not indicate the VM name. This appears to have been by-design since the Citrix XenDesktop 5.6 days as shown in one of my older posts here:

How do I find what master image I used for a desktop catalog in Citrix XenDesktop 5.6?
http://terenceluk.blogspot.com/2012/04/how-do-i-find-what-master-image-i-used.html

It was easy to load the PowerShell directly on a Delivery Controller back in the on-premise days but most of the environments I’ve worked in over the past few years has been in Citrix Cloud, which means you’ll need to install the Citrix SDK to remotely connect to Citrix cloud.

Given that I haven’t really written a post on this and I often struggle to remember, this serves as a short write up that I can refer to in the future.

The Virtual Apps and Desktops Remote PowerShell SDK can be downloaded here:

Virtual Apps and Desktops Remote PowerShell SDK
https://www.citrix.com/downloads/citrix-cloud/product-software/xenapp-and-xendesktop-service.html

Once installed, launce PowerShell and execute the following command to add the Citrix PowerShell snapins:

asnp citrix*.

Use the following cmdlet to authenticate against Citrix Cloud (a browser popup with a prompt similar to https://citrix.cloud.com will be displayed):

Get-XDAuthentication

After successfully authenticating, you can use the cmdlet Get-ProvScheme to list all of the Machine Catalogs for the tenant or narrow it down by using the ProvisioningSchemeName to reference the machine catalog you want the details for:

Get-ProvScheme -ProvisioningSchemeName “Machine catalog name”

The screen shot below is a sample output and the field we’re interested in is the MasterImageVM with the following output:

MasterImageVM: XDHyp:\HostingUnits\CC Nimble\CTX-CMComApp-CC.vm\CTX-CMComApp-CC Post Vendor Upgrade

07-11-19.snapshot\Post Vendor Upgrade 08-04-20.snapshot\Updated July 28

2021.snapshot\CTX-CMComApp-CC_vm-3432_1.snapshot\Vendor Update July 4 2022.snapshot

The value ending with .vm represents the virtual machine name and in this example the VM is named:

CTX-CMComApp-CC

If there are multiple vCenters in the environment, the vCenter hosting the VM can be found via the GUI by navigating into the configured resource:

Hope this helps anyone looking for this information.

Using Microsoft Graph PowerShell SDK to retrieve Office 365 / Microsoft 365 license usage with friendly names

Those who have been using the Msol online and Azure AD modules may be aware that Microsoft has announced that they will be deprecated after December 2022 this year, which means all the scripts that relies on these modules will need to be updated. This announcement meant one of the scripts that I have often used in the past to automate reports for Office 365 / Microsoft 365 licensing was no longer going to work in 5 months so I thought there would be no better time than now to modernize it. The two options available were:

1. Microsoft Graph PowerShell SDK
2. Microsoft Graph API

Given that what I had was already a PowerShell script, I decided to migrate it to option #1 – Microsoft Graph PowerShell SDK.

The task seemed pretty straight forward as the Get-MgSubscribedSku (https://docs.microsoft.com/en-us/powershell/module/microsoft.graph.identity.directorymanagement/get-mgsubscribedsku?view=graph-powershell-1.0) appeared to be exactly what I needed but I later realized that the output did not provided the amount of licenses that were purchased because it was stored in the PrepaidUnits property that displays:

1. TotalUnits
2. SuspendedUnits
3. WarningUnits

Furthermore, the provided SkuPartNumber name wasn’t the friendly name we would see on the portal so simply sending these fields to, say, sales or accounting would likely confuse them.

The script I ended up with at the end was to retrieve a table with the fields provided by Get-MgSubscribedSku, another table that contained the PrepaidUnits property, and finally an imported table from a CSV that contained a list of licenses and their friendly names that was provided by Microsoft here: https://docs.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference

The script can be found here at my GitHub repo: https://github.com/terenceluk/Microsoft-365/blob/main/Administration/Get-M365-License-Report.ps1

… and here are some sample outputs:

HTML

Excel