API POWERBI - List reports that use uncertified visuals

[jkrumb]

"Hello everyone, currently I am able to list all the reports present in all workspaces. I would like to be able to list the reports that use uncertified visuals. Thank you for your help."

[jkrumb]

"Thank you for your feedback, indeed, this information is important to me. I will propose the idea on https://ideas.fabric.microsoft.com."

[lbendlin]

Sounds good. What have you tried and where are you stuck?

The Playground gives you an idea how to query report artifacts

Power BI Playground - Developer Sandbox

[jkrumb]

# Azure AD Application Parameters
$tenantId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$clientId = "xxxxxxxx-xxxxxxxx-xxxxxxxx-xxxx-xxxxxxxxxx"
$clientSecret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
$authorityUrl = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
$scope = "https://analysis.windows.net/powerbi/api/.default"
$apiUrl = "https://api.powerbi.com/v1.0/myorg/groups"

# Get the access token
$authBody = @{
grant_type = "client_credentials"
client_id = $clientId
client_secret = $clientSecret
scope = $scope
}
$response = Invoke-RestMethod -Method Post -Uri $authorityUrl -ContentType "application/x-www-form-urlencoded" -Body $authBody

$accessToken = $response.access_token

# Check if the token was successfully retrieved
if (-not $accessToken) {
Write-Host "Error while obtaining the access token."
return
}

# List of workspace IDs
$workspaceIds = @(
"xxxxxxxxx-xxxxxxx-xxxxxx-xxxxxxxxx-xxxxxxxxxxxxx"
)

# List to store data
$data = @()

# Call the Power BI API to list reports in each workspace
$headers = @{
Authorization = "Bearer $accessToken"
}

foreach ($workspaceId in $workspaceIds) {
Write-Host "Processing workspace: $workspaceId"

try {
$reportsUri = "$apiUrl/$workspaceId/reports"
$reportsResponse = Invoke-RestMethod -Method Get -Uri $reportsUri -Headers $headers
$reports = $reportsResponse.value

if ($reports) {
foreach ($report in $reports) {
$reportId = $report.id
$reportName = $report.name
$reportUrl = $report.webUrl

# Check for uncertified visuals
$visualsUri = "$apiUrl/$workspaceId/reports/$reportId/visuals"
$visualsResponse = Invoke-RestMethod -Method Get -Uri $visualsUri -Headers $headers
$visuals = $visualsResponse.value

$hasUncertifiedVisuals = $false
foreach ($visual in $visuals) {
if (-not $visual.isCertified) {
$hasUncertifiedVisuals = $true
break
}
}

if ($hasUncertifiedVisuals) {
$data += [pscustomobject]@{
WorkspaceId = $workspaceId
ReportName = $reportName
ReportId = $reportId
ReportUrl = $reportUrl
HasUncertifiedVisuals = $true
}
}
}
} else {
Write-Host " No reports found in this workspace."
}
Write-Host "------------------------"
} catch {
Write-Host " Error when calling the Power BI API for workspace $workspaceId: $_"
Write-Host " Full response: $($_.Exception.Response.Content | ConvertTo-Json -Depth 5)"
}
}

# Export the data to a CSV file with UTF-8 encoding
$csvFilePath = "C:\Users\xxxxxxxxxxxxxx\xxxxxxxxxxx.csv"
$data | Export-Csv -Path $csvFilePath -NoTypeInformation -Encoding UTF8

Write-Host "Reports with uncertified visuals have been exported to the CSV file: $csvFilePath"

[lbendlin]

If you want to simplify the authentication process you can use Invoke-PowerBIRestMethod instead.

If you have more than 5000 workspaces you'll need to use $top and $skip and combine multiple calls.

$visualsUri = "$apiUrl/$workspaceId/reports/$reportId/visuals"

There is no such call in the current API.  If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.fabric.microsoft.com

Here is the sandbox approach: