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

by NormYoung Frequent Visitor on ‎01-09-2018 10:12 AM - last edited on ‎01-11-2018 12:48 PM by Power BI Admin MindyJ

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:

 

PBI-Adoption-Report

 

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

Comments
by dpclay41 New Member
‎01-17-2018 08:11 AM - edited ‎01-17-2018 08:12 AM

Good morning, ty for the script.  Id like ot know why I am getting access denied when I am a global admin?

 

cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
New-PSSession : [outlook.office365.com] Connecting to remote server outlook.office365.com failed with the following
error message : [ClientAccessServer=CY4PR1801CA0002,BackEndServer=dm2pr0701mb1309.namprd07.prod.outlook.com,RequestId=2
ab5bf63-5586-4152-85fd-5a0355eda77a,TimeStamp=1/17/2018 3:57:42 PM] Access Denied For more information, see the
about_Remote_Troubleshooting Help topic.
At C:\temp\Get_Auditlogs.ps1:7 char:12
+ $Session = New-PSSession -ConfigurationName Microsoft.Exchange -Conne ...
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (System.Manageme....RemoteRunspace:RemoteRunspace) [New-PSSession], PSRemotin
   gTransportException
    + FullyQualifiedErrorId : -2144108477,PSSessionOpenFailed
Import-PSSession : Cannot validate argument on parameter 'Session'. The argument is null. Provide a valid value for
the argument, and then try running the command again.
At C:\temp\Get_Auditlogs.ps1:8 char:18
+ Import-PSSession $Session
+                  ~~~~~~~~
    + CategoryInfo          : InvalidData: (Smiley Happy [Import-PSSession], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.ImportPSSessionCommand

Search-UnifiedAuditLog : The term 'Search-UnifiedAuditLog' is not recognized as the name of a cmdlet, function, script
file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct
and try again.
At C:\temp\Get_Auditlogs.ps1:27 char:11
+ $Result = Search-UnifiedAuditLog -StartDate $Start_Date -EndDate $End ...
+           ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Search-UnifiedAuditLogSmiley Frustratedtring) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Connect-PNPOnline : The term 'Connect-PNPOnline' is not recognized as the name of a cmdlet, function, script file, or
operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At C:\temp\Get_Auditlogs.ps1:34 char:1
+ Connect-PNPOnline -url $SPO_Site -credential $User_Credential
+ ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Connect-PNPOnlineSmiley Frustratedtring) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Add-PnPFile : The term 'Add-PnPFile' is not recognized as the name of a cmdlet, function, script file, or operable
program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At C:\temp\Get_Auditlogs.ps1:37 char:1
+ Add-PnPFile -Path $Csv_File -Folder $SPO_Folder
+ ~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Add-PnPFileSmiley Frustratedtring) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

 

 

 

by NormYoung Frequent Visitor
on ‎01-17-2018 10:49 AM

Installing the PnP Powershell cmdlets should resolve the error.

 

https://github.com/SharePoint/PnP-Tools

 

NY