Forum Discussion
New Usage Metrics Report across workspaces
We had the same problem and decided the best approach was to do this ourselves. We wrote some simple PowerShell scripts that connect to the Power BI REST APIs which then pull down the usage metrics. Then we have a SSIS package that picks up that output and loads it into a SQL table which we use going forward to query.
We had the same problem and decided the best approach was to do this ourselves. We wrote some simple PowerShell scripts that connect to the Power BI REST APIs which then pull down the usage metrics. Then we have a SSIS package that picks up that output and loads it into a SQL table which we use going forward to query.
That's great, could you maybe upload these scripts (without sensitive information of course) so I could reuse them with a bit of tweaking?
I guess this is the road we need to go to.
I don't see a way to attach a file here so I'll paste a couple sample scripts here.
1. Usage Metrics Script.....pulls down of yesterday's usage metrics and outputs to csv file
#credentials for connecting to PBI Service
Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint XXXXXX -ApplicationId XXXXXX -Tenant XXXXXX#get date info for appending to csv export
$CurrentDate = Get-Date
$CD = $CurrentDate.AddDays(-1)
$CurrentDate2 = $CD.ToString('MM-dd-yyyy')#Get Yesterday's date
$Today = Get-Date
$Yesterday = $Today.AddDays(-1)#Get dates to use in API and then call API using PBI cmdlet
$startDate = $Yesterday.ToString("yyyy-MM-ddT00:00:00.000")
$endDate = $Yesterday.ToString("yyyy-MM-ddT23:59:59.999")
$Activities = Get-PowerBIActivityEvent -StartDateTime $startDate -EndDateTime $endDate | ConvertFrom-Json
$Activities | Format-Table#export results to CSV
#INSERT FILE PATH BELOW
$Activities| Export-Csv -Path Z:\Usage_Metrics\PowerBIUsageMetrics_$CurrentDate2.csv -NoTypeInformation2. Workspace Reports.....loops through each workspace and provides all reports
Connect-PowerBIServiceAccount -ServicePrincipal -CertificateThumbprint XXXXX -ApplicationId XXXXX -Tenant XXXXX
#loops through each workspace and grabs reports
Get-PowerBIWorkspace -Scope Organization -Include All -All |
ForEach-Object {
$Workspace = $_.name
$WorkspaceId = $_.Id
foreach ($Report in $_.Reports) {
[PSCustomObject]@{I
Workspace = $Workspace
WorkspaceId = $WorkspaceId
ReportName = $Report.Name
ReportID =$Report.Id}}} | Export-CSV "C:\Script Output\Reports "