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 the MS 365 subscriptions that your organization 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 admin 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).

Product names and service plan identifiers for licensing CSV file sample.
Product names and service plan identifiers for licensing CSV file. For example, the product display name for String_Id (skuPartNumber) MCOMEETADV is “Microsoft 365 Audio Conferencing”.

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.

 JSON validator website.
Use a JSON validator to make sure you haven’t made any formatting errors. Click the Validate button to validate your JSON array.

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:

The entire flow

Create an array variable called arrSkuNames

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

Create an array variable called arrSkuNames

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

We build the mappings table as a JSON array.

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:

Initialize an array variable to store the report table

The HTTP action

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

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:

Apply to each loop and value used in the loop.

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:

The filter array action.

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:

Append to array variable action.

Here is the Append to array action in more detail:

Append to array variable action - close up.

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.

Create a HTML table action

HTML table formatting code

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

HTML table formatting code

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:

Send an email action with HTML formatting code and HTML table.

The Runtime Output

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

Sample successful runtime output

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

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

Scheduling the flow by deleting the manual trigger and adding a recurring schedule of your choice.
Watch the how to delete a manual trigger and then schedule your flow to run at a specific time.

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Built with WordPress.com.

Up ↑

%d bloggers like this: