Someone asked on Power BI Service on how to get all reports across all Premium workspaces so I started reading documentation and found that even a PowerShell noob like me can generate a piece of code for such request, so here we go.
First launch PowerShell ISE as an admin.
Next we need to install a module named MicrosoftPowerBIMgmt using the command
Install-Module -Name MicrosoftPowerBIMgmt
Once installed we can use the following method to Login to Power BI Service
Login-PowerBIServiceAccount
You will get a prompt like this one:
Once we are logged in we can invoke another method of getting all the workspacess in the tenant.
And we are going to store the collection of workspaces in a variable called $Workspaces
$Workspaces = Get-PowerBIWorkspace
$Workspaces
If you select the above code and run it it will return all the workspaces for you. If you need all the workspacess accross organization then use this
$Workspaces = Get-PowerBIWorkspace -Scope Organization
$Workspaces
It will also return those workspaces that have been deleted so you can use a foreach loop to filter out those.
$Workspaces = Get-PowerBIWorkspace -Scope Organization
#$Workspaces
foreach($W in $Workspaces){
if($W.State -eq "Active"){
Write-Output $W.Name
}
}
If you want to keep Deleted ones just replace "Active" with "Deleted"
Okay moving on we can filter for Premium workspaces using IsOnDedicatedCapacity property:
$Workspaces = Get-PowerBIWorkspace -Scope Organization
foreach($W in $Workspaces){
if($W.IsOnDedicatedCapacity -eq $true){
Write-Output $W.Name
}
}
My Premium workspaces have PPU suffixed so that it is easy to confirm.
But as you can see even My Workspace appears because I have changed the type to PPU so to exclude that we can add another condition in the IF statement:
Now once we have a collection of target workspaces we can iterate on the reports in them. To get reports as a collection we can write the following code:
$Reports = Get-PowerBIReport -WorkspaceId $w.Id
And then we can loop on the collection using another foreach loop
Finally to make it more obvious which report belongs to which workspace we can concatenate workspace and report name.
If you want to show something like "No Reports" for the workspaces that do not have any report you can add another check:
if($Reports.Count -eq 0){
Write-Output "$($W.Name) - No Reports"
}
Here is the complete code:
#Install-Module -Name MicrosoftPowerBIMgmt
#Login-PowerBIServiceAccount
CLS # Clear output window
$Workspaces = Get-PowerBIWorkspace -Scope Organization
foreach($W in $Workspaces){
if($W.IsOnDedicatedCapacity -eq $true -and $W.Type -eq "Workspace"){
$Reports = Get-PowerBIReport -WorkspaceId $W.Id -Scope Organization
if($Reports.Count -eq 0){
Write-Output "$($W.Name) - No Reports"
}
else{
foreach($R in $Reports){
Write-Output "$($W.Name) - $($R.Name)"
}
}
}
}
Comments