Last active
May 23, 2024 15:47
-
-
Save SQLvariant/fd3b77e597fc6e13118636bf0d682383 to your computer and use it in GitHub Desktop.
Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# 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 | |
} |
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?
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Absolutely brilliant script to use as an admin - awesome job