Build Your Own Power BI Audit Log; Usage Metrics Across the Entire Tenant

Power BI dashboard and reports come with a usage metric, which you can see how users used this content. There is another report for usage metrics across the entire tenant, which you can see if you have access to the Power BI Administrator account under Admin Panel in the Power BI Service. However, what if you want to create your own detailed usage metrics report across the entire tenant? How if you want to see across all workspaces in the tenant, how was the consumption of reports and dashboards? Who created reports, who are users of them, in which workspace these are located and etc. This information is not easily accessible in the Power BI Service. In this article and video, I will show you how to extract the Audit log from Office 365, export it into text files, and create a Power BI report from it, or in other words, How to create your custom usage metrics report across the tenant. If you like to learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Video

The Problem

Let’s first explain what the issue is. Each report and dashboard have a usage metrics report, which shows where and when, and by who the content has been used. You can usually find it at the top of the dashboard or report in the Power BI service.

Here is an example of a usage metrics report

I previously wrote an article and explained how you could customize it and create your version of this report by save as, and then editing the report. And this is an example of a customized usage metrics report I created:

However, there is still a big challenge. This report will show me the usage metrics only in the current workspace, not other workspaces. I have to produce this report in each workspace separately!

Usage Metrics of Report and Dashboard, even after customizing, shows only usage of the content in the current workspace, not all workspaces.

On the other hand, if you have access to the Power BI Administrator account, under Admin Portal, you can find the usage metrics of all reports and dashboards across the tenant;

The challenge with this report is that you cannot make many customizations here.

The usage metrics in the Admin Portal gives you the metrics related to all content in Power BI tenant, but it is not customizable, and lots of details are missing.

So considering the two points above, we are looking for a way to get a usage metrics report which gives us information about all activities in the Power BI tenant. Let’s check to see how it is possible.

Audit Log

Power BI service leverages the office 365 logging system. The Power BI activities are already logged into Office365; you just need to find them. If you have an account with sufficient privilege to the audit log, you can go to Admin Portal, and under Audit Log,

This will open the Audit Log Search in the Office portal

Now you can search for the activities by selecting them in the activities drop-down list,

You can also add other criteria, such as your search’s start and end date and users to search through. here is an example output;

This output can then be exported as a source of a Power BI report. However, you cannot export all Power BI activities of all users. You will get an error like this:

Exporting the log data manually, and also partially each time, is not practical, we need to find another way to extract the audit log information.

PowerShell for Extract the Audit Log

There is more than one way to extract the Audit log of Power BI. Here are some of the methods;

• Using Office365 Audit log PowerShell Cmdlets (used in this article)
• Using Power BI Cmdlets for PowerShell, You can learn more about it here.
• Using REST API for Power BI, You can learn more about it here.
• Using a third-party tool, such as Power BI Helper.

Each of the methods above can be used to export the Audit log. The method I explain here is using the Office365 Audit log Cmdlets for PowerShell. This method can also extract logs about all other parts of Office 365.

Before I start the PowerShell part, I have to say special thanks to Aaron Nelson for his wonderful help on the PowerShell side. I am a newbie when it comes to PowerShell, and Aaron helped me a lot to learn a bit about it 🙂 PowerShell is an expression-based tool that helps to automate some of the work for admins. PowerShell can be used to access the audit log of Office365. First of all, you would need to open PowerShell as Administrator.

Then start with the script below; (the script below is needed only once for a machine)

Install-Package ExchangeOnlineManagement
Import-Module ExchangeOnlineManagement

After installing and importing the module, you can then login to your Power BI account using the command below;

Connect-ExchangeOnline -UserPrincipalName <Power BI email account>

To access the entire audit log across the tenant, we need access to the admin account; this is the account you have to use in the above script

After successful login, we can start accessing the audit log. But for that, you will need another package; ExchangePowerShell. Install, and Import is using the script below (needed only once per machine);

Install-Package ExchangePowerShell
Import-Module ExchangePowerShell

Now, as the final step, we can read the audit log using the Search-UnifiedAuditLog function. Here is an example:

Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date) -RecordType PowerBIAudit -ResultSize 10

This will give you only ten rows of audit log (I have limited the result size to 10) as below;

As you can see in the above screenshot, we will have a list of log activities with a field named AuditData, which includes anything about the activity, including object name and ID, the operation, the time and date of the operation, and users who did the operation and the result of the operation.

The next step is to format this data and export it to CSV, which is the task that the script below does.

Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date) -RecordType PowerBIAudit -ResultSize 5000 | ConvertTo-Csv | Out-File c:\PowerBIAuditLog.csv

This will export the output of 5000 log entries in the period of the last three months to CSV format such as below;

Here are a few tips about the export above;

• The default time period that Office365 keeps the audit log is 90 days. You can change it if you want. That is why I got back 90 days in my date periods.
• The export only supports up to 5000 audit log transactions. You need to split it into multiple exports if you have more than that number.

Power BI Report for Audit Log

Now that we have the CSV file of all audit logs let’s use that as a data source in Power BI. Open a New Power BI file, and get data from CSV.

After selecting the source file, click on Transform Data to go to the Power Query Editor window.

Now let’s remove the top 1 row, as this row doesn’t have any useful information for us;

In the next step, go to the Transform tab and Use First Row as Headers.

You will have the log output below;

As you can see, there is some generic information, and then the main details are all in the AuditData field. The AuditData field is formatted as JSON. let’s remove all other fields and only keep the AuditData;

As the field is in JSON format, you can choose the Parse JSON option, as illustrated in the screenshot below;

JSON data will be as a record in every cell, and now you can expand it to underlying columns;

Here are all audit log data now:

The remaining is just visualizing this data. Here are some sample visualizations I did as a report; this report shows all users, and their operations on items (report, dataset, dashboard, workspaces)

and other visualization that shows all the objects and the operation done on them with the list of users.

Summary

Yes, you can create your own custom audit or usage metrics report across the entire tenant from all workspaces. You can leverage PowerShell to export the audit log into CSV and then use that as the source of the Power BI report. This process can be automated using SSIS, Azure Data Factory, or Task Scheduler. This information is also available through Power BI Helper. Also, PowerShell can be useful for getting much other information from the service. In the next article, I’ll explain how to get even more information about the Power BI objects in your tenant using PowerShell. Now go ahead and create your own metrics report, and let me know if you have any comments or questions in the comments below.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.