Retrieving list of users for all workspaces in your PowerBI tenant using Powershell

From previous blogpost(s):

• Deleting Power BI datasets using Powershell
• Longterm-storage of Power BI activity logs and statistics using Powershell

Determining and checking who has access to a particular workspace in your organisation can be time consuming task. You can always retrieve the list of workspaces and access the list of all users with PowerShell cmdlet Get-PowerBIWorkspace.

The results of this cmdlet gives you a great way to get to valuable information easy and fast. And the following script can do just that.

# 1. Login to app.power.bi
$user = "YourAzure.Email@domain.com"
$pass = "YourStrongP422w!!rd"

$SecPasswd = ConvertTo-SecureString $pass -AsPlainText -Force
$myCred = New-Object System.Management.Automation.PSCredential($user,$SecPasswd)
Connect-PowerBIServiceAccount -Credential $myCred


# 2. Get list of users and workspaces
$WorkSpace_Users = Get-PowerBIWorkspace -Scope Organization -Include All -All

# 3. Iterate through the users for each workspace (and exclude Personal Workspaces)
$WorkSpace_Users | ForEach-Object {
    $Workspace = $_.name
    foreach ($User in $_.Users) {
        [PSCustomObject]@{
            WorkspaceName = $Workspace
            UserName   =$user.Identifier
            AccessPermission = $User.accessright    
                                           }
            }
} | Select UserName, AccessPermission, WorkspaceName |  Where-Object {$Workspace -NotLike "PersonalWorkspace *"}

I have excluded the Personal Workspaces.

In addition, there is a little R script, that can be used to visualize the this tiny network

First, export the results of a Powershell script into a CSV file. Simply add the following command at the end of the script:

 |  Export-Csv "C:\DataTK\results.csv"

The R script will transform the results into Edges and Nodes. To make this script shorter, I have joined everything in nodes and added the group to set apart the users and workspaces.

library(tidygraph)
library(igraph)
library(dplyr)

file = "C:\\DataTK\\results.csv"
pbi <- read.csv(file, sep = ",", header = TRUE, skip=1)

# Data preparation
links <- pbi[,c("UserName","WorkspaceName")]
colnames(links) <- c("from","to")

# Nodes 
nodes1 <-  pbi %>% group_by(WorkspaceName) %>% summarise(n = n())
nodes1$group <- "WS"
colnames(nodes1) <- c("id","size", "group")
nodes2 <-  pbi %>% group_by(UserName) %>% summarise(n = n())
nodes2$group <- "US"
colnames(nodes2) <- c("id","size","group")
nodes <- rbind(nodes1, nodes2)

# create plot
net <- graph_from_data_frame(d=links, vertices=nodes, directed=TRUE) 
plot(net, edge.arrow.size=.5,vertex.label=V(net)$group)

Follow for more Powershell Scripts for Power BI on Github.

Happy scripting and stay healthy!