Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell

GatewayClusters.ps1

<# 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
}

@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

thank you in advance

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

@SQLvariant

There are no results

@SQLvariant

There are no results

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

Absolutely brilliant script to use as an admin - awesome job

Just wanted to say Tons of thanks 🥇

this is awesome, thanks so much for sharing! is there a way to get the region of the gateway in this script?

Congratulations, very good for those who want to understand the structure of the Azure Power BI RestFul API! I used it as a basis for 1 year =)