Unlike other finales in movie franchises that turn out to only be a part 1 with the part 2 being another 1-2 years away, this part 2 of my finale follows hot on the heels of the part 1.
Not following what I’m referring to? Then read part 1 before continuing.
.
.
.
.
.
Now that you’re all caught up…
Solution Overview
The “M365 Copilot Usage Reporter” solution I’ve created is made up of several components:
- Entra ID app registration to interact with Microsoft Graph
- Power Automate workflows to capture Copilot interactions for licensed users
- Dataverse tables to store the data
- Power BI report to display the data
Let’s break these down individually.
Entra ID app registration
We need to create an app registration with the following application-level permissions:
- AiEnterpriseInteraction.Read.All – this will do the actual reading of the Copilot interactions
- Directory.Read.All – this is used to read users and various properties about them, licenses, etc. (It might be possible to use a lower-level API permission, but I didn’t try.)
You’ll need to create a secret as well. Store this, the client/application ID, and your tenant ID for the next step.
Workflows & tables
Head over to https://make.powerautomate.com and import the Power Platform solution file you downloaded from my GitHub repository.
NOTE: The account that will run the workflows will need the Power Automate Premium license, due to the usage of Dataverse tables.
After the confirmation screen, it should then automatically create a Dataverse connection for you (if one doesn’t already exist).
After this point, you’ll be prompted to enter your tenant ID, application ID, and secret value.
Once you’ve put those in and continued, the import will begin and should only take a few minutes.
While we wait for it to install, why don’t we find out…
What’s in the box?
A good question. What’s in this solution, and how does it work?
Inside, you’ll find a mixture of tables, connections references, and workflows
First, let’s address the 3 tables:
- M365 Copilot Currently Licensed Users: this table stores (you guessed it) all the users in your environment that are licensed for M365 Copilot. The contents of the table are wiped and re-created every day, as the listing will change.
- M365 Copilot Interactions v2: this table stores (you guessed it) all of the actual Copilot interactions by users. The table is not wiped and will continue to grow every day as more records are added.
Why is it called “v2”? Because there was a previous version of it in this solution, I needed a new table, called it “v2”, and deleted the old one. Are you happy that I’ve aired my dirty laundry now??? - M365 Copilot Reporter – Entra ID Users: this table stores (you (hopefully) guessed it) all of the licensed users in your tenant. This is used to provide a comparison of who is and isn’t licensed for M365 Copilot, as well as additional user details about those who are.
Now, let’s focus on the 5 workflows, as that’s where all the work…. flows.
- M365 Copilot Reporter – (DAILY) Coordinator: this workflow runs daily at UTC midnight and calls the two child workflows:
- M365 Copilot Reporter – (CHILD) Licensed Users Retriever: this workflow empties out the table with the M365 Copilot licensed users and retrieves a fresh listing.
- M365 Copilot Reporter – (CHILD) Interaction Retriever: this performs the API call against Microsoft Graph to retrieve the Copilot interactions.
This workflow doesn’t just get the interactions, it is removes some of the fields from being recorded for two reasons:- Results in Copilot responses are not recorded due to privacy reasons
- Some of the outputs are images, which result in blocks of text that are too big to store in a table row
- M365 Copilot Reporter – (WEEKLY) Entra ID users: this workflow performs a weekly extraction of all licensed users in your Microsoft 365 tenant. Unfortunately this means it may pick up things like service accounts, Teams meeting rooms, and other non-human accounts. The workflow runs once a week and is independent of the others.
- M365 Copilot Reporter – (MANUAL) Historical Importer: this workflow only exists to be run after you’ve performed the installation, and the Coordinator workflow has completed its first run.
Because the daily workflow only collects interactions for the previous day, (I hope) you will want to get any other Copilot interactions that have been performed before you’ve installed this solution.
The Historical Importer uses the Interaction Retriever child workflow in the same way the Coordinator does, but it provides the API call with a different date range to work from: everything between 365 days and the previous day. This effectively avoids any potential overlap between the two workflows being run.
I would strongly suggest you run it straight away and then delete it as it no longer serves a purpose.
NOTE: The Historical Importer will take longer to run than the Coordinator does, due to the volume of data it needs to retrieve. In a small deployment (88 users) this did not cause any issues, however in a larger deployment (950 users) I did see some child workflows running long and eventually timing out – so please keep an eye on this.
Even without the Historical Importer workflow completing, you’re now ready to move onto the Power BI report installation.
Power BI report
Locating the Power BI template file you downloaded from my GitHub repository, simply open the file to start connecting to the Dataverse tables.
You’ll first be prompted for the host address of the Power Platform environment where the solution was imported to.
The easiest way to achieve this is to browse to https://make.powerapps.com (this doesn’t work from Power Automate, only from the Power Apps portal) and select the environment where you installed the solution. In the top-right corner, click the gear icon and select session details:
You’ll then see a bunch of GUIDs and other things, but this is what we’re after:
Take note that we don’t want any of the characters before or after the host address, just the “orgxxxxx.crmx.dynamics.com” part that I’ve highlighted in the screenshot.
Paste that into the prompt in Power BI and press the Load button. You’ll then be asked to sign in, so that the connection to the environment can be made.
Follow the prompts and then press the Connect button.
This next step will take a bit of time while it retrieves all the data in the table (longer if you’ve run the Historical Importer).
When all is done, your data will be there!
From here, all you need to do is publish the report somewhere and make it available to those who need it!
Conclusion
It is important to remember that the endpoint we’re using to pull the data here is in beta, and therefore subject to change. If I see something change significantly, I’ll try to update the solution.
Also, throughout the 6 weeks that I’ve been working on this solution I have seen changes to the data around the apps that show up as “M365App”, “BizChat”, and “WebChat”. It’s not entirely clear what they’re supposed to be / how they differ, and while some assumptions are made, I will most likely post an updated version of the template when it’s clearer.
If you have suggestions on how to improve the Power BI report such as other visuals, scores, or other things, please don’t just leave a comment – share your modifications so everyone can benefit!
Also published on Medium.
Discover more from Loryan Strant, Microsoft 365 MVP
Subscribe to get the latest posts sent to your email.
Hi thanks for the great work, I have an error with this: flow M365 Copilot Reporter – (WEEKLY) Entra ID Users “Flow client error returned with status code “BadRequest” and details “{“error”:{“code”:”InvalidOpenApiFlow”,”message”:”Flow save failed with code ‘InvalidPaginationPolicy’ and message ‘The pagination policy of workflow run action ‘HTTP_-_Get_items_from_API’ of type ‘Http’ at line ‘1’ and column ‘2109’ is not valid. The value specified for property ‘minimumItemsCount’ exceeds the maximum allowed. Actual: ‘100000’. Maximum: ‘5000’.’.”}}”. ” Can you help me on this ? Thanks
Ah, whoops!
The issue is because I had set the pagination of the get user HTTP action to be 100k, but that only works if you have a Power Automate premium license (which you need for Dataverse access anyway).
If you lower it to 5000 in the settings of that action, it’ll work. But, not sure if any other part of it will if you don’t have the license.
May I know what this means? I got this after import solution. Btw great work on this. Very useful
Solution “Microsoft 365 Copilot Usage Reporting” imported successfully with warnings: Flow client error returned with status code “Forbidden” and details “{“error”:{“code”:”MissingAdequateQuotaPolicy”,”message”:”Flow could not be activated because you need a [Power Automate Premium license](https://go.microsoft.com/fwlink/?linkid=2297915) or other license that includes premium connectors to save this flow with connection: ‘Microsoft Dataverse'”,”extendedData”:{}}}”.
Ah yes, sorry, I forgot to add that as a requirement.
Because the workflow uses Dataverse tables it needs to run under an account with a Power Automate Premium license. Will update the blog post now.
Thank you so much! Loading the PowerApps solution in PowerBi it shows me blank report. How usually will take effect to see the report? Is there anything I need to run in PowerApps side?
Have you checked if the workflows inside the solution worked successfully?
Thanks, Loryan! The report is now working. By the way, I have a few clarifications. On the Home tab, there’s “Number of users using the app” and another tab named as “Usage by app.” What’s the difference between the two? I noticed the numbers don’t match.
Hey Jodie, that’s fantastic news, glad you got it working!
The difference between them is that the front screen shows the total number of users that use each app, vs. the “Usage by app” shows the total amount of interactions per app.
What would probably make a better interface, would be to show both values on the graph so it shows the number of users inside the bar for the number of interactions.
The other thing I’d thought of, but hadn’t done was to create things like averages per app, or other metrics. At the end of the day, there’s only so much BI I can do. 🙂
Hi Loryan, thank you so much for your great work, it has been really helpful so far. If you don’t mind, could you clarify the difference between “Usage by User” and “Interaction Count by User (Last 30 days)”? Some of the numbers don’t seem to match, and I wanted to clarify this before making any assumptions.
Thanks again in advance!
Glad to hear it Con!
Do you mean the “Interaction Count by Category” screen? That’s not per user, and is more so just grouping the data so you can see a broader view of how many interactions all users are doing.
So, what is the program exactly counting as an interaction? Just opening the app? editing a file? making a prompt?
Thanks for your fast reply Loryan
If you have a look at the prior blog post I have a link to the data source where the interactions come from.
Now, that data provides both prompt and response – I only count the prompt data.
Further to that, I filter it down to be individual sessions.
So, where you might have a long flowing conversation with Copilot in Word with 20 prompts and responses being a total of 40 interactions – I count that as 1, as it’s a single session.
Otherwise if I counted every prompt and response as an interaction, the data would be wildly different and I don’t think a very accurate representation.
Great & very useful solution, thanks for sharing. When I import I get this message, what does it mean & what are next steps?
Solution “Microsoft 365 Copilot Usage Reporting” imported successfully with warnings: Flow client error returned with status code “BadRequest” and details “{“error”:{“code”:”ChildFlowNeverPublished”,”message”:”The workflow with id ‘xxxxx’ cannot be used as a child workflow because it has never been published. Child workflows need to be published at least once before they can be included in a published parent workflow.”}}”.
..submitting the message again:
Solution “Microsoft 365 Copilot Usage Reporting” imported successfully with warnings: Flow client error returned with status code “BadRequest” and details “{“error”:{“code”:”ChildFlowNeverPublished”,”message”:”The workflow with id ‘0cd572be-53ce-ef11-8ee9-000d3a79c13a’ cannot be used as a child workflow because it has never been published. Child workflows need to be published at least once before they can be included in a published parent workflow.”}}”.
Check each of the workflows with “CHILD” in the name.
If you go into the workflow it’s probably showing as draft.
Hit Publish and it should be fine.
thanks Loryan, that worked. On installing/importing the solution, all flows were in ‘off’ state & simply turning them ‘On’, then publishing the solution was fine. I’m in an educational organization and realized wasn’t getting any licensed users even though we have ~40. I found that the SkuId should match what your org has purchased. We have the “Microsoft_365_Copilot_EDU” and not “Microsoft_365_Copilot” & these SkuId’s are listed on https://learn.microsoft.com/en-us/entra/identity/users/licensing-service-plan-reference.
Replacing the correct SkuId in the HTTP action of the “M365 Copilot Reporter – (CHILD) Licensed Users Retriever” flow will get you all the users.
Good pickup on that one. I hadn’t tested it on an EDU tenant so wasn’t aware it was a different SKU ID.
Thanks!
Hi Loryan, thank you for this useful tool. Unfortunately I get this error when I try to import it. Can you offer any suggestions as to why? I’ve already ensured that the app permissions have been granted, and I’m importing this as a user who has a premium power automate licence and therefore dataverse access.
Solution “Microsoft 365 Copilot Usage Reporting” failed to import: SecLib::CheckPrivilege failed. User: 380e7439-7e2b-ee11-bdf4-000d3ad0f9a3, PrivilegeName: prvCreateEntity, PrivilegeId: 341e3ebf-74b8-4335-84f3-7f617bb7d081, Required Depth: Basic, BusinessUnitId: 60482048-7b42-e911-a97e-000d3ad1caf6, MetadataCache Privileges Count: 5664, User Privileges Count: 1626
thanks!
I also tried importing your copilot trial management solution and it worked, so it isn’t that I don’t have permission to properly import solutions.
Hey Stephen, thank you for trying to troubleshoot this by importing another solution as a test – that helped as you’ve ruled out one of the permissions.
And you’ve got the right license – so that rules out another issue.
However, it does still look like it’s a permission/roles issue. As the Copilot trial management solution uses Lists, it wouldn’t be doing anything with Dataverse – but the fact that we’re seeing “prvCreateEntity” in the error tells me it’s something to do with Dataverse tables, and possibly not having the ability to create them. Can please you double-check that you have the role applied to your account that enables you to create tables?