In Part 1, we covered options for pulling usage reports without interacting with an API. These are helpful, but they still face these limitations:

1. You don't get access to all the activity data.
2. They don't give interaction level detail.
3. There is no long-term data retention.

In this article, we will cover more advanced techniques for pulling and storing Power BI user activity.

Who this article is for

This article is intended primarily for Power BI administrators who need to see all Power BI usage. Knowledge of scripting or basic API interaction will be helpful. Examples will be provided in PowerShell, but any programming language can be used.

How this article is organized

We will start with the most generic tool to find Power BI - and all Microsoft 365 - logs. If you aren't familiar with it, it will be a good tool in your toolbox. We then move into a more specific tool for Power BI, the Power BI Activity Log. Finally, we will discuss the easiest way by far to collect and analyze all Power BI usage data - and retain it!

Introducing the Unified Audit Log (UAL)

For as long as Microsoft has offered Office as a cloud service, they have been logging events that happen in those tools. Around 2015, Microsoft brought all of these logs together into the "Unified Audit Log" to create a single place where all activities could be recorded.

Parsing the Microsoft branding

These days, "Office 365" is called "Microsoft 365" and the Unified Audit Log has been grouped under the "Microsoft Purview" umbrella, as a data governance, protection, and compliance solution.

Power BI is grouped into the Microsoft 365 umbrella and so participates in the UAL as well.

Search the UAL through a UI

If you have the right role in your organization, you can search these logs through web-based UI by visiting: https://compliance.microsoft.com/auditlogsearch.

Once you are at this screen, you can go looking for Power BI logs by narrowing down on any field, but I would recommend either the "Record types" or "Workloads" as an easy filter.

This will launch an asynchronous search (hello Synapse!)

When done, the job status will change to "Completed".

And the results are viewable in this format. Exporting also takes a moment, but will result in a CSV format. Note that these audit logs are all contained in JSON objects, so even after you have the data downloaded in a file, you've still got quite a bit formatting to do. Still, for a one-off search, the UI can be useful.

Search the UAL through script

Microsoft, and many others, have also developed helpful PowerShell cmdlets that can perform this search through code. A key cmdlet is Search-UnifiedAuditLog.

It's a good idea to check through a few settings in your Exchange Online settings before you start firing off searches, and you can read through those here.

Here's an example script usage that would to the same as our search above:

$auditlog = Search-UnifiedAuditLog -StartDate 09/15/2023 -EndDate 09/16/2023 -RecordType PowerBIAudit

And this script would write the results of that call into a CSV.

$auditlog | Select-Object -Property CreationDate,UserIds,RecordType,AuditData | Export-Csv -Path c:\AuditLogs\PowerShellAuditlog.csv -NoTypeInformation

Both of these are adapted from this article, which also gives tips on including these commands in a script.

Search the UAL through API

It's also entirely possible for you to access these Unified Audit Log events through the Office 365 Management Activity API. The biggest challenge with this, we find, is getting your authentication and secret management handled correctly. However, this opens up this data to any language and platform that needs to consume it.

Postman

For interacting with this or any other API, I highly recommend Postman! It's an easy to use API client that can keep all your work organized and has options for collaborating with a team. Often, you can find pre-built projects that are 80% configured to talk to the API you want.

This blog from Sergei-Sergeev seems quite helpful if you choose to go this route: https://spblog.net/post/2021/11/02/configure-postman-to-be-easily-used-with-any-azure-ad-protected-api-sharepoint-graph-etc

The Power BI Activity Log API

Knowing that Power BI Administrators would want a limited focus, Microsoft released the Power BI Activity Log API in December 2019. It focuses the UAL on Power BI events only, plus it opens up those events to people with the Power BI Administrator role who may or may not also be Microsoft 365 admins.

It has no UI to search, but has options to use PowerShell and an API endpoint.

The Power BI Activity Log through PowerShell

With the right permissions, you can download events with the Get-PowerBIActivityEvent cmdlet.

Get-PowerBIActivityEvent -StartDateTime 2023-09-15T00:00:00 -EndDateTime 2023-09-16T00:00:00

This will give you very similar output as the UAL.

The Power BI Activity Log through API

In the Power BI Admin API, you can also access the Get Activity Events endpoint.

Here's a sample request to be equivalent to those we have done so far. You can optionally provide a filter argument to narrow down to specific users or activity types.

GET https://api.powerbi.com/v1.0/myorg/admin/activityevents?startDateTime='2023-09-15T00:00:00.000Z'&endDateTime='2023-09-16T00:00:00.000Z'

The output from this is, of course, all JSON.

Permissions needed for Power BI Activity Log

Note that, to use either of these commands, you must have some administrator privileges, namely, you have to either have the Office 365 Global administrator or. Power BI Service Administrator role or use a service principal.

Getting deep into details with Log Analytics (premium required)

If we need even more detail about what is going on in our reports and datasets, we can turn to a feature that became GA in May 2023, to integrate our Power BI Premium workloads with Azure Log Analytics. When enabled, the logs that are captured are the same as those that would be seen by connecting SQL Server Profiler to an active instance of SQL Server Analysis Services, with just a little more information about the exact workspace that the dataset is running in.

In practice, this means that your logs will include query requests and query processing. The logs captured by Azure Log Analytics can include everything generated by these SQL Server Trace Events. Those query processing events have a lot of detail, even breaking down activities at the storage engine vs. formula engine internally, so be warned, the logs can grow large very quickly!

Can Azure Log Analytics give interaction level detail?

The trace events also include detailed information about the visual ID that requested them in their metadata. This means it is theoretically possible to understand, if now how many times a visual was viewed, at least how many times it was rendered and served. However, it only contains the GUID for that visual, and there's no trivial way to tie the GUIDs to your catalog of reports and visuals.

If enough interest is expressed, we will write a much more detailed article about setting up and using the Azure Log Analytics solution to get interaction-level detail. Hopefully this makes you aware of the user analytics capabilities possible with Log Analytics.

Buying a solution: Argus PBI User Activity

Finally, capturing all of this detail is hard. And once you get it, where do you store it? What if you miss some usage detail? How long to retain what you have? After all, you're a great script writer, but you've also got a job to do and questions to answer quickly.

That's where Argus PBI steps in to provide turnkey, whole-tenant monitoring for Power BI. You can check the product out at the link below. There are built-in Power BI reports to show you usage across your entire tenant. This data is retained for at least one year, and it costs way less than the time you would spend writing and maintaining all of your own scripts.

Disclaimer: Greg and Brent are founding partners of Argus PBI.