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
