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.

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