Create Report Of Assigned Licenses For Users From Microsoft 365

Dipen Shah

3y19.6k20100Article

20 Comments

Join the conversation! Your thoughts help the community grow.

Tim2y ago
Can you upload the flow as a template here? I’m having trouble with a few things while creating it.
Martin Villard2y ago
Question : what is the Audience URL ? Where do you get its value ? I do not know where to find it. Thank you
Martin Villard2y ago
Hello Dipen, Thank you for this tutorial. AS I'm not an expert in Azure and in Power Automate, it's very useful to follow your tutorial.
Martin Villard2y ago
Hello Dipen
Brian Johnson3y ago
Where can I get the zip file?
Joe Doe4y ago
Hi Dipen thanks for your great work on this. It's possible to update the zip file in GitHub with all the steps? I'm struggling to make this work for a licenses report for Power BI, and it's the most complete solution I have find all over the web. Thanks!
- Brian Johnson3y ago
  Joe, can you provide the zip file? I looked on GitHub and could not find it. I'm struggling with two components of this; Parse JSON for Assigned Licenses and Step 10.
Lucas Edson5y ago
Is there a reason you've skipped explaining Step 10?
- Dipen Shah5y ago
  In Step 10, Once Add new Row in Excel execute successfully, than you need to reset 2 variables.1. Product name and SKUId
- Lucas Edson5y ago
  I guess my confusion is where are you getting the "Title" variable in "append to string variable" defined? I think there's some JSON parsing that's not fully defined.
Lucas Edson5y ago
I'm actually trying to build this, but there is a key info left out. - Step 9: How are you choosing the "assignedLicenses" as an option in the Parse JSON action?
- Dipen Shah5y ago
  Lucas Edson "Parse JSON for On Boarded Users" pass dynamic Content "body('Get_All_Users')" JSON will be as below. { "type": "object", "properties": { "@@odata.context": { "type": "string" }, "@@odata.nextLink": { "type": "string" }, "value": { "type": "array", "items": { "type": "object", "properties": { "@@odata.id": { "type": "string" }, "displayName": { "type": "string" }, "createdDateTime": { "type": "string" }, "accountEnabled": { "type": "boolean" }, "userPrincipalName": { "type": "string" }, "givenName": { "type": "string" }, "surname": { "type": "string" }, "externalUserState": {}, "assignedLicenses": { "type": "array", "items": { "type": "object", "properties": { "disabledPlans": { "type": "array" }, "skuId": { "type": "string" } }, "required": [ "disabledPlans", "skuId" ] } } }, "required": [ "@@odata.id", "displayName", "createdDateTime", "accountEnabled", "userPrincipalName", "givenName", "surname", "externalUserState", "assignedLicenses" ] } } } }
- Dipen Shah5y ago
  Https://github.com/dips365/SamplePowerAutomate/blob/main/Sample-Report-Generation_20210918064447.zip You can download sample automate with all correct JSON on above URL. Let me know if you need more assistance on this. We can connect by call.
- Dipen Shah5y ago
  Hey Lucas Edson, you need to initialize variable called "ProductName". when Filter Array connector called than you need apply for each on body of "Filter Array". You need to add all values from Filter Array in String variable called "ProductName" by Append connector. Updated Flow XMl file is uploded on URL "https://github.com/dips365/SamplePowerAutomate/blob/main/Sample-Report-Generation-V2.0_20210921033412.zip". Actually, Article and Power automate is not synced because I have created sample automate zip file for you from existing one after cutting lot's of business processes.

Introduction

Power automate is a tool that enables you to implement queries without knowing hardcore coding patterns. Microsoft Graph API is the universal endpoint for Microsoft 365 which give you the flexibility to access azure active directory resource through single endpoint using power automate.

I am writing this article to share how we can implement Graph API with Power automate to generate reports and this implementation would be useful to IT Admin who is responsible for AD resources and related reports.

Today, I am going to write steps to generate reports for users along with the list of licenses are assigned.

Definition

Generate an excel report on 1st day of each month for users, a list of assigned licenses to each user along with the metadata of users, and store excel report in the SharePoint document library.

Learning Objectives

Register Application and Grant Permission in Azure Active Directory
Create Scheduler in Power Automate
Access SharePoint list in Power Automate
Array Filter in Power Atomate
Connect to Graph API
Excel Online Connectors in Power Automate
Add row to Excel Worksheet.

Prerequisites

Microsoft 365 Account
License to access Power Automate
SharePoint Site with Site Collection Administrator
Create Excel Template including worksheets and tables and upload it to the SharePoint document library. The sample Template file is added to the attachment.

Let’s get started !!

Step 1 - Create a new SharePoint List to stores licenses Product Name and SKU IDs

When we get the user’s assigned licenses from Graph API then it returns SKU ID only. So, we can use this list to filter Product Names from SKU ID. Click here to get a list of licenses product names and SKU IDs.

Create a new list called “licenses” in SharePoint Site and add a column called GUID. Add Product Name and GUID from above Microsoft Documents.

Step 2 - Register Application and Azure Active Directory.

Application Creation

Open Azure portal
Click on azure active directory
Click on App registration
Click on New Registration
Enter the Name of the application
Select ‘Accounts in this organizational directory only ‘
Click on register
Copy Application ID and Tenant ID and put it aside, we will use it in power automate.

Generate Secret Key

Click on ‘Certificates & secrets’ in the newly created application.
Click on New client secret
Add description
Select Expires to 24 months.
Click on Add
Copy the value of the Secret key and put it aside, we will use it in power automate.

Grant API Permission

Click on API Permission
Click on ‘Add a permission’
Select Microsoft Graph
Select Application Permission
Find and Select ‘User.Read.All’
Click on Add Permission
Click on Grant Admin consent for <Tenant Name>

Step 3 - Create Power Automate from Recurrence trigger

Open power automates from App list in Microsoft 365.

Create new flow -> Select Recurrence -> Select month -> Create.

Create report of assigned licenses for users from Microsoft 365

Step 4 - Initialize variables in Power Automate

We need 7 variables that will store multiple values and use them to generate reports.

Paste Tenant ID, Client Secret and Client ID which we had from the azure active directory.

Step 5 - Get Licenses from SharePoint list in Power automate

We will use 'Get Items' and 'Append to array object' connectors in power to automate.

Step 6 - Create report file from excel template

We will get file content from a template file that is store in the SharePoint document library and create a new file that will be used to store users' details in it.

We will use “Get File Content” to get content from the existing excel file template and “Create File” to create a new file in the desire location in SharePoint.

Step 7 - Access Excel worksheet in Power Automate

We will get worksheets from excel sheet using Excel Online connector in Power automate.

Get all sheets from excel file using “get Worksheet: connector.

“Parse Json” connector is used to access response from getting Worksheet connector.

JSON Schema for Parse Json should be the same as below,

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "@@odata.id": {
                        "type": "string"
                    },
                    "id": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    },
                    "position": {
                        "type": "integer"
                    },
                    "visibility": {
                        "type": "string"
                    }
                },
                "required": [
                    "@@odata.id",
                    "id",
                    "name",
                    "position",
                    "visibility"
                ]
            }
        }
    }
}

Step 8 - Get Users using Graph API

“Http” Connector will be used to execute rest API in power automate.

End Point URL

https://graph.microsoft.com/beta/users?$filter=userType%20eq%20%27Member%27&$select=displayName,createdDateTime,accountEnabled,userPrincipalName,givenName,surname,externalUserState,assignedLicenses

Method: get
Authentication: Azure Active Directory
Tenant: Add Reference of TenantId variable
Client Id: Add Reference of Client ID Variable
Secret: Add Reference of Client Secret Variable
Audience: Add Reference of AudienceURL variable

Pass JSON schema in Parse Json Connector.

{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "@@odata.id": {
                        "type": "string"
                    },
                    "displayName": {
                        "type": "string"
                    },
                    "createdDateTime": {
                        "type": "string"
                    },
                    "accountEnabled": {
                        "type": [
                            "boolean",
                            "null"
                        ]
                    },
                    "userPrincipalName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "givenName": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "surname": {
                        "type": [
                            "string",
                            "null"
                        ]
                    },
                    "externalUserState": {
                        "type": [
                            "string",
                            "null"
                        ]
                    }
                },
                "required": [
                    "@@odata.id",
                    "displayName",
                    "createdDateTime",
                    "accountEnabled",
                    "userPrincipalName",
                    "givenName",
                    "surname",
                    "externalUserState"
                ]
            }
        }
    }
}

Step 9 - Get Assigned Licenses Product Name

We have an array of Product name list which we had created on starting of the automation.

Let’s Access the assigned Licenses object from the user’s object which is getting on the response of HTTP request.

Create report of assigned licenses for users from Microsoft 365

Let's filter the array if we found licenses are assigned to the user. We can get SKU ID from the response and we do filter from licenses array based on SKU IDs.

"Filter Array" action is used to filter items from an existing array in power automate.

Step 10

Add a new row in the table is created excel file.

Full Power automate will look like as below.

Create report of assigned licenses for users from Microsoft 365

The output will be as below.

Create report of assigned licenses for users from Microsoft 365

Conclusion

We learned how to connect the SharePoint site and list to Power automate, Create and append array, Call graph End point, Register application and Access and Add new Records in Excel worksheet.

Happy Learning.