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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

Power BI dashboard and reports come with a usage metric, which you can see how users used this content. There is also another usage metrics across the entire tenant, which you can see it if you have access to 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 it, in which workspace these are located and etc. This information is not easily accessible in the Power BI Service. In this blog post, I’m going to 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 Power BI book from Rookie to Rock Star.

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 in the top of the dashboard or report in the Power BI service.

Here is an example of usage metrics report

I previously wrote an article and explained how you can 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 dashboard across the tenant;

The challenge with this report is that there are not many customizations you can do 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 Power BI tenant. Let’s check to see how it is possible.

Audit Log

Power BI service leverages the office 365 logging system. As a result, 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 it in the activities drop-down list,

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

This output can be then exported to be used 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

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, which 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;

After pressing enter, choose Y as the answer;

if we are going to access the entire audit log across tenant, we need to have access to the admin account, so we need to ask for the credentials. the script below does that;

After successful login, we can start with creating a session connection to office365 using the same credentials;

then the script line below imports that session data;

So far, we have got the output below in PowerShell

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

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.

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

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

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, if you have more than that number, then you need to split it into multiple exports, I may right about it in the future.

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 start with getting data from CSV.

After selecting the source file, click on Edit to go to Edit queries 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 Header

You will have the log output as below;

As you can see, there is some generic information, and then the main details are all in the AuditData field. 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 Power BI report. 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. Also, PowerShell can be useful to get 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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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: