Power BI

Content by Charles Sterling

Automatically download and use the Power BI Audit Log

As you saw in my previous post i have been playing with the Power BI Audit Log to find out which users are using my tenant, when are they doing this and what are they playing with.   If you haven’t seen the Power BI Audit Log in the wild it looks like the following:


To do this is pretty easy with less than 10 steps!

Step 1.  Export the Power BI Audit Log into CSV

Remember to run the following PowerShell as Admin!  Also included the PowerShell (commented out) that will run in an infinite loop as to continually updating the underlying PowerBI source.

Set-ExecutionPolicy RemoteSigned

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic –AllowRedirection

Import-PSSession $Session

$csvFile = “c:\diad\auditlog.csv”

Add-SPOFile -UploadFilePath “C:\diad\auditlog.csv ” -userName chass@powercommunity.onmicrosoft.com -siteURL “https://powercommunity.sharepoint.com/sites/PowerDemos” -ListName Document -FolderPath PlanLib

#infinite loop

#    while(1)


$result = Search-UnifiedAuditLog -StartDate 1/1/2016 -EndDate 5/15/2017 -RecordType PowerBI | Export-Csv $csvFile

##The following lines are for copying to my SharePoint site (see https://blogs.msdn.microsoft.com/charles_sterling/2017/04/15/using-sharepoint-online-or-onedrive-for-business-to-get-out-of-the-power-bi-refresh-game/)

#.\BulkUploadSharePointCSOM.ps1 -UserName “user@powercommunity.onmicrosoft.com” -Password “password” -SiteURL “https://powercommunity.sharepoint.com/sites/PowerDemos” -DocLibName “Shared Documents” -Folder “c:\chassps”


#   start-sleep -seconds 6000


#End Loop

2.  Bring in the resulting Power BI Audit log into Power BI Desktop


3. Select “Edit” to update the underlying data


4. Remove the First Row of the CSV file


5. Set the first row as headers


6. Change all the Date columns to dates.

(I also changed the ResultIndex and ResultCount to numerics)


7. Answer the questions you have been wondering about like…Who is using which reports, when are the busiest times of the day etc etc


Remember if you want to get the latest log information, to update the source per this blog post:

Using SharePoint Online or OneDrive for Business to get out of the Power BI “Refresh Game”


Again thanks to @Guyinacube for the pointers on the initial PowerShell commands