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"
