Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to make a list of Reports with it's last refresh date and owner names for all the workspaces in one sheet. Is it possible to write a script for that purpose? Any place we can find such scripts already available?
Thanks!
Hi, @PBInewbie21 ;
Here is the powershell script that you can use in conjunction with the REST API to get the refresh history of all workspaces.
1.First you could Installing the Power BI PowerShell Management module
2.then run it (if you have other need ,you could change it a little)
Connect-PowerBIServiceAccount
$Workspaces = Get-PowerBIWorkspace
foreach($workspace in $Workspaces)
{
$DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}
foreach($dataset in $DataSets)
{
$URI = "groups/" + $workspace.id + "/datasets/" + $dataset.id + "/refreshes"
#$OutFile = $ExportFolder + '\' + $workspace.Name + '-' + $dataset.Name + '.json'
$Results = Invoke-PowerBIRestMethod -Url $URI -Method Get | ConvertFrom-Json
foreach($result in $Results.value)
{
$errorDetails = $result.serviceExceptionJson | ConvertFrom-Json -ErrorAction SilentlyContinue
$row = New-Object psobject
$row | Add-Member -Name "Workspace" -Value $workspace.Name -MemberType NoteProperty
$row | Add-Member -Name "Dataset" -Value $dataset.Name -MemberType NoteProperty
$row | Add-Member -Name "refreshType" -Value $result.refreshType -MemberType NoteProperty
$row | Add-Member -Name "startTime" -Value $result.startTime -MemberType NoteProperty
$row | Add-Member -Name "endTime" -Value $result.endTime -MemberType NoteProperty
$row | Add-Member -Name "status" -Value $result.status -MemberType NoteProperty
$row | Add-Member -Name "errorCode" -Value $errorDetails.errorCode -MemberType NoteProperty
$row | Add-Member -Name "errorDescription" -Value $errorDetails.errorDescription -MemberType NoteProperty
Write-Host $row
}}}
3.The final output is shown below:
powerbi-powershell/Export-RefreshHistory.ps1 at master · microsoft/powerbi-powershell · GitHub
https://technovert.com/blog/refreshing-all-datasets-in-power-bi-using-rest-api/
https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/get-refresh-history
https://community.powerbi.com/t5/Service/Get-Refresh-History-for-all-workspaces-via-API/m-p/1867631
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The information provided above works, but how do I get the information to export?
Thanks for your reply. The script is very helpful to get me started. I have two questions :
1) Anyway to find Dataset owner name? Couldn't find in Dataset properties. Do you know how to fatch it?
2) Is it possible to find Datasource/ Database name using Powershell? I modified your script a bit with Get-PowerBIDataSource but can't find Datasource or database name. Here is the code I'm running
#Login-PowerBIServiceAccount
$Workspaces = Get-PowerBIWorkspace
foreach($workspace in $Workspaces)
{
$DataSets = Get-PowerBIDataset -WorkspaceId $workspace.Id | where {$_.isRefreshable -eq $true}
foreach($dataset in $DataSets)
{
$DataSource = Get-PowerBIDatasource -DatasetId $dataset.Id
Write-Host $dataset.name "-" $DataSource.Name
}
}
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.