Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

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

Hello Power BI Admins, I have a requirment to produce a list of all the connections configured on the On-Premise or Enterprise Gateway. I did not find anything online so posting question here. is...

Need Help

v-huizhn-msft
8 years ago
Hi Anonymous,

After research, there is no such list including data source and users. You need to produce them one by one.

Best Regards,
Angelia

Anonymous

Not applicable

1 year ago

You can use following hard coded Powershell script on your local system provided that you have Power BI admin access role in your organization:
1) Open Notepad and copy-Paste following Powershell script and save as Datasourcedetails.PS1 on your Local system path For example: C:\Users\Datasourcedetails.PS1
2) Open Powershell, Change Directory to C:\Users\ folder and run command .\Datasourcedetails.PS1 and it will take you to browser window to authenticate.

#========================================================================
# This PowerShell Script creates 2 Files
# 1) GatewayDataSources.csv: Lists all data source on On-Premises Gateways for all Gateway IDs mentioned in $gatewayIds = @("","") function
# 2) DatasourceUsers.csv: Lists users of all Data sources on On-Premises Gateways for all Gateway IDs mentioned in $gatewayIds = @("","") function.
# Author: Amit J
#========================================================================
# Part 1 : Lists all data source on On-Premises Gateways for all Gateway IDs mentioned in $gatewayIds = @("","") function
# Install the necessary module if not already installed
if (-not (Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
    Install-Module -Name MicrosoftPowerBIMgmt -Scope CurrentUser
}

# Import the module
Import-Module -Name MicrosoftPowerBIMgmt

# Log in to Power BI
Login-PowerBI

# Define the gateway IDs, you can define multiple gateway ID or gateway cluster ID if you have multiple gateways
$gatewayIds = @("Enter your gateway ID 1 or gateway cluster ID 1 here", "Enter your gateway ID 2 or gateway cluster ID 2 here")

# Function to get data sources for a specific gateway
function Get-GatewayDataSources {
    param (
        [string]$gatewayId
    )
    # Construct the full API URL for the gateway
    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources"
    Write-Output $url
    # Make the API request
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json

    return $response.value
}

# Function to get gateway name
function Get-GatewayName {
    param (
        [string]$gatewayId
    )
    # Construct the full API URL for the gateway
    $url = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId"
    # Make the API request
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json

    return $response.name
}

# 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

    # Display progress
    Write-Progress -Activity "Processing data..." -Status "Processing gateway $gatewayName" -PercentComplete (($gatewayIds.IndexOf($gatewayId) / $gatewayIds.Count) * 100)

    $dataSources = Get-GatewayDataSources -gatewayId $gatewayId
    foreach ($dataSource in $dataSources) {
        $dataSourcesList += [PSCustomObject]@{
            GatewayId = $gatewayId
            GatewayName = $gatewayName
            DataSourceId = $dataSource.id
            DataSourceName = $dataSource.datasourceName
            DataSourceType = $dataSource.datasourceType
            ConnectionDetails = $dataSource.connectionDetails
        }
    }
}

# Export the data sources to a CSV file, You need to define path where GatewayDataSources.csv will be saved
$dataSourcesList | Select-Object GatewayName, GatewayId, DataSourceName, DataSourceId, DataSourceType, ConnectionDetails | Export-Csv "C:\Users\GatewayDataSources.csv" -NoTypeInformation

Write-Output "Data sources have been exported to GatewayDataSources.csv"
#============================================================================
# Part 2: Lists users of all Data sources on On-Premises Gateways for all Gateway IDs mentioned in $gatewayIds = @("","") function.

# Define the output CSV file path, You need to define path where DatasourceUsers.csv will be saved
$outputCsv = "C:\Users\DatasourceUsers.csv"

# Initialize an array to hold the user data
$userData = @()

# Loop through each row in the CSV file
foreach ($dataSource in $dataSourcesList) {
    $gatewayId = $dataSource.GatewayId
    $gatewayName = $dataSource.GatewayName
    $datasourceId = $dataSource.DataSourceId
    $datasourceName = $dataSource.DataSourceName
    $dataSourceType = $dataSource.DataSourceType

    # Display progress
    Write-Progress -Activity "Processing data..." -Status "Processing data source $datasourceName" -PercentComplete ((($dataSourcesList.IndexOf($dataSource) / $dataSourcesList.Count) * 100))

    # Define the API endpoint
    $apiUrl = "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$datasourceId/users"

    # Make the API request
    $response = Invoke-PowerBIRestMethod -Url $apiUrl -Method Get | ConvertFrom-Json

    # Extract the user list and add gatewayId and datasourceId to each user
    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
        Write-Output $user.displayName
    }
}

# Export the user data to a CSV file
$userData | Select-Object GatewayName, GatewayId,  DataSourceId, DataSourceName, DataSourceType, displayName, emailAddress, datasourceAccessRight | Export-Csv -Path $outputCsv -NoTypeInformation

Write-Output "User list exported to $outputCsv"

AmitNJadhav3

Frequent Visitor

1 year ago

Here is updated version you can try:

<#
====================================================================================
Power BI Gateway & Data Source Audit Script
====================================================================================

This PowerShell script performs the following tasks:

1. **Setup**:
   - Installs and imports the `MicrosoftPowerBIMgmt` module.
   - Logs into the Power BI service.

2. **Part 1: Gateway and Data Source Information**:
   - Retrieves all Power BI gateway IDs.
   - For each gateway:
     - Fetches the gateway name.
     - Retrieves all associated data sources.
     - For each data source:
       - Fetches detailed information including credential type and connection details.
   - Exports the collected data to `GatewayDataSources.csv`.

3. **Part 2: Data Source User Access**:
   - For each data source:
     - Retrieves the list of users with access.
     - Adds metadata (gateway name, data source name/type).
   - Exports the user access data to `DataSourceUsers.csv`.

4. **Part 3: Gateway Overview Report**:
   - Groups data by gateway and generates a summary report.
   - For each gateway:
     - Calculates the total number of data sources.
     - Calculates the total number of users.
     - Identifies unique users and lists their names and email addresses.
     - Identifies unique data source types and lists them.
   - Exports the summary data to `PowerBIGatewayOverview.csv`.

5. **Summary Output**:
   - Displays the total number of data sources, total users, and unique users.

Output Files:
- `GatewayDataSources.csv`: Contains gateway and data source details.
- `DataSourceUsers.csv`: Contains user access information for each data source.
- `PowerBIGatewayOverview.csv`: Contains a summary of gateway-level metrics, including:
   - Total Data Sources
   - Total Users
   - Unique Users
   - List of Unique Users
   - List of Unique User Email Addresses
   - List of Unique Data Source Types

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

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

# Import the module
Import-Module -Name MicrosoftPowerBIMgmt
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
# Log in to Power BI
Login-PowerBI > $null

# Define output file path
$outputCsv1 = "<#Enter output path#>\GatewayDataSources.csv"

#====================================================================================
# Part 1 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"
    $response = Invoke-PowerBIRestMethod -Url $url -Method Get | ConvertFrom-Json
    return $response.name
}

# 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 $outputCsv1 -NoTypeInformation

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

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

$outputCsv = "<#Enter output path#>\DataSourceUsers.csv"
$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 $outputCsv -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


#====================================================================================
# Part 3: Generate Gateway Overview Report
#====================================================================================

# Define the output file path for the overview report
$outputCsvOverview = "<#Enter output path#>\PowerBIGatewayOverview.csv"

# Prepare data for the overview report
$overviewData = @()

# Group data sources and users by Gateway
$gateways = $dataSourcesList | Group-Object -Property GatewayId

foreach ($gatewayGroup in $gateways) {
    $gatewayId = $gatewayGroup.Name
    $gatewayName = ($gatewayGroup.Group | Select-Object -First 1).GatewayName

    # Data sources for the current gateway
    $gatewayDataSources = $gatewayGroup.Group
    $totalDataSources = $gatewayDataSources.Count

    # Users for the current gateway
    $gatewayUsers = $userData | Where-Object { $_.GatewayId -eq $gatewayId }
    $totalUsers = $gatewayUsers.Count

    # Unique users for the current gateway
    $uniqueUsers = $gatewayUsers | Select-Object -Property displayName, emailAddress -Unique
    $uniqueUserCount = $uniqueUsers.Count
    $uniqueUserNames = $uniqueUsers | ForEach-Object { $_.displayName }
    $uniqueUserEmails = $uniqueUsers | ForEach-Object { $_.emailAddress }

    # Unique data source types for the current gateway
    $uniqueDataSourceTypes = $gatewayDataSources | Select-Object -Property DataSourceType -Unique
    $uniqueDataSourceTypeNames = $uniqueDataSourceTypes | ForEach-Object { $_.DataSourceType }

    # Add the gateway overview data to the report
    $overviewData += [PSCustomObject]@{
        GatewayName             = $gatewayName
        GatewayId               = $gatewayId
        TotalDataSources        = $totalDataSources
        TotalUsers              = $totalUsers
        UniqueUsers             = $uniqueUserCount
        UniqueDataSourceTypeList = ($uniqueDataSourceTypeNames -join "; ") # Join unique data source types into a single string
        UniqueUserList          = ($uniqueUserNames -join "; ") # Join unique user names into a single string
        UniqueUserEmailList     = ($uniqueUserEmails -join "; ") # Join unique user email addresses into a single string
    }
}

# Export the overview data to a CSV file
$overviewData | Export-Csv -Path $outputCsvOverview -NoTypeInformation

# Display a message indicating the overview report has been generated
Write-Host "Power BI Gateway Overview report has been generated." -ForegroundColor Green
Write-Host "Overview report exported to $outputCsvOverview" -ForegroundColor Green

AmitNJadhav3

Frequent Visitor

1 year ago

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

matepol
Regular Visitor
8 months ago
AmitNJadhav3 - thank you for the useful script!

AmitNJadhav3

Frequent Visitor

1 year ago

With Help of Power BI tenant ID, Admin API Enabled Service Principal, Service Principal Secret authentication:

<#
=================================================================
Power BI Gateway & Data Source Audit Script (Needs Power BI tenant ID,Admin API Enabled Service Principal, Service Principal Secret authentication)
=================================================================

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 Service Principal.

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

#==============================================================
# Service Principal Login Setup
#==============================================================
$tenantId = "Your tenantId"
$clientId = "Your Service Principal id"  
$clientSecret = "Your Service Principal Secret"

# Convert client secret to secure string
$secureClientSecret = ConvertTo-SecureString $clientSecret -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($clientId, $secureClientSecret)

# Connect using Service Principal
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId $tenantId

#==============================================================


# Define output file path
$outputCsv1 = "E:\OUT\GatewayOverview.csv"
$outputCsv2 = "E:\OUT\GatewayDataSources.csv"
$outputCsv3 = "E:\OUT\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

Forum Discussion

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

Related content

Connection failed using a Folder connection in a pipeline

Power BI Oracle Connection Error – ORA-50201 (Failed to connect / parse connect string)

connection to dataflows

dataflow connection

Database connectivity.