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

Usage metrics for a report in Power BI service

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,

Link to Audit logs from the Power BI Admin Portal

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>
Login to the Office 365 account using PowerShell Cmdlet

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

18 thoughts on “Build Your Own Power BI Audit Log; Usage Metrics Across the Entire Tenant

  • Hi Reza,

    Nice article!
    I’m trying to use PowerBI Audit Logs since two month ago but I’m finding some problems with data quality.
    For example for users that have installed PowerBI Application on his mobile, I’m finding recurrent Views of Reports every two hours. I imagine that this is due to the automatic refresh of the mobile application but this is producing wrong data and information about the views of the reports.

    Have you seen the same problem? Any way to solve this problem and correct the data?

    Thanks.

    • Do you mean that the user didn’t actually viewed the report every two hours, and the mobile app automatically did that? I haven’t faced such thing before but can do a test.

  • Hi Reza,

    I tried following your steps but it didn’t work for us. We couldn’t activate the Office 365 Audit log. We contacted Microsoft about it and they redirected us to the “Microsoft 365 usage analytics” content pack and its documentation (I still haven’t manage to make the content pack work though…):
    https://docs.microsoft.com/en-us/office365/admin/usage-analytics/usage-analytics?view=o365-worldwide
    Do you know about this one ? What would be the best option for a usage report across all workspace that can be edited and then shared to non Admin users ?

    Thanks

    • Hi Camille
      Not sure what exactly you mean by it didn’t work for you. Did you get an error? what was that?
      the method I suggested here, can be used to export data to CSV, then you can have your report on it. exactly as I explained in this post, and then you can share it with non-admin users for sure.
      Cheers
      Reza

  • Hi I’m not allowed to install the Exchange Management Tools. So I can’t use the Search-UnifiedAuditLog command. I’m looking for another alternative, any thoughts about it?
    Thanks for sharing

  • Thank you for this article. I have been needing refresh logs for several of my Power BI .pbix files. I have created a simple Power Query file that goes out to the web and gets the time and date as of the moment the “refresh” button is clicked in the Power BI file. This creates a one row 6 column data table with the current refresh time. Every time the there is a refresh, the row is recreated (and the old refresh time/date is lost). I would like to append this individual data row to a data table that would act like a log data table and collect the refresh data table date row as it is created. In other words, the log file data table would collect (and append) each refresh data table row as it is created. The only requirement is that the log data table be in Power BI file. The logic of creating log file data table (once) and then appending to it for future rows escapes me as it seems like Power Query is constantly building its data tables from scratch.

    • Hi William
      Still not 100% if I follow your method of getting the logs. However, to answer your question: Power BI dataset will be wiped out and filled again each time for the refresh. You can use Incremental Load, however, that is a premium only feature at the moment. Or alternatively, you can use tools such as Azure Data Factory to run M script and store the output somewhere.
      Cheers
      Reza

  • Good afternoon Rad,

    Thank you for making this post, once I can get it working it will be incredibly useful!

    Everything worked well until the reading audit log step. When I enter –

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

    The following error appears:

    Search-UnifiedAuditLog : The term ‘Search-UnifiedAuditLog’ is not recognized as the name of a cmdlet, function, script
    file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct
    and try again.
    At line:1 char:1
    + Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (G …
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Search-UnifiedAuditLog:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    Any idea of what to do?

    Thank you in advanced.

    • Have you done all the previous steps?
      can you copy/paste your entire PowerShell script until that step here? I believe an step is missing which caused this.
      Cheers
      Reza

    • Your user id should have role as ‘Exchange Administrator’ to get access to Search-UnifiedAuditLog’

  • Hi,

    If we are using Power BI embedded feature in Dynamic365 for FnO and pin the reports in Dynamic 365, will we be able to retrieve the usage metrics and audit logs for the reports. Can you please also let me know the requirements in terms of licensing and subscriptions also if it is possible.

    Thanks,
    R

    • Whenever users login to a system to see the Power BI report, it will be logged and can be accessed through the audit log. It also includes secure embed method. But if you use Power BI embedded, or Publish to web, the metrics would be different because the log would be different.
      Cheers
      Reza

  • This has been the best resource on this, so THANK YOU!
    Two Questions:
    1) If you want more than 5000 results, you explained in the video but I didn’t quiet understand what you meant by looping, etc. Can you give more detail and the script if possible?
    2) You had stated “This process can be automated of course using SSIS or Azure Data Factory, In fact, this is something that we will hopefully add to the next version of Power BI Helper.” is this available yet?

    • Hi Britni,

      Before I answer your questions:
      There is a newer and much easier method to fetch the audit log; using PowerShell scripts of Power BI itself. that way, it won’t even have the 5000 rows limitation. I will write about it sometimes soon 😊

      Now back to your questions:
      If you have more than 5000 log rows, you need to create a kind of loop structure in PowerShell, there are some commands and ways to do that. you can run the PowerShell script for each day for example and then append the entire result. this is one way of doing it.

      We are working on Power BI Snapshot features to have this ability, but because that needs a service always up and running on our side, it might not come as a free tool.

      Cheers
      Reza

  • Hi Reza;

    I’m getting the below error trying to run the final command. Can you help?

    Search-UnifiedAuditLog : The term ‘Search-UnifiedAuditLog’ is not recognized as the name of a cmdlet, function, script
    file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct
    and try again.
    At line:1 char:1
    + Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (G …
    + ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Search-UnifiedAuditLog:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Leave a Reply

%d bloggers like this: