cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

powershell for audit of cheduled refreshes

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={""}}
}

8 REPLIES 8
Highlighted
Super User II
Super User II

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.

Highlighted

Thanks for reply.

 

Do you know of any links with examples of this?

Highlighted

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)

Highlighted

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.

Highlighted

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.

Highlighted

Thanks for update 

 

datasources are on premise.

 

If coudl update post to your blog once published that would be great.

 

Thanks in advance.

Highlighted

Here you go

https://community.powerbi.com/t5/Community-Blog/Working-with-Power-BI-Gateway-logs/ba-p/1352383

 

More questions than answers, but maybe it can help with the discussion.

Highlighted

Hi,

 

Thanks will have a read through

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.