Using Power Automate to Get a List of Your MS 365 Subscriptions – Part 2

In Part 1 I showed how we can use Power Automate to get a list of all the MS 365 subscriptions that your organisation has acquired. We achieved this using the Microsoft Graph API and the following data properties: skuPartNumber, consumedUnits and prepaidUnits.

In Part 2 I’ll cover the following:

• how to select the Microsoft products to report on – we don’t need to report on everything
• how to change the product’s description text to a user friendly name of our choice (e.g. from FLOW_PER_USER to Power Automate per user plan)
• how to better format the HTML table in the email report
• how to schedule the flow to run on a weekly basis.

The demo flow assumes that you or your Azure administrator has already setup an App registration for Microsoft Graph API as described in Part 1.

The Unfriendly names

When we view products and licensing information in the Azure Portal or the Microsoft 365 admin centre we see friendly names like Microsoft 365 A3 for Faculty and Dynamics 365 Sales Enterprise Edition etc. When we ran the flow from Part 1 the same products were identified using a less friendly name (called the skuPartNumber) such as M365EDU_A3_FACULTY and DYN365_ENTERPRISE_SALES :

Microsoft publishes a full list of Product names and service plan identifiers for licensing as a downloadable CSV file. This file includes the skuPartNumber and product friendly names. We can use that file to find the product’s friendly name (column Product_Display_Name) by looking at the String_Id column (the Excel column String_Id is the same as the skuPartNumber).

“Interpreting the values used by Microsoft is an art into itself. The Product names and service plan licensing page is a valuable resource, but sometimes it’s a matter of guesswork based on your knowledge of the products and service plans in use.”

Tony Redmond

Create an ‘Unfriendly name’ to ‘Friendly name’ mappings table

To translate the product unfriendly names to friendly names we can create a “table” that maps the skuPartNumber to a friendly name of our choice.

We build the mappings table as a JSON array. The content and format of the JSON array looks like this:

[
  {
    "skuPartName": "Microsoft 365 A3 for Faculty",
    "skuPartNumber": "M365EDU_A3_FACULTY"
  },
  {
    "skuPartName": "Office 365 A1 for Faculty",
    "skuPartNumber": "STANDARDWOFFPACK_FACULTY"
  },
  {
    "skuPartName": "Project Plan 3 for Faculty",
    "skuPartNumber": "PROJECTPROFESSIONAL_FACULTY"
  },
  {
    "skuPartName": "Power Automate per user plan",
    "skuPartNumber": "FLOW_PER_USER"
  },
  {
    "skuPartName": "Microsoft 365 Audio Conferencing Pay-Per-Minute for Faculty",
    "skuPartNumber": "MCOMEETACPEA_FACULTY"
  },
  {
    "skuPartName": "Visio Online Plan 1 for Faculty",
    "skuPartNumber": "VISIOONLINE_PLAN1_FAC"
  },
  {
    "skuPartName": "Dynamics 365 Sales Enterprise Edition",
    "skuPartNumber": "DYN365_ENTERPRISE_SALES"
  },
  {
    "skuPartName": "Dynamics 365 Team Members",
    "skuPartNumber": "DYN365_TEAM_MEMBERS"
  }
]

Each product in the array table has an skuPartNumber property and a corresponding friendly name stored in the skuPartName property. This array contains the list of all the product licenses we want Power Automate to report on.

You will need to edit the table to suit your needs. To ensure that you haven’t made any formatting errors you should paste the content into a JSON validator, such as jsonFormatter.org and validate it. Correct any errors you find.

The flow

This is the entire flow. Note that the flow currently has a manual trigger. We will later replace this with a recurring schedule once we are satisfied that the flow works without issue:

Create an array variable called arrSkuNames

Create an array variable called arrSkuNames to store the content of the JSON array mappings table:

Now paste the JSON data in to the array variable arrSkuNames:

Remember that this array contains the list of all the product licenses we want Power Automate to report on, including the product’s friendly name.

Given the skuPartNumber of M365EDU_A3_FACULTY we can get the text (skuPartName) “Microsoft 365 A3 for Faculty”.

Similarly, given the skuPartNumber of VISIOONLINE_PLAN1_FAC we get skuPartName of “Visio Online Plan 1 for Faculty”.

The skuPartNumber is the key, and the skuPartName text returned to us is the value. This is also known as a key/value pair.

Create an array variable to store the licensing information

Create an array variable called arrLicenseInfo . We will fill this array with the license information obtained using the Graph API. We will later use this array to create a HTML table:

The HTTP action

Note the properties that are used for Authentication in the HTTP action:

Apply to each loop

Create an Apply to each loop using the arrSkuNames array (the “mapping table”) as its value. We will iterate or loop through each of the products listed in the arrSkuNames array:

Add a filter array action

The filter array action searches the data returned by the HTTP action for the product we want to report on:

These are the expressions used in the Filter array action:

body('HTTP')?['value']
item()?['skuPartNumber']
items('Apply_to_each')?['skuPartNumber']

Add Append to Array action

HTTP request returns a lot of information. We are interested in just the following properties: consumedUnits (the number of licenses used or assigned), the prepaidUnits (how many licenses the organization has purchased or the maximum quantity available) and the skuPartNumber. The skuPartNumber is used as they key to the mapping table in order to get our custom text for the product’s description text:

Here is the Append to array action in more detail:

These are the expressions used in the Append to array action:

body('Filter_array')[0]?['consumedUnits']
body('Filter_array')[0]?['prepaidUnits']?['enabled']
items('Apply_to_each')?['skuPartName']

Create HTML table action

Add a Create a HTML table action using the array variable arrLicenseInfo for its From value. The array variable arrLicenseInfo was filled with the product’s license information and the product’s friendly name.

HTML table formatting code

Add a compose action to store the HTML table formatting code that will make our email report look nice:

This is the HTML table formatting code that you can copy-and-paste into the Compose action:

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 100%;
  text-align: left;
  border-collapse: collapse;
  table-layout: auto;
}
table td, table th {
  border: 1px solid #AAAAAA;
  padding: 10px;
}
table tbody td {
  font-size: 13px;
}
table thead {
  background: #1C6EA4;
  border-bottom: 2px solid #444444;
}
table thead th {
  font-size: 15px;
  font-weight: bold;
  text-align: left;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
  border-left: none;
}
</style>

Send an email action

Finally we add the Send an email. Add the HTML formatting code and HTML table:

The Runtime Output

Now we can test the flow. Here is a sample runtime output:

This is the email report that was sent by the flow:

Schedule the flow

Finally schedule the flow by deleting the manual trigger and adding a recurring schedule of your choice:

An example schedule that runs every Monday at 9:00AM: