As per Part 1 – The need here was to create a completely airgapped – i.e. no admin accounts for logging into reporting dashboards – reporting mechanisms for managers across a tenant shared between 20 or more business units. The reporting was primarily to monitor licenses and how they were assigned, what usage the business unit was getting from that and other metrics which are useful to report on.
Part 1 – Using Power Platform and Graph for Office 365 Reporting – Part 1 – Users
Part 2 – Using Power Platform and Graph for Office 365 Reporting – Part 2 – License Assignments
In Part One I used Flow to interogate Microsoft Graph and dump all of our users in a custom CDS Entity. Now we need to get all of the licenses assigned to those users and stick them in another entity.
I’m not going to go through every concurrency and timeout setting ion this post, my advice is the same as in Part 1.
Graph Query
The data we want here is what license each user has and how it is applied – i.e. is it via a group or direct.
To get this we need to run a select of license assignment states against each user: https://graph.microsoft.com/v1.0/users/<<UserID>>?$select=licenseAssignmentStates will return JSON of the form:
{
"@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users(licenseAssignmentStates)/$entity",
"licenseAssignmentStates": [
{
"skuId": "c7df2760-2c81-4ef7-b578-5b5392b571df",
"disabledPlans": [],
"assignedByGroup": null,
"state": "Active",
"error": "None"
}
]
}We just need to iterate through all of our users and grab this data using flow.
Data Entity
Exactly as per the User entity we can go to make.powerapps.com and hit Data | Entities | +New Entity. This entity is part of the M365 reporting mechanism and will hold License Assignments so I called it M365_Reporting_LicenseAssignments. I called the Primary Name field usersku as well just be sticking the user+sku data in here.
We need 3 custom fields in here to hold the assignment group that assigned the license, the license sku and the user objectID
Flow
The flow is going to read the users from the CDS entity we created and populated in Part 1, then for each of those users get their license assignments from Microsoft Graph and stick them in our new CDS entity.
First off we just create a new flow called M365_Reporting_LicenseAssignments scheduled to run every day. Again I’ll start with the overview then go into the detail:
After our recurrence setting the flow to run daily (i went with 1a.m. so it runs after the Users flow but leaves a few hours gap for a third flow I’ll be creating later…) we use a CDS list records task to get all of our users from the M365_Reporting_CurrentUsers entity:
We dont need any filters we just want everyone. By default this will return 5000 records. We need everything, in my case close to 50,000. To do this we need to use pagination. To set it hit the breadcrumbs and go to settings then turn on pagination and set the limit to 100000 items: Don’t forget to hit Done at the bottom of settings, it is usually off screen and I have on more than one occasion forgotten, set off my flow then found out I forgot after three hours.
Now we use a Control – Apply to Each loop which is going to do our work – we’re using the values from the user lookup above:
The first thing we do in our loop is find all of the licenses we have previously seen applied to the user then delete their records, there is no easy If EXISTS method so this seems the best way, its slow but this is a reporting function running overnight so that’s fine:
This is a list records Common Data Service task filtered on the object ID of the current user in our apply to each loop
crf11_m365_reporting_la_userobjectid eq @{items('Loop_through_Users')?['crf11_m365_reporting_currentusers_objectid']}We then have a second Apply to each using the value from this list and Delete each record, the item identifier is the unique ID from our list – crf11_m365_reporting_licenseassignmentsid
Now we have no license attached to that user so we can go and do our graph call to get the user’s licenses
The method here is the same as for Part One when I grabbed the users, I use a service principal with a cert based logon (which I will definitely get around to writing a post on very very soon!) and permissions to read the directory.
The URI we are interrogating is
https://graph.microsoft.com/v1.0/users/M365_Reporting_CurrentUsers_ObjectID?$select=licenseAssignmentStates
This will grab the licenses for the current user in the loop and return them in JSON. The temptation here is to assemble the URL in a variable and just feed the variable to the graph call to make is a little more clear to read (much as I used $GraphURL in the users piece) however this breaks my biggest rule of using loops in flow – Never, Ever update a variable inside a loop. Flow is pretty slow especially when you are doing multiple CDS calls and a graph query every loop so you need concurrency. I reccomened settign every loop to 50:
So you definitely dont want another iteration of a loop updating your variable before you’ve finished using it.
Backl to our flow, now we have our results as JSON in an html response we just need to parse them into an array:
My advice on schema building is still the same, the generate from sample used with the output from Graph Explorer is a good start but will again have issues where properties are null-able. In this case you might need to edit some types, the schema is below:
{
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"licenseAssignmentStates": {
"type": "array",
"items": {
"type": "object",
"properties": {
"skuId": {
"type": "string"
},
"assignedByGroup": {},
"state": {
"type": [
"string",
"null"
]
},
"error": {
"type": "string"
}
},
"required": [
"skuId",
"assignedByGroup",
"state",
"error"
]
}
}
}
}Now we have our array of licenses for this user we just loop through the array and create a record for every array item (we dont need any conditional logic as we just deleted the existing ones)
This is another For Each loop, this time we use the @{body(‘Parse_JSON’)?[‘licenseAssignmentStates’]} output of our parse_json task. From this we run a COmmond Data Service Add Record task to add a record to our M365_Reporting_LicenseAssignments entity.
| M365_Reporting_LicenseAssignments_UserSKU | @{items(‘Loop_through_Users’)?[‘crf11_m365_reporting_currentusers_objectid’]}-@{items(‘Loop_through_licenses’)?[‘skuId’]} |
| M365_Reporting_LA_AssignmentGroup | @{items(‘Loop_through_licenses’)?[‘assignedByGroup’]} |
| M365_Reporting_LA_SKUID | @{items(‘Loop_through_licenses’)?[‘skuId’]} |
| M365_Reporting_LA_UserObjectID | @{items(‘Loop_through_Users’)?[‘crf11_m365_reporting_currentusers_objectid’]} |
When the flow runs we’ll now have an entity listing User Object ID against the SKU ID for that user’s licenses along with whether they were group applied. We can expand this to include plan settings and dates but I didnt need that so didnt.
