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 -NoTypeInformationThe 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 out2. 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

PowerShell REST APIs - getting list of all reports in the tenant

12 Replies

Regular Visitor

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

Anonymous
Not applicable
6 years ago
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

Post Prodigy

6 years ago

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

Anonymous

Not applicable

6 years ago

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')

Anonymous
Not applicable
4 years ago
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.
'https://api.powerbi.com/v1.0/myorg/admin/Groups?%24top=4000&%24expand=reports,dashboards,datasets,users,dataflows,workbooks'

Forum Discussion

PowerShell REST APIs - getting list of all reports in the tenant

12 Replies

Related content

Get-PowerBIActivityEvent & PowerShell problems

Get powerbiuser powershell

PowerShell Get a List of Reports and Datasets from My workspace

Power BI Get Data using Powershell

My workspace(reports) download using PowerShell