powershell for audit of cheduled refreshes

po · ‎09-04-2020

Hi,

Have powershell script which gives info on refreshes workspace, dataset name, report name, date of refresh.

However, looking for the gateway the reports refresh under as we have a few gateways.

How can we best report the associated gateway used in these refreshes.

Thanks

# filter out 'report usage metrics model' dataset - always there
foreach ($ds in ($datasets | Where-Object { $_.Name -ne "Report Usage Metrics Model"}) )
{
Write-Verbose "Dataset: $($ds.Name)"
# successful refreshes
$historyItems += Get-RefreshHistory -Workspace $ws -Dataset $ds -Scope $Scope -Top $Top |
Where-Object {$_.serviceExceptionJSON} |
Select-Object -Property id, refreshType, startTime, endTime, status, serviceExceptionJson, `
@{n='WorkspaceName';e={$ws.Name}}, @{n='WorkspaceId';e={$ws.Id}}, @{n='DatasetName';e={$ds.Name}}, @{n='DatasetId';e={$ds.Id}},`
@{n='ErrorCode';e={""}}, @{n='ClusterUri';e={""}}, @{n='ActivityId';e={""}}, @{n='RequestId';e={""}}, @{n='Timestamp';e={""}}

# failing refreshes
$historyItems += Get-RefreshHistory -Workspace $ws -Dataset $ds -Scope $Scope -Top $Top |
Where-Object {-not ($_.serviceExceptionJSON)} |
Select-Object -Property id, refreshType, startTime, endTime, status, @{n='serviceExceptionJson';e={$null}},`
@{n='WorkspaceName';e={$ws.Name}}, @{n='WorkspaceId';e={$ws.Id}}, @{n='DatasetName';e={$ds.Name}}, @{n='DatasetId';e={$ds.Id}},`
@{n='ErrorCode';e={""}}, @{n='ClusterUri';e={""}}, @{n='ActivityId';e={""}}, @{n='RequestId';e={""}}, @{n='Timestamp';e={""}}
}

lbendlin · ‎09-04-2020

You can't. You need to pull the gateway logs for that, separately from each cluster member, and then glue them together.

These logs are notoriously hard to read. In addition they are reporting on Data Source refresh, not necessarily dataset refresh. You need to go hunting for "ObjectId" in column 8. That is the dataset ID.

po · ‎09-07-2020

Thanks for reply.

Do you know of any links with examples of this?

lbendlin · ‎09-07-2020

I can show you my Power Query scripts that I cobbled together, but they are pretty raw. I still hope that Microsoft will one day share that mythical template (or maybe improve the file structure)

po · ‎09-07-2020

Thanks for update.

Do you have a link to these e.g. github or similar?

Would have thought scheduled report audit woudl have been visible via admin panle in power bi - perhaps in future - agree woudl be useful for microsoft tp have specimen templates, reports for common amdin tasks such as this.

lbendlin · ‎09-07-2020

If your data source is in the cloud and you don't allow the gateway to handle those refreshes then the gateway logs will not give you the complete picture. You may want to use the audit logs instead if you are only interested in refreshes (but not in who handled them)

I prepared a blog article on gateway logs, should be out in a week or so.