Extract gateway connections details - Data source and User having access to each connection

Updated version for Gateway admins:

<#
=================================================================
Power BI Gateway & Data Source Audit Script (Needs Gateway Admin Atleast)
=================================================================

This PowerShell script automates the process of collecting and exporting detailed information about Power BI gateways, data sources, and associated users. It performs the following key tasks:

1) Setup and Authentication:
Ensures TLS 1.2 is used.
Installs and imports the MicrosoftPowerBIMgmt module.
Authenticates the user with Power BI.

2) Gateway Overview Collection:
Retrieves all gateway IDs.
For each gateway, it fetches:
Gateway metadata (status, version, type, etc.)
Data sources and their types.
Users with access to each data source.
Aggregates and exports this data to GatewayOverview.csv.

3) Data Source Details:
Collects detailed information about each data source including:
Name, type, credential type, and connection details.
Checks the status (online/offline) of each data source.
Exports this data to GatewayDataSources.csv.

4) User Access Information:
Lists all users with access to each data source.
Captures user details like display name, email, and access rights.
Exports this data to DataSourceUsers.csv.

5)Final Output:
Displays summary statistics: total data sources, total users, and unique users.

Author: Amit N Jadhav
====================================================================================
#>


# Ensure TLS 1.2 is used
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Install the necessary module if not already installed
if (-not (Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser -Force
}

# Import the module
Import-Module -Name MicrosoftPowerBIMgmt

# Log in to Power BI
Login-PowerBI > $null

# Define output file path
$outputCsv1 = "D:\Powershell\GatewayOverview.csv"
$outputCsv2 = "D:\Powershell\GatewayDataSources.csv"
$outputCsv3 = "D:\Powershell\\DataSourceUsers.csv"

#====================================================================================
# Part 1 Generating Gateway overview details
#====================================================================================
# Get all gateways
$allGateways = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways" -Method Get | ConvertFrom-Json
$gatewayIds = $allGateways.value | Select-Object -ExpandProperty id

# Initialize array to hold gateway details
$gatewayDetails = @()

# Loop through each gateway ID and fetch detailed info
foreach ($gatewayId in $gatewayIds) {
    try {
        # Get details from Fabric API
        $fabricUrl = "https://api.fabric.microsoft.com/v1/gateways/$gatewayId"
        $fabricInfo = Invoke-PowerBIRestMethod -Url $fabricUrl -Method Get | ConvertFrom-Json

        # Get gateway status from Power BI API
        $pbiUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId"
        $pbiInfo = Invoke-PowerBIRestMethod -Url $pbiUrl -Method Get | ConvertFrom-Json

        # Get data sources
        $dataSourcesUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"
        $dataSources = Invoke-PowerBIRestMethod -Url $dataSourcesUrl -Method Get | ConvertFrom-Json
        $gatewayDataSources = $dataSources.value
        $totalDataSources = $gatewayDataSources.Count

        # Initialize user collection
        $allUsers = @()

        foreach ($ds in $gatewayDataSources) {
            $dsId = $ds.id
            $dsUsersUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$dsId/users"
            try {
                $dsUsers = Invoke-PowerBIRestMethod -Url $dsUsersUrl -Method Get | ConvertFrom-Json
                $allUsers += $dsUsers.value
            } catch {
                Write-Warning "Failed to fetch users for DataSource ID: $dsId on Gateway ID: $gatewayId"
            }
        }

        # Aggregate user and data source info
        $totalUsers = $allUsers.Count
        $uniqueUsers = $allUsers | Select-Object -Property displayName, emailAddress -Unique
        $uniqueUserCount = $uniqueUsers.Count
        $uniqueUserNames = $uniqueUsers | ForEach-Object { $_.displayName }
        $uniqueUserEmails = $uniqueUsers | ForEach-Object { $_.emailAddress }

        $uniqueDataSourceTypes = $gatewayDataSources | Select-Object -Property DataSourceType -Unique
        $uniqueDataSourceTypeNames = $uniqueDataSourceTypes | ForEach-Object { $_.DataSourceType }

       
# Add combined info to array
        $gatewayDetails += [PSCustomObject]@{
            GatewayName                 = $fabricInfo.displayName
            GatewayId                   = $gatewayId
            GatewayStatus               = $pbiInfo.gatewayStatus
            Version                     = $fabricInfo.version
            NumberOfMemberGateways      = $fabricInfo.numberOfMemberGateways
            LoadBalancingSetting        = $fabricInfo.loadBalancingSetting
            AllowCloudConnectionRefresh = $fabricInfo.allowCloudConnectionRefresh
            AllowCustomConnectors       = $fabricInfo.allowCustomConnectors
            Type                        = $fabricInfo.type
            TotalDataSources            = $totalDataSources
            TotalUsers                  = $totalUsers
            UniqueUsers                 = $uniqueUserCount
            UniqueDataSourceTypeList    = ($uniqueDataSourceTypeNames -join "; ")
            UniqueUserList              = ($uniqueUserNames -join "; ")
            UniqueUserEmailList         = ($uniqueUserEmails -join "; ")

        }
    } catch {
        Write-Warning "Failed to fetch details for Gateway ID: $gatewayId"
    }
}

# Export to CSV
$gatewayDetails | Export-Csv -Path $outputCsv1 -NoTypeInformation -Force

#====================================================================================
# Part 2 Dynamically fetch all gateway IDs & get Gateway Data sources information
#====================================================================================
$allGateways = Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/gateways" -Method Get | ConvertFrom-Json
$gatewayIds = $allGateways.value | Select-Object -ExpandProperty id

# Function to get data sources for a specific gateway
function Get-GatewayDataSources {
    param (
        [string]$gatewayId
    )
    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
    return $response.value
}

# Function to get gateway name
function Get-GatewayName {
    param (
        [string]$gatewayId
    )
#    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId"
    $url = "https://api.fabric.microsoft.com/v1/gateways/$gatewayId"
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
    return $response.displayName

}


# Function to get data source details (to retrieve CredentialType)
function Get-DataSourceDetails {
    param (
        [string]$gatewayId,
        [string]$dataSourceId
    )
    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$dataSourceId"
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
    return $response
}

# Initialize an array to store all data sources
$dataSourcesList = @()

# Loop through each gateway ID and get the data sources
foreach ($gatewayId in $gatewayIds) {
    $gatewayName = Get-GatewayName -gatewayId $gatewayId
    Write-Progress -Activity "Extracting Data Sources" -Status "Processing gateway: $gatewayName" -PercentComplete (($gatewayIds.IndexOf($gatewayId) / $gatewayIds.Count) * 100)

    $dataSources = Get-GatewayDataSources -gatewayId $gatewayId
    foreach ($dataSource in $dataSources) {
        if (-not [string]::IsNullOrEmpty($dataSource.id)) {
            $dsDetails = Get-DataSourceDetails -gatewayId $gatewayId -dataSourceId $dataSource.id

            $dataSourcesList += [PSCustomObject]@{
                GatewayId         = $gatewayId
                GatewayName       = $gatewayName
                DataSourceId      = $dataSource.id
                DataSourceName    = $dataSource.datasourceName
                DataSourceType    = $dataSource.datasourceType
                CredentialType    = $dsDetails.credentialType
                ConnectionDetails = $dataSource.connectionDetails
            }
        }
    }
}

# Export the data sources to a CSV file
$dataSourcesList | Select-Object GatewayName, GatewayId, DataSourceName, DataSourceId, DataSourceType, CredentialType, ConnectionDetails | Export-Csv -Path $outputCsv2 -NoTypeInformation

#Write-Host "Gateway Data source information has been collected." -ForegroundColor DarkYellow

#====================================================================================
# Part 2.1: Determine Data Source Status (Online/Offline) and Error Details
#====================================================================================

# Function to get the status of a data source
function Get-DataSourceStatus {
param (
[string]$gatewayId,
[string]$dataSourceId
)
$url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$dataSourceId/status"
try {
# Make the API request to check the status
$response = Invoke-PowerBIRestMethod -Url $url -Method Get -ErrorAction Stop
return @{
Status = "Online"
# Error = "No Error"
}
} catch {
# If the request fails, return the error code
return @{
Status = "Offline"
#Error = $_.Exception.Response.StatusCode.Value__
}
}
}
# Update the data sources list with status and error details
foreach ($dataSource in $dataSourcesList) {
$statusDetails = Get-DataSourceStatus -gatewayId $dataSource.GatewayId -dataSourceId $dataSource.DataSourceId
$dataSource | Add-Member -MemberType NoteProperty -Name "DataSourceStatus" -Value $statusDetails.Status
# $dataSource | Add-Member -MemberType NoteProperty -Name "Error" -Value $statusDetails.Error
}

# Export the updated data sources to a CSV file
$dataSourcesList | Select-Object GatewayName, GatewayId, DataSourceName, DataSourceId, DataSourceType, DataSourceStatus, CredentialType, ConnectionDetails | Export-Csv -Path $outputCsv2 -NoTypeInformation

Write-Host "Gateway Data source information with data source status has been collected." -ForegroundColor DarkYellow

#====================================================================================
# Part 3: Lists users of all Data sources
#====================================================================================


$userData = @()

foreach ($dataSource in $dataSourcesList) {
    $gatewayId = $dataSource.GatewayId
    $gatewayName = $dataSource.GatewayName
    $datasourceId = $dataSource.DataSourceId
    $datasourceName = $dataSource.DataSourceName
    $dataSourceType = $dataSource.DataSourceType

    Write-Progress -Activity "Extracting Data Source Users" -Status "Processing data source: $datasourceName" -PercentComplete ((($dataSourcesList.IndexOf($dataSource) / $dataSourcesList.Count) * 100))

    $apiUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$datasourceId/users"
    $response = Invoke-PowerBIRestMethod -Url $apiUrl -Method Get | ConvertFrom-Json

    foreach ($user in $response.value) {
        $user | Add-Member -MemberType NoteProperty -Name "GatewayId" -Value $gatewayId
        $user | Add-Member -MemberType NoteProperty -Name "GatewayName" -Value $gatewayName
        $user | Add-Member -MemberType NoteProperty -Name "DataSourceId" -Value $datasourceId
        $user | Add-Member -MemberType NoteProperty -Name "DataSourceName" -Value $datasourceName
        $user | Add-Member -MemberType NoteProperty -Name "DataSourceType" -Value $dataSourceType
        $userData += $user
    }
}

$userData | Select-Object GatewayName, GatewayId, DataSourceId, DataSourceName, DataSourceType, displayName, emailAddress, datasourceAccessRight | Export-Csv -Path $outputCsv3 -NoTypeInformation

Write-Host "Gateway Data source Users information has been collected." -ForegroundColor DarkYellow

$uniqueUsers = $userData | Select-Object -Property displayName -Unique
$uniqueUserCount = $uniqueUsers.Count

Write-Host "`nTotal Data Sources: $($dataSourcesList.Count)" -ForegroundColor White
Write-Host "Total Users: $($userData.Count)" -ForegroundColor White
Write-Host "Unique Users: $uniqueUserCount" -ForegroundColor White

Write-Host "Gateway Data sources have been exported to $outputCsv1" -ForegroundColor Green
Write-Host "Gateway Data source users list exported to $outputCsv" -ForegroundColor Green