Skip to content

Instantly share code, notes, and snippets.

@jcallaghan
Last active March 5, 2024 12:30
Show Gist options
  • Save jcallaghan/86b391716cdec5b39926e196b28a1496 to your computer and use it in GitHub Desktop.
Save jcallaghan/86b391716cdec5b39926e196b28a1496 to your computer and use it in GitHub Desktop.
Power BI Workspace Members Report

Power BI Workspace Members Script

This PowerShell script lists all Power BI workspaces (1) in your organization, along with the access/membership list (2). The members User Principal Name (UPN) and their permission are listed and includes users, groups and app identities.

image

How it works

  • The script starts by importing the MicrosoftPowerBIMgmt module, which provides the cmdlets needed to interact with Power BI.
  • It then logs into Power BI and connects to the Power BI service account.
  • The script retrieves all workspaces in the organization using the Get-PowerBIWorkspace cmdlet.
  • It creates an empty array to store the results.
  • The script then iterates over each workspace, and for each workspace, it iterates over each user.
  • For each user, it creates a new PowerShell object with the properties we're interested in: WorkspaceName, WorkspaceId, WorkspaceType, WorkspaceState, MemberUserIdentifier, MemberPrincipleType, MemberUserPrincipalName, and MemberAccessRight.
  • It adds this object to the results array.
  • Finally, the script exports the results to a CSV file named PowerBIWorkspaceUsers.csv. The CSV file includes headers for each property, and each row in the CSV file represents a user in a workspace.

How to run the script

  1. Open PowerShell.
  2. Navigate to the directory where the script is located.
  3. Run the script by typing .\PowerBI-Workspaces.ps1 and pressing Enter.
  4. The script will prompt you to log into Power BI. Enter your credentials.
  5. The script will run and create a CSV file named PowerBIWorkspaceUsers.csv in the current directory.

Requirements

  • PowerShell
  • The MicrosoftPowerBIMgmt PowerShell module. You can install it by running Install-Module -Name MicrosoftPowerBIMgmt in PowerShell.
  • A Power BI account with the necessary permissions to list workspaces and their users.
# Install the PowerBI module
# Install-Module -Name MicrosoftPowerBIMgmt
# Import the PowerBI module
Import-Module MicrosoftPowerBIMgmt
# Login to Power BI
Login-PowerBI
# Connect to Power BI (Optional -UserName "yourusername@yourdomain.com")
Connect-PowerBIServiceAccount
# Get all workspaces in the organization and include all workspaces
$workspaces = Get-PowerBIWorkspace -Scope Organization -Include All
# Create an empty array to store the results
$results = @()
# Iterate over each workspace
foreach ($workspace in $workspaces) {
# Iterate over each user in the workspace
foreach ($user in $workspace.users) {
# Create a new PSObject with the properties we want to display
$result = New-Object PSObject -Property @{
WorkspaceName = $workspace.Name
WorkspaceId = $workspace.Id
WorkspaceType = $workspace.Type
WorkspaceState = $workspace.State
MemberUserIdentifier = $user.Identifier
MemberPrincipleType = $user.PrincipalType
MemberUserPrincipalName = $user.UserPrincipalName
MemberAccessRight = $user.AccessRight
}
# Add the result to the results array
$results += $result
}
}
# Output the results in a table
# $results | Select-Object WorkspaceName, WorkspaceId, WorkspaceType, WorkspaceState, MemberUserIdentifier, MemberPrincipleType, MemberUserPrincipalName, MemberAccessRight | Format-Table
# Output the results to a CSV file
$results |
Select-Object @{name='WorkspaceName';expression={$_.WorkspaceName}},
@{name='WorkspaceId';expression={$_.WorkspaceId}},
@{name='WorkspaceType';expression={$_.WorkspaceType}},
@{name='WorkspaceState';expression={$_.WorkspaceState}},
@{name='WorkspaceMemberUserIdentifier';expression={$_.MemberUserIdentifier}},
@{name='WorkspaceMemberPrincipleType';expression={$_.MemberPrincipleType}},
@{name='WorkspaceMemberAccessRight';expression={$_.MemberAccessRight}},
@{name='WorkspaceMemberUserPrincipalName';expression={$_.MemberUserPrincipalName}} |
Export-Csv -Path PowerBIWorkspaceUsers.csv -NoTypeInformation
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment