Export details of your PowerPlatform Gateway assets from your entire tenant.

Export_PowerPlatform_Gateway_Assets.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "powershell",
            "display_name": "PowerShell"
        },
        "language_info": {
            "name": "powershell",
            "codemirror_mode": "shell",
            "mimetype": "text/x-sh",
            "file_extension": ".ps1"
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "<img src=\"https://app.powerbi.com/13.0.10537.111/images/PowerBI96x96.png?raw=true\" width=\"10%\"> ➕\r\n",
                "<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"10%\"> = ❤\r\n",
                "\r\n",
                "# Collect Power Platform Data Gateway information\r\n",
                "\r\n",
                "This Notebook will walk you through how to collect:\r\n",
                "+ All of the Data Gateway Clusters within your tenant\r\n",
                "+ All of the Member servers of thoseData Gateway Clusters\r\n",
                "+ All of the Data Sources on each of those Clusters\r\n",
                "+ All Users of those Data Sources\r\n",
                "\r\n",
                "NOTE: This Notebook exports all the this information to different Worksheets within the same Excel file.  In order to do this, we use the community-driven `ImportExcel` module from the PowerShell Gallery.  Also of note, the name of the Excel file we are exporting to is named `c:\\temp\\PPGateways.xlsx` but you may want to change that.\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "99a22e38-cb12-4dbf-a494-be3330c80e87"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Install the `DataGateway` module from the PowerShell Gallery:"
            ],
            "metadata": {
                "azdata_cell_guid": "67e8ab93-1994-4720-8b5c-85acd9c06960"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Install-Module DataGateway"
            ],
            "metadata": {
                "azdata_cell_guid": "02a5dad2-f074-4a79-ad4c-f4166274a413"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Before you can use any of the cmdlets in the `DataGateway` module, you must first login to your tenant using the `Login-DataGateway` command."
            ],
            "metadata": {
                "azdata_cell_guid": "d93b0b60-31dc-4ecf-b6e5-fd58a52c965d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Login-DataGateway"
            ],
            "metadata": {
                "azdata_cell_guid": "610aba89-20ae-4195-9ea1-487de790f16e"
            },
            "outputs": [],
            "execution_count": 1
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all Data Gateway Clusters"
            ],
            "metadata": {
                "azdata_cell_guid": "73343cd1-0b05-4a16-9ee9-a5cb4791e299"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DataGatewayCluster -Scope Organization |\r\n",
                "Export-Excel -Path c:\\temp\\PPGateways.xlsx -WorksheetName DataGatewayClusters"
            ],
            "metadata": {
                "azdata_cell_guid": "d822a9e5-0305-4477-8712-8da858085d03"
            },
            "outputs": [],
            "execution_count": 2
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect info about each Member of your Data Gateway Clusters"
            ],
            "metadata": {
                "azdata_cell_guid": "41eee8f6-9b54-472a-8a85-1f506323dace"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DataGatewayCluster -Scope Organization | SELECT -ExpandProperty MemberGateways |\r\n",
                "Export-Excel -Path c:\\temp\\PPGateways.xlsx -WorksheetName ClusterMembers"
            ],
            "metadata": {
                "azdata_cell_guid": "6d811048-c85f-4ff0-a62d-e961f3e84df1"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect the info about each Data Source within each Data Gateway Cluster"
            ],
            "metadata": {
                "azdata_cell_guid": "d36a4c00-cdce-493c-92a1-2305c13f6782"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "Get-DataGatewayCluster | Get-DataGatewayClusterDatasource |\r\n",
                "Export-Excel -Path c:\\temp\\PPGateways.xlsx -WorksheetName ClusterDatasource"
            ],
            "metadata": {
                "azdata_cell_guid": "8260e002-2da8-415a-813e-948cf2db3de5"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Collect all of the users of the above Data Sources"
            ],
            "metadata": {
                "azdata_cell_guid": "a9b59c00-4552-45da-91a8-e2fdd741a5d3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "foreach ($clusterDS in Get-DataGatewayCluster | Get-DataGatewayClusterDatasource) \r\n",
                "{\r\n",
                "     $a = Get-DataGatewayClusterDatasourceUser -GatewayClusterId $clusterDS.ClusterId -GatewayClusterDatasourceId $clusterDS.Id\r\n",
                "     $a | Add-Member -NotePropertyName ClusterId -NotePropertyValue $clusterDS.ClusterId; \r\n",
                "     $a | Add-Member -NotePropertyName DatasourceId -NotePropertyValue $clusterDS.Id; \r\n",
                "     $a | Export-Excel -Path c:\\temp\\PPGateways.xlsx -WorksheetName DatasourcePermissions -Append\r\n",
                "}"
            ],
            "metadata": {
                "azdata_cell_guid": "d9960c8d-405c-4ea6-b7b1-00a5cd696154"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Once your done, you can import the `PPGateways.xlsx` file into your Power BI Report."
            ],
            "metadata": {
                "azdata_cell_guid": "2e6a8cf3-e3ad-401a-9002-fdaebe5da626"
            }
        }
    ]
}