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:


clip_image002


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"

##https://gallery.technet.microsoft.com/PowerShell-Bulk-Upload-b9e9d600

#   start-sleep -seconds 6000

#}

#End Loop




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


image


3. Select “Edit” to update the underlying data


image


4. Remove the First Row of the CSV file


image

5. Set the first row as headers


image

6. Change all the Date columns to dates.

(I also changed the ResultIndex and ResultCount to numerics)

image


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

image


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”

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/

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


Comments (0)

Skip to main content