Hijacking Viva Insights to build a meaningful Microsoft 365 Copilot usage report

Continuing my series about constructing meaningful Microsoft 365 Copilot, this next piece covers Viva Insights.

A few things before we begin:

• Before reading further, make sure you read the previous piece comparing the four ways to construct your own Copilot usage reporting solution.
• After this, read the next piece about the method to track Copilot usage based on interactions (link to come when it’s written, and then this text will be removed — so if you’re reading this right now you should consider yourself lucky as future generations won’t see it… unless something happens to me, or I get distracted… ooooh, squirrel!!!).
• For the rest of this post, I will be referring to “Microsoft 365 Copilot” as just “Copilot” for simplicity.
• The method I am about to show you is not recommended or supported by Microsoft, me, or my former/current/future employer. I am simply showing you a potential path at getting some of that sweet, sweet Viva Insights data for Copilot.
• The method I am about to show you involves doing something I really don’t like, but (at this time) it’s the only way to achieve the outcome — so you’ll need to decide if it’s something you’re comfortable with.
  (You’ll know when you see it, and for those who know of my penchant for automating things — just imagine my face as if I’ve eaten the most bitter lemon. That’s how I feel about it.)

Now that we’ve gotten that out of the way…

The Background

Viva Insights has been with us for many years. How many years I (don’t) hear you ask?

It was first introduced as “Delve Analytics” in 2016, then renamed to “MyAnalytics” later in 2016, and then to Viva Insights in 2021.

In that time, we’ve also had something called “Workplace Analytics” from 2017. Where MyAnalytics focused on an individual’s view of their productivity, Workplace Analytics provided the organisation with higher-level views, made available through Power BI reports.

Workplace Analytics was also renamed to be Viva Insights. Not Viva Insights Pro or Premium, just “Viva Insights”.

This is where you have to be careful about the text when you see a feature being made available to Viva Insights licensed users. This refers to those who have a Viva Insights license as either a standalone purchase, or part of the Viva Suite. Not users who have a Viva Insights license as part of their Office 365 / Microsoft 365 license.

So, what does this have to do with the Copilot Dashboard available to Microsoft 365 organisations? Because Viva Insights is what powers that dashboard.

The licensing and requirements have changed for this a few times, so I won’t jump into that river. BUT… the functionality I am referring to in this piece is specifically for the Power BI dashboard functionality of Viva Insights. Historically you have needed a paid Viva Insights license to access this, but as of reading this you might not specifically for the Copilot reports. We just can’t know for sure these days.

To know if this functionality is right for you, please consult with your Microsoft 365 licensing specialist. And then maybe 1–2 others just in case someone isn’t current on what the situation is today.

The Challenge

As I mentioned in the previous post, the user identification data in Viva Insights is actually obfuscated from the organisations due to privacy.

Now, in Viva Insights organisational data comes from two potential sources: Entra ID, or data upload. Unfortunately, it cannot be a combination of both; it’s only one or the other.

If you rely on the Entra ID method, then you are stuck with the built in fields. Where this can set you on the wrong path, is that when looking at the Power BI template in Viva Insights there is a field called “PersonId” which contains a GUID.

“Huzzah” I hear you proclaim! A user-identifiable bit of data I can use to correlate the data to actual users. Don’t break out the champaign yet.

While you might see this reference in this document:

Unfortunately, that PersonId field is of no use to you because of this document, specifically this line: “The Person identifier is a cryptographically generated identifier derived from the person’s Microsoft 365 email address.”

The Solution

While the document around privacy I referred to earlier also says it may be possible to identify a person using several attributes together, I’m going to show you how to hijack the data and put a laser straight onto the target.

Before we can achieve this, we have to go through some pain.

As I mentioned earlier, there are two ways to get organisational data (i.e. information about users) into Viva Insights. The first being via Entra ID, which is the default, and how many organisations would still have it set. The second is via data upload.

Specifically, via CSV file.

Yes, you read that right. CSV file. Containing organisational data. Uploaded into Viva Insights.

I’ll wait while you go scream into a pillow.

Now that you’re back, you might read further or be told by someone that there are ways to do this via API or via Azure and that it can be automated. And you’d be right… kinda.

You still need to provide a CSV in both of those methods.

If you think the payoff is worth it (and I hope it is), then here’s what you need to do:

1. Add a column to the CSV with the Entra ID object ID of the users you are uploading to Viva Insights.
2. That’s it.

Yep, it’s that “simple”. Here’s an example I prepared earlier:

Once you’ve got this upload going with the identifiable data, you’re now ready to do something with it!

Using one of the existing Microsoft 365 Copilot report templates in Viva Insights (such as the adoption report), going into the back end you should now see your additional column showing up in the Master table:

The only challenge is that these user IDs have nothing to be matched with in order to show you names, department, manager, country, or any other attribute.

For that, you need to add another source of data into your Power BI report which pulls that data from Entra ID. It can either be achieved by having a table somewhere with that extracted information (which you will have to keep in sync), or directly in Power BI using a query like this one:

Below you’ll find the text for direct query, for which you’ll need to create parameters in Power BI containing your:

• Tenant ID
• Client/application ID from an app registration with Graph permissions to read your users
• Secret value (which you probably shouldn’t store in there)

I also have the Graph query in there which provides the fields for my filters/slicers, but also only returns users who are licensed for Microsoft 365 Copilot:

v1.0/users?$select=id,userPrincipalName,displayName,department,country,assignedLicenses&$filter=assignedLicenses/any(u:u/skuId%20eq%20639dec6b-bb19-468b-871c-c5c441c4b0cb)&$expand=manager($select=id,displayName)

let

   ResourceAppIdUrl = "https://graph.microsoft.com",
   OAuthUrl = "https://login.microsoftonline.com/",

   Resource = Text.Combine({"resource", Uri.EscapeDataString(ResourceAppIdUrl)}, "="),
   ClientId = Text.Combine({"client_id",ClientID}, "="),
   ClientSecret = Text.Combine({"client_secret", Uri.EscapeDataString(SecretValue)}, "="),
   GrantType = Text.Combine({"grant_type", "client_credentials"}, "="),
   Body = Text.Combine({Resource, ClientId, ClientSecret, GrantType}, "&"),


   AuthResponse= Json.Document(Web.Contents(
       OAuthUrl,
       [
           RelativePath= Text.Combine({TenantID,"/oauth2/token"}),
           Content=Text.ToBinary(Body)
       ]
   )),

   AccessToken= AuthResponse[access_token],
   Bearer = Text.Combine({"Bearer", AccessToken}, " "),

   Response = Json.Document(Web.Contents(
       ResourceAppIdUrl,
       [
           RelativePath = GraphURI,
           Headers = [#"Content-Type"="application/json", #"Authorization"=Bearer]
       ]
   )),
    value = Response[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "userPrincipalName", "displayName", "department", "country", "manager"}, {"Column1.id", "Column1.userPrincipalName", "Column1.displayName", "Column1.department", "Column1.country", "Column1.manager"}),
    #"Expanded Column1.manager" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.manager", {"id", "displayName"}, {"Column1.manager.id", "Column1.manager.displayName"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.manager",{{"Column1.id", "UserID"}, {"Column1.userPrincipalName", "UPN"}, {"Column1.displayName", "Name"}, {"Column1.department", "Department"}, {"Column1.country", "Country"}, {"Column1.manager.id", "Manager ID"}, {"Column1.manager.displayName", "Manager Name"}})
in
    #"Renamed Columns"

Using the power of relationships between that additional table in your Power BI file to match between the object ID returned in the “Licensed users” table, and the now-visible column of EntraUserID we uploaded in the CSV file.

What we end up with is user-identifiable data in our report:

(No comments on my Power BI skills please. Firstly it’s not my forte, secondly these visuals are more to prove the point of the data existing — they are not the final product.)

Conclusion

My hope is that you can use this knowledge to create more meaningful reports on Microsoft 365 Copilot usage and adoption in your organisation.

However, there are some trade-offs that you need to consider which some organisations may not be comfortable with:

• Identifying user data not just in the Copilot report, but all Viva Insights reports
• Switching from the Entra ID organisational data method, to one that involves using a CSV file which you are now responsible for populating, keeping current, and uploading

Originally published at Loryan Strant, Microsoft 365 MVP.