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.
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.
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;
$UserCredential = Get-Credential
After successful login, we can start with creating a session connection to office365 using the same credentials;
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
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:
Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date) -RecordType PowerBI -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.
Now 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 PowerBI -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;
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.
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.