Creating a Power BI Report from the Audit Logs stored in Azure Blob Storage
Following on from my blog post on How you can store All your Power BI Audit Logs easily and indefinitely in Azure, I wanted to share with you how I connected to the Blob Storage Files and created my Power BI report, to report on the audit logs.
Before starting with the Power BI report, I first had to get the following details which would allow me to connect to the csv files stored in Blob storage.
I needed to get the Storage Account Name and the Access Key
I go went into the Azure Portal, click on All Resources and find my storage account called “mvpnloganalytics” (Which is where my csv files are stored)
- This is the name of my storage account “mvpnloganalytics”
- I then clicked on mvpnloganalytics, this then opened the storage account.
Next, I clicked on Access keys
- I made a note of the Key
Connecting to Azure Blob Storage
Next, I had to connect to the Azure Blog Storage access the files.
- I went into Power Query Editor, clicked on New Source, clicked on more
I then searched for Blob and selected Azure Blob Storage
- And clicked on Connect
In the Account Name or URL, I put in my Storage Account name
- I then clicked OK
I was then asked for the Account key which I put in
- And then clicked Connect
I was then prompted with the Navigator, where I selected my folder called “pbitest” which was where I had configured the csv files to be stored in Azure Blob Storage
- I then clicked OK
- I am not going to bore you with all the details, but what I did was to then get the CSV files in a format which allowed me to easily create the report.
- The good news is that at the end of the blog post you can get a copy of the PBIT which you can see all the steps I used to achieve the dataset.
- Here is what it looked like once I was done in Power Query.
- I then loaded it to the data model.
Creating the Report
The final step was to create the report as shown below.
All the measures and details are in the PBIT below.
My initial report was to track the usage of the reports by Report Name and which events were being used.
There is a wealth of information in the audit logs, where it is possible to create many useful reports.
As an example, I want to ensure that all reports are ONLY shared via an App and not via the Sharing button. I did this by creating another report page and filtering as shown below.
I then created a subscription to this report page, so daily I could ensure that sharing was done in the correct manner.
I have shown in this blog post how to connect to files stored in Azure Blob storage and then create useful reports from the Power BI Audit logs. I hope that you have found this useful and allows you to quickly and easily create reports of value to your organization.
Here is a copy of the PBIT: Power BI Audit Log – Blob Storage.pbit
A quick note on the PBIT is that there are 2 tables in the Power Query Editor, and I have disabled the one prior to 2020-01-05, because this is when I started using the new Audit Logs, which have a slightly different format.
Thanks for reading and as always if there are any comments please leave them in the section below.