Measuring Power BI Adoption using PowerShell, SharePoint and Power BI - Part 1

[Anonymous]

The Power BI Office 365 Adoption content pack provides insight into Exchange, OneDrive, SharePoint, Skype and Yammer. Surprisingly it does not include Power BI.

As of September 2017, the Power BI Service includes usage metrics for dashboards and reports. The usage metrics are excellent for dashboard and report consumption, but they do not lend themselves to enterprise adoption.

For those wanting to measure Power BI usage at the enterprise level, we are left with leveraging the Office 365 Audit Logs.

In Part 1 of this blog, we will get and store the Audit Log data using PowerShell and SharePoint. In Part 2 we will transform and visualize the Audit Log data using Power BI. If all goes well you can create a report that looks similar to this:

Part 1 – PowerShell and SharePoint

The appended script below extracts Audit Log data using a custom date range and stores that output into a locally stored CSV file. The CSV file is then uploaded to SharePoint for central access and storage.

The “Search-UnifiedAuditLog” PowerShell command is used to search and output Audit Log data.

The “Connect-PNPOnline” PowerShell command is used to connect and post the Audit Log data to a SharePoint site.

Set-ExecutionPolicy Unrestricted -Scope CurrentUser -Force
 
# Get security credential based on a user name and password
$User_Credential = Get-Credential
 
# Get Exchange cmdlets
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $User_Credential -Authentication Basic -AllowRedirection
Import-PSSession $Session
 
# Controls number of days to retrieve from audit log; customize as required
$Number_Days = 7
 
# Date control variables
$End_Date = Get-Date -format "yyyy-MM-dd"
$Start_Date = (Get-Date).AddDays(-$Number_Days).ToString("yyyy-MM-dd")
 
# Output folder and file; customize as required
$Output_Folder = "C:\Temp"
$Csv_File = "$Output_Folder\Audit_Log-$End_Date.csv"
 
# Check for output folder; create if required
IF (!(Test-Path $Output_Folder -PathType Container)) {
New-Item -ItemType Directory -Force -Path $Output_Folder
}
 
# Get Audit Log data for PowerBI activities
$Result = Search-UnifiedAuditLog -StartDate $Start_Date -EndDate $End_Date -RecordType PowerBI -ResultSize 5000 | Export-Csv $Csv_File
 
# Site and document library name; customize as required; no sub-folders
$SPO_Site = "Your SharePoint Online site URL here"
$SPO_Folder = "Your Document Library name here"
 
# Connect to SharePoint / OneDrive for Business
Connect-PNPOnline -url $SPO_Site -credential $User_Credential
 
# Upload csv file
Add-PnPFile -Path $Csv_File -Folder $SPO_Folder

These commands require elevated permissions within the O365 tenant and with some tweaking the routine could be scheduled.

Thanks for stopping by.

NY