Hi all, hopefully last noob question on how to best use the REST APIs. I have a requirement I thought would be simple: "list all reports in the entire tenant, and which workspace they live in".
My current code is:
$Workspaces = Get-PowerBIWorkspace -Scope Organization -All $Reports = ForEach ($workspace in $Workspaces) { Write-Host $workspace.Name ForEach ($report in (Get-PowerBIReport -Scope Organization -WorkspaceId $workspace.Id)) { [pscustomobject]@{ WorkspaceID = $workspace.Id WorkspaceName = $workspace.Name ReportID = $report.Id ReportName = $report.Name ReportURL = $report.WebUrl ReportDatasetID = $report.DatasetId } } } $Reports | Export-Csv -Path $logpath -NoTypeInformation
The script runs...but I'm hitting several issues I'm not sure how to work around:
1. I seem to be seeing reports that were deleted months ago...and there doesn't appear to be an attribute I can use to filter those out
2. I'm still researching this - but it appears that for reports that are shared, each user is getting a "copy" of the report in their own workspace (or at least the script above is showing that). There also doesn't appear to be a way to filter these out from "real" reports a user created in their own workspace...if I exclude personal workspaces then I loose visibility into ALL user created reports, not just "shared" ones. I did find a code snippet that suggests filtering down to reports where "isOwnedByMe" = TRUE, but the "isOwnedByMe" attribute doesn't show up when an admin lists all the reports...only when a user lists their own reports
This seemed like it was going to be so easy...but obviously not so much. I appreciate any help / insights in to how to work around around this - thank you!
Scott
Solved! Go to Solution.
Dear Scott,
Not sure if anyone has gotten back to you, or if you've solved it yourself, but I recently set up a Tenancy overview report using the Power Shell cmdlets and rest API in Power BI and noticed teh same.
With a bit of investigation i noticed that they aren't acutally DELETED reports, they're Reports that have been Published via APPS as the Report ID and Dataset ID are different to the Reports stored in the Workspace. This is because different versions can exist in both spaces, and an App report doesn't automatically get replaced when publishing a new report to a workspace.
I found this out by using the cmdlet Invoke-PowerBIRestMethod -url 'Apps' -Method Get to retrieve published apps and their reports.
It's also worth noting that this only occurs when using the -scope Organization on Get-Reports.
The way i dealt with this was those ReportID's that matched when using the invoke-powerbirestmethod were published reports and datasets, those that didn't were Workspace Reports (unpublished). This gave me a much cleaner overview of the tenancy.
Hope this helps.
Chris
I recommend this admin api call, possible new api since this first posted. I was getting errors because of too many api calls. This is one stop shopping.
Dear Scott,
Not sure if anyone has gotten back to you, or if you've solved it yourself, but I recently set up a Tenancy overview report using the Power Shell cmdlets and rest API in Power BI and noticed teh same.
With a bit of investigation i noticed that they aren't acutally DELETED reports, they're Reports that have been Published via APPS as the Report ID and Dataset ID are different to the Reports stored in the Workspace. This is because different versions can exist in both spaces, and an App report doesn't automatically get replaced when publishing a new report to a workspace.
I found this out by using the cmdlet Invoke-PowerBIRestMethod -url 'Apps' -Method Get to retrieve published apps and their reports.
It's also worth noting that this only occurs when using the -scope Organization on Get-Reports.
The way i dealt with this was those ReportID's that matched when using the invoke-powerbirestmethod were published reports and datasets, those that didn't were Workspace Reports (unpublished). This gave me a much cleaner overview of the tenancy.
Hope this helps.
Chris
Hi All,
Do you have the example complete code for this?
Saw this post and and can get a list of workspaces and reports in them but if a user has deleted a report and republished with same name that report is showing with same name twice - once for the real report and one for the deleted one which gives message below when click the url.
Looking for complete code if anyone has it availalble which gives workspace inventory just for live reports in that workspace.
Thanks
Hi, here's a script we use to describe the entire environment. At this point it's sadly out of date - they've added cmdlet and API options to make this easier, I just haven't had time to go back and refresh the scripts.
p.s. the account used to log in is a service account that also has the power BI admin permission. It needs this to be able to see content in all the workspaces.
p.p.s. I also completely suck at PowerShell, sorry! Things like error trapping are beyond me right now...
Hope this helps!
Scott
#
# list all workspaces, reports and dashboards across every workspace. Tie "current date" is added to each row...Power BI will then be able to tell thea "first date"
# that something appeared.
#
Write-Host "Starting script:" (Get-Date).ToString('MM/dd/yyyy hh:mm:ss tt')
# connect to PBI service using the service account
$User = "*** PBI login USER ID ***"
$PWord = ConvertTo-SecureString -String "*** Password for that user ***" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
Connect-PowerBIServiceAccount -Credential $UserCredential
$logbase = "*** Directory or UNC path where you want the output to go to***"
#################################
# Capacities #
#################################
# get capacities info
Write-Host "******* Exporting Capacities *****"
$url = "capacities"
$Capacities = (ConvertFrom-Json (Invoke-PowerBIRestMethod -Url $url -Method Get)).value
# export capacities
$logpath = $logbase + "capacities.csv"
$Capacities | select id, displayName, sku, state, region | Export-Csv -Path $logpath -NoTypeInformation
# export capacity admins
$logpath = $logbase + "capacity_admins.csv"
$capacity_admins =
ForEach ($capacity in $Capacities)
{
ForEach ($admin in $capacity.admins)
{
[pscustomobject]@{
CapacityID = $capacity.id
CapacityName = $capacity.displayName
AdminUser = $admin
}
}
}
$capacity_admins | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Workspaces #
#################################
#
# Scott notes:
# Workspace type "Workspace" is new workspace experience
# "Group" is old workspace experience
# "PersonalGroup" is "My Workspace" for end users
#
# State "Active"
# "Removing" these are already deleted...
# "Deleted"
# "Deprovisioning failed"
#
#
# Things blow up later (like listing datasets, datasources, etc.) if states other than "Active" are in the list...so I'm going to filter to active only...
# Also may remove personal workspaces for now just to improve speed...
#
#
Write-Host "******* Exporting Workspaces *****"
$Workspaces = Get-PowerBIWorkspace -Scope Organization -All | where state -eq "Active" | where type -ne "PersonalGroup"
# export workspaces
$logpath = $logbase + "workspaces.csv"
$Workspaces | select Id, Name, Type, State, IsReadOnly, IsOrphaned, CapacityId | Export-Csv -Path $logpath -NoTypeInformation
# export workspace users
$logpath = $logbase + "workspace_users.csv"
$workspace_users =
ForEach ($workspace in $Workspaces)
{
ForEach ($user in $workspace.Users)
{
[pscustomobject]@{
WorkspaceID = $workspace.id
WorkspaceName = $workspace.Name
AccessRight = $user.AccessRight
User = $User.UserPrincipalName
}
}
}
$workspace_users | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Datasets #
#################################
# Note - we can't just get all datasets - because the resulting dataset object doesn't tell which workspace it is in. So going to have to loop over each workspace
# and grab just the datasets in it to make that link. Might dump ALL datasets into a separate test file to make sure we don't miss any...
Write-Host "******* Exporting Datasets ******"
$logpath = $logbase + "datasets.csv"
$Datasets =
ForEach ($workspace in $Workspaces)
{
ForEach ($dataset in (Get-PowerBIDataset -Scope Organization -WorkspaceId $workspace.Id))
{
[pscustomobject]@{
WorkspaceID = $workspace.Id
WorkspaceName = $workspace.Name
DatasetID = $dataset.Id
DatasetName = $dataset.Name
DatasetAuthor = $dataset.ConfiguredBy
IsRefreshable = $dataset.IsRefreshable
IsOnPremGatewayRequired = $dataset.IsOnPremGatewayRequired
}
}
}
$Datasets | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Datasources #
#################################
# Loop over all datasets to get the associated datasources
# Scott ToDo - add try/catch blocks. Some datasources blow up with error "ConvertFrom-Json : Cannot bind argument to parameter 'InputObject' because it is null."
# I believe this is because it is an invalid data source - for example reading text file that doesn't exist, etc.
Write-Host "******* Exporting Data Sources *****"
$logpath = $logbase + "datasources.csv"
$Datasources =
ForEach ($dataset in $Datasets)
{
$url = "groups/" + $dataset.WorkspaceID + "/datasets/" + $dataset.DatasetID + "/datasources"
$sources = (ConvertFrom-Json (Invoke-PowerBIRestMethod -Url $url -Method Get)).value
ForEach($datasource in $sources)
{
[pscustomobject]@{
WorkspaceID = $dataset.WorkspaceID
WorkspaceName = $dataset.WorkspaceName
DatasetID = $dataset.DatasetID
DatasetName = $dataset.DatasetName
DataSourceID = $datasource.datasourceId
DataSourceType = $datasource.datasourceType
DataSourceConnection = $datasource.connectionDetails
DataSourceGatewayID = $datasource.gatewayId
}
}
}
$Datasources | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Dashboards #
#################################
# Note - similar to datasets, we can't just grab dashboards - because the resulting object doesn't tell which workspace it is in. So going to have to loop over each workspace
# and grab just the dashboards in it to make that link.
$logpath = $logbase + "dashboards.csv"
$Dashboards =
ForEach ($workspace in $Workspaces)
{
Write-Host "Writing dashboards...on workspace: " $workspace.Name
ForEach ($dashboard in (Get-PowerBIDashboard -Scope Organization -WorkspaceId $workspace.Id))
{
[pscustomobject]@{
WorkspaceID = $workspace.Id
WorkspaceName = $workspace.Name
DashboardID = $dashboard.Id
DashboardName = $dashboard.Name
}
}
}
$Dashboards | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Reports #
#################################
# same as dashboards and datasets - loop over each workspace and list the reports in it.
$logpath = $logbase + "reports.csv"
$Reports =
ForEach ($workspace in $Workspaces)
{
Write-Host "Writing reports...on workspace: " $workspace.Name
ForEach ($report in (Get-PowerBIReport -Scope Organization -WorkspaceId $workspace.Id))
{
[pscustomobject]@{
WorkspaceID = $workspace.Id
WorkspaceName = $workspace.Name
ReportID = $report.Id
ReportName = $report.Name
ReportURL = $report.WebUrl
ReportDatasetID = $report.DatasetId
}
}
}
$Reports | Export-Csv -Path $logpath -NoTypeInformation
#################################
# Apps #
#################################
$url = "apps"
$Apps = (ConvertFrom-Json (Invoke-PowerBIRestMethod -Url $url -Method Get)).value
# export apps
$logpath = $logbase + "apps.csv"
$Apps | Export-Csv -Path $logpath -NoTypeInformation
# export app dashboards
$logpath = $logbase + "app_dashboards.csv"
$AppDashboards =
ForEach ($app in $Apps)
{
$url = "apps/" + $app.Id + "/dashboards"
$app_dashboards = (ConvertFrom-Json (Invoke-PowerBIRestMethod -Url $url -Method Get)).value
ForEach ($dashboard in $app_dashboards)
{
[pscustomobject]@{
AppID = $app.Id
AppName = $app.Name
DashboardID = $dashboard.Id
DashboardName = $dashboard.displayName
IsReadOnly = $dashboard.isReadOnly
}
}
}
$AppDashboards | Export-Csv -Path $logpath -NoTypeInformation
# export app reports
$logpath = $logbase + "app_reports.csv"
$AppReports =
ForEach ($app in $Apps)
{
$url = "apps/" + $app.Id + "/reports"
$app_reports = (ConvertFrom-Json (Invoke-PowerBIRestMethod -Url $url -Method Get)).value
ForEach ($report in $app_reports)
{
[pscustomobject]@{
AppID = $app.Id
AppName = $app.Name
ReportID = $report.Id
ReportName = $report.Name
ReportURL = $report.webURL
}
}
}
$AppReports | Export-Csv -Path $logpath -NoTypeInformation
Disconnect-PowerBIServiceAccount
Write-Host "Script complete:" (Get-Date).ToString('MM/dd/yyyy hh:mm:ss tt')
When you say: p.s. the account used to log in is a service account that also has the power BI admin permission. It needs this to be able to see content in all the workspaces.
Do you mean that the service_account OR ServicePrincipalName (or any account actually) needs to HAVE the PowerBI Administrator role? Thanks in advance!
Hi Scott,
I found that your script is perfect. But I have one more consider.
Can I add 1 more column which is username in Workspace list?
Hi Scott,
This script is exactly the kind of thing I've been looking for, but I keep running into problems after 200 results. Is that to be expected? I have read on other posts that the PBI API is limited to 200 calls - do you think that could effect this PowerShell script?
We have 600+ workspaces and 3600+ reports so I am still looking for a way to list them all. Any advice appreciated
All the best
Vicky
Hi Scott,
Thanks again. I'm new to powershell especially in power bi so this will be a good help.
Will have a read through.
Thanks
Does anyone have an idea how to list all users per report? I would like to pass the report name rather than going through all reports. I was not able to find that while googling.
Thanks,
Stan
If you have admin privleges, I recommend this admin api call, possible new api since this first posted. I was getting errors because of too many api calls. This is one stop shopping.
Hi Chris - I appreciate the insight. I'll take a look and see if our data is showing up similar to what you describe. I'm honestly finding it very difficult to use the APIs and the audit logs - it seems like Microsoft keeps updating / changing the structure, which makes it almost impossible to produce reports which work across history.
Thanks very much for the lead, I'll play with this next week and see what comes out of it (and will update this thread so hopefully others can find this info easier).
Thanks again!
Scott
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!