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 it possible to extract a list with details:1. list of data source (Connection name, source type, server/dbname or linketc, Authentication type).2. list of users having access to each connection.

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

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

11 Replies

Anonymous

Not applicable

2 years 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

v-huizhn-msft
Microsoft Employee
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
  8 years ago
  
  Angelia, Thanks for the quick response
  
  Is there any chance of this beeing made available in future. Is there any Idea that i can vote(link).
  - v-huizhn-msft
    Microsoft Employee
    8 years ago
    Hi Anonymous,
    
    Please review the following idea and comment on it.
    
    https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31710763-gateway-management-tool-cli
    
    Thanks,
    Angelia
Anonymous
Not applicable
6 years ago
Hello,
for the record, point 1 (list of data sources) can be done using the API :
https://docs.microsoft.com/en-us/rest/api/power-bi/gateways/getdatasources

Forum Discussion

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

11 Replies

Related content

Connection failed using a Folder connection in a pipeline

connection to dataflows

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

dataflow connection

Database connectivity.