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.
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic –AllowRedirection
$csvFile = "c:\diad\auditlog.csv"
Add-SPOFile -UploadFilePath "C:\diad\auditlog.csv " -userName firstname.lastname@example.org -siteURL "https://powercommunity.sharepoint.com/sites/PowerDemos" -ListName Document -FolderPath PlanLib
$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 "email@example.com" -Password "password" -SiteURL "https://powercommunity.sharepoint.com/sites/PowerDemos" -DocLibName "Shared Documents" -Folder "c:\chassps"
# start-sleep -seconds 6000
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:
Again thanks to @Guyinacube for the pointers on the initial PowerShell commands