list of reports with dataset refresh and owner names

[PBInewbie21]

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!

[v-yalanwu-msft]

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.

[monica2563]

The information provided above works, but how do I get the information to export?

[PBInewbie21]

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

}
}