Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell
<# Make sure you have the modules installed.
You only need the ImportExcel module if you want to export the results to Excel files. #>
Install-Module DataGateway
Install-Module ImportExcel
<# Discovery #>
# All Gateway Clusters in your tenant.
Get-DataGatewayCluster -Scope Organization
# All nodes of all Gateway Clusters in your tenant.
Get-DataGatewayCluster -Scope Organization | SELECT -ExpandProperty MemberGateways
# All Data Sources for all Gateways you have Admin right to.
Get-DataGatewayCluster | Get-DataGatewayClusterDatasource
# All permissions of all Data Sources for all Gateways.
# NOTE: You need to be an admin or user of the Data Source in order to see its permissions.
foreach ($clusterDS in Get-DataGatewayCluster | Get-DataGatewayClusterDatasource)
{
$a = Get-DataGatewayClusterDatasourceUser -GatewayClusterId $clusterDS.ClusterId -GatewayClusterDatasourceId $clusterDS.Id
$a | Add-Member -NotePropertyName ClusterId -NotePropertyValue $clusterDS.ClusterId;
$a | Add-Member -NotePropertyName DatasourceId -NotePropertyValue $clusterDS.Id;
$a | Format-Table -Autosize
}
<# Export Portion
Everything here is the same as above, except that the results are going to separate Worksheets
within the same Excel file. #>
Get-DataGatewayCluster -Scope Organization |
Export-Excel -Path c:\temp\PPGateways.xlsx -WorksheetName DataGatewayClusters
Get-DataGatewayCluster -Scope Organization | SELECT -ExpandProperty MemberGateways |
Export-Excel -Path c:\temp\PPGateways.xlsx -WorksheetName ClusterMembers
Get-DataGatewayCluster | Get-DataGatewayClusterDatasource |
Export-Excel -Path c:\temp\PPGateways.xlsx -WorksheetName ClusterDatasource
foreach ($clusterDS in Get-DataGatewayCluster | Get-DataGatewayClusterDatasource)
{
$a = Get-DataGatewayClusterDatasourceUser -GatewayClusterId $clusterDS.ClusterId -GatewayClusterDatasourceId $clusterDS.Id
$a | Add-Member -NotePropertyName ClusterId -NotePropertyValue $clusterDS.ClusterId;
$a | Add-Member -NotePropertyName DatasourceId -NotePropertyValue $clusterDS.Id;
$a | Export-Excel -Path c:\temp\PPGateways.xlsx -WorksheetName DatasourcePermissions -Append
}
@thegurugit

This comment has been minimized.

Copy link

@thegurugit thegurugit commented Sep 29, 2020

@SQLvariant

Could you please help me in below ?

I tried above script, but it give me blank sheet for ClusterDataSource.

My objective is to get everything in csv using powershell that is displayed on GUI : file attached
dgpbi

thank you in advance

@SQLvariant

This comment has been minimized.

Copy link
Owner Author

@SQLvariant SQLvariant commented Sep 29, 2020

Are you getting the correct number of items back when you run Get-DataGatewayCluster | Get-DataGatewayClusterDatasource?

@thegurugit

This comment has been minimized.

Copy link

@thegurugit thegurugit commented Sep 29, 2020

@SQLvariant

There are no results
image

@SQLvariant

This comment has been minimized.

Copy link
Owner Author

@SQLvariant SQLvariant commented Oct 5, 2020

@SQLvariant

There are no results
image

What about if you run just that first one? Does Get-DataGatewayCluster return any data?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.