Retrieving list of users for all workspaces in your PowerBI tenant using Powershell
This article is originally published at https://tomaztsql.wordpress.com
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 = "[email protected]"
$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!
Thanks for visiting r-craft.org
This article is originally published at https://tomaztsql.wordpress.com
Please visit source website for post related comments.
