How to deploy my (meaningful) Microsoft 365 Copilot usage report

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.