Export users in Power BI Workspaces with Python and REST API

In this blog let's take a look at how you can use Python to export users across all workspaces in Power BI Service into an Excel file.

First you need to install some libraries:

import msal
import requests
import pandas as pd
import itertools

# pip install msrest, mstrestazure
# pip install azure.graphrbac
# pip install azure.common

from azure.common.credentials import ServicePrincipalCredentials
from azure.graphrbac import GraphRbacManagementClient

Next you need to register an application in Azure using https://app.powerbi.com/embedsetup

Once done take a note of application ID and application secret, keep it noted somewhere otherwise you will have to create a new one in Azure and won't get back the current one.

You also need to make sure that you add the application (Service Principal) either individually into all workspaces or add that application to a security group which is already part of workspaces. Otherwise you will obtain the access token but won't return anything from the workspaces.

Next declare 3 variables:

app_id = '*********************************************'
pbi_tenant_id = '*********************************************'
app_secret = '*********************************************'

Now we need to create a function that generates the access token with the help of the application we registered.

def get_access_token():
    
    authority_url = f'https://login.microsoftonline.com/{pbi_tenant_id}'
    scopes = [r'https://analysis.windows.net/powerbi/api/.default']

    client = msal.ConfidentialClientApplication(
        app_id, 
        authority=authority_url, 
        client_credential=app_secret
    )
    
    response = client.acquire_token_for_client(scopes)
    token = response.get('access_token')
    
    return token


token = get_access_token()
headers = {
    'Content-Type': 'application/json',
    'Authorization': f'Bearer {token}'
}

Next we first get list of Workspaces using this function:

def get_workspaces():
    
    # workspace_endpoint = 'https://api.powerbi.com/v1.0/myorg/groups?$filter=(isOnDedicatedCapacity eq true)'
    
    workspaces = 'https://api.powerbi.com/v1.0/myorg/groups'
    response_request = requests.get(workspaces, headers=headers)
    result = response_request.json()
    
    workspace_id = [workspace['id'] for workspace in result['value']]
    workspace_name = [workspace['name'] for workspace in result['value']]
    
    return zip(workspace_id, workspace_name)

Now we need to iterate each workspace and get the users but there can be entries that are security groups, so we want to list out memberships as well and for that first we need to identify how to get a string of members using this function:

def get_group_members(group_id):
    
    # Code taken from https://stackoverflow.com/questions/51859504/how-to-access-the-azure-ad-groups-and-user-details-using-python
    
    credentials = ServicePrincipalCredentials(
        client_id = app_id,
        secret = app_secret,
        resource = "https://graph.windows.net",
        tenant = '16ky80.onmicrosoft.com' # Change 16ky80 to your tenant
    )

    graphrbac_client = GraphRbacManagementClient(
        credentials,
        pbi_tenant_id
    )
    
    users = graphrbac_client.groups.get_group_members(group_id)
    
    result = ''
    
    for u in users:
        if u.object_type == 'User':
            result += u.mail  + '\n'
        elif u.object_type == 'ServicePrincipal':
             result +=  'Service Principal - ' + u.display_name + '\n'

    return result[:-1]

The above function will be called by another function row by row so that we can return a string on members.

Next we list out users in each workspace:

def get_workspace_users():
    
    workspace_ids, workspace_names = zip(*list(get_workspaces()))
    user_details = []

    for workspace_id in workspace_ids:
        users = fr"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/users"
        response = requests.get(users, headers = headers)
        response_text = response.json()['value']
        
        for entry in response_text:
            entry['WorkspaceID'] = workspace_id
            if entry['principalType'] == 'Group':
                entry['Group Membership'] = get_group_members(entry['identifier'])
        
        user_details.append(response_text)
        
    return list(itertools.chain(*user_details))

After this all we need to do is store the result of above functions in a dataframe and perform some cleanup operations.

workspace_df = pd.DataFrame.from_records(get_workspaces(), columns = ['WorkspaceID', 'WorkspaceName'])
workspace_user_df = pd.DataFrame(get_workspace_users())

final_df = pd.merge(workspace_df, workspace_user_df, how = 'left', on = 'WorkspaceID' )
final_df.rename(
    columns = {
        'WorkspaceID': 'Workspace ID',
        'WorkspaceName': 'Workspace Name',
        'groupUserAccessRight': 'User Access',
        'displayName': 'User Name',
        'identifier': 'User ID',
        'principalType': 'User Type',
        'emailAddress': 'Email ID'
    },
    inplace = True
)

And now we can export the dataframe to an Excel file:

final_df.to_excel(r"C:\Users\antsharma\OneDrive\Desktop\User Details.xlsx", index = False)

The end result will look like this, with the Group Membership column wrapped.

Complete code:

import msal
import requests
import pandas as pd
import itertools

# pip install msrest, mstrestazure
# pip install azure.graphrbac
# pip install azure.common

from azure.common.credentials import ServicePrincipalCredentials
from azure.graphrbac import GraphRbacManagementClient

app_id = '*********************************************'
pbi_tenant_id = '*********************************************'
app_secret = '*********************************************'


def get_access_token():
    
    authority_url = f'https://login.microsoftonline.com/{pbi_tenant_id}'
    scopes = [r'https://analysis.windows.net/powerbi/api/.default']

    client = msal.ConfidentialClientApplication(
        app_id, 
        authority=authority_url, 
        client_credential=app_secret
    )
    
    response = client.acquire_token_for_client(scopes)
    token = response.get('access_token')
    
    return token


token = get_access_token()
headers = {
    'Content-Type': 'application/json',
    'Authorization': f'Bearer {token}'
}


def get_workspaces():
    
    # workspace_endpoint = 'https://api.powerbi.com/v1.0/myorg/groups?$filter=(isOnDedicatedCapacity eq true)'
    
    workspaces = 'https://api.powerbi.com/v1.0/myorg/groups'
    response_request = requests.get(workspaces, headers=headers)
    result = response_request.json()
    
    workspace_id = [workspace['id'] for workspace in result['value']]
    workspace_name = [workspace['name'] for workspace in result['value']]
    
    return zip(workspace_id, workspace_name)


def get_group_members(group_id):
    
    # Code taken from https://stackoverflow.com/questions/51859504/how-to-access-the-azure-ad-groups-and-user-details-using-python
    
    credentials = ServicePrincipalCredentials(
        client_id = app_id,
        secret = app_secret,
        resource = "https://graph.windows.net",
        tenant = '16ky80.onmicrosoft.com' # Change 16ky80 to your tenant
    )

    graphrbac_client = GraphRbacManagementClient(
        credentials,
        pbi_tenant_id
    )
    
    users = graphrbac_client.groups.get_group_members(group_id)
    
    result = ''
    
    for u in users:
        if u.object_type == 'User':
            result += u.mail  + '\n'
        elif u.object_type == 'ServicePrincipal':
             result +=  'Service Principal - ' + u.display_name + '\n'

    return result[:-1]


def get_workspace_users():
    
    workspace_ids, workspace_names = zip(*list(get_workspaces()))
    user_details = []

    for workspace_id in workspace_ids:
        users = fr"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/users"
        response = requests.get(users, headers = headers)
        response_text = response.json()['value']
        
        for entry in response_text:
            entry['WorkspaceID'] = workspace_id
            if entry['principalType'] == 'Group':
                entry['Group Membership'] = get_group_members(entry['identifier'])
        
        user_details.append(response_text)
        
    return list(itertools.chain(*user_details))


workspace_df = pd.DataFrame.from_records(get_workspaces(), columns = ['WorkspaceID', 'WorkspaceName'])
workspace_user_df = pd.DataFrame(get_workspace_users())

final_df = pd.merge(workspace_df, workspace_user_df, how = 'left', on = 'WorkspaceID' )
final_df.rename(
    columns = {
        'WorkspaceID': 'Workspace ID',
        'WorkspaceName': 'Workspace Name',
        'groupUserAccessRight': 'User Access',
        'displayName': 'User Name',
        'identifier': 'User ID',
        'principalType': 'User Type',
        'emailAddress': 'Email ID'
    },
    inplace = True
)
final_df.to_excel(r"C:\Users\antsharma\OneDrive\Desktop\User Details.xlsx", index = False)