Get Power BI users and licenses from Azure through PowerShell script

For those who want to have more control over the management of Power BI licenses to help with Data Governance, managing the allocation of Power BI licenses (Free and Pro) is a fundamental task for administrators. Therefore, through PowerShell scripts, we were able to obtain AzureAD data and know who the users are, which licenses are active in Azure, and who owns the licenses.

With that, we managed to get some insights:

• Who has a Power BI Free or Pro license?
• How many licenses are active per department? And what are the types of licenses?
• What is the capacity of licenses in the organization?
• From that capacity, how many are active and how many are available to be activated?
• What percentage of active and available licenses?
• What is the value of Pro licenses in total?

For these scripts I used Brett Powell’s content from Insights Quest, separating the scripts into PowerShell files to add and adding the Power BI Free Service Plan ID, in addition to not using a separate file to connect to Azure.

The first step is to install Azure modules on PowerShell:

Install-Module -Name AzureRM -Force
Install-Module -Name AzureAD -Force

After that, you can run the first script to get which licenses are active in your organization’s Azure.

# Reference taken from the official link https://docs.microsoft.com/en-us/powershell/module/azuread/?view=azureadps-2.0
# To work, it is necessary to install some modules, link: https://docs.microsoft.com/pt-br/powershell/azure/azurerm/install-azurerm-ps?view=azurermps-6.13.0

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Imports Azure modules
Import-Module AzureRM
Import-Module AzureAD

# Dados da conta e do Azure para poder acessar
$PBIAdminUPN = “youremail@email.com.br”
$PBIAdminPW = “yourpassword”
$MyOrgTenantID = “your tenant”
$MyOrgBIAppID = “your ID from your Azure app”
$MyOrgBIThumbprint = “thumbprint id”

# Run the credential according to the login, password and tenant ID above
$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)

Connect-AzureAD -TenantId “your Azure tenant” -Credential $myCred

# Defines the directory and name of the file to be exported to the CSV file
$RetrieveDate = Get-Date 
$BasePath = "D:\Azure\"
$OrgO365LicensesCSV = $BasePath + "BI_AzureLicenses.csv"

#https://docs.microsoft.com/en-us/office365/enterprise/powershell/view-licenses-and-services-with-office-365-powershell
$OrgO365Licenses = Get-AzureADSubscribedSku | Select-Object SkuID, SkuPartNumber,CapabilityStatus, ConsumedUnits -ExpandProperty PrepaidUnits | `
    Select-Object SkuID,SkuPartNumber,CapabilityStatus,ConsumedUnits,Enabled,Suspended,Warning, @{Name="Retrieve Date";Expression={$RetrieveDate}} 

$OrgO365Licenses | Export-Csv $OrgO365LicensesCSV -force -notypeinformation -Encoding UTF8

Then we can run the script to get users who have Free and Pro licenses for Power BI. Therefore, to obtain data for Free or Pro licenses, it is only necessary to change the Service Plan ID, according to the link available in the script.

Free:

# Reference taken from the official link https://docs.microsoft.com/en-us/powershell/module/azuread/?view=azureadps-2.0
# To work, it is necessary to install some modules, link: https://docs.microsoft.com/pt-br/powershell/azure/azurerm/install-azurerm-ps?view=azurermps-6.13.0

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Imports Azure modules
Import-Module AzureRM
Import-Module AzureAD

# Email and Azure account data to be able to connect
$PBIAdminUPN = “youremail@email.com.br”
$PBIAdminPW = “yourpassword”
$MyOrgTenantID = “your Azure tenant”
$MyOrgBIAppID = “your ID from your Azure app”
$MyOrgBIThumbprint = “thumbprint id”

# Run the credential according to the login, password and tenant ID above
$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)

Connect-AzureAD -TenantId “your Azure tenant” -Credential $myCred

# Defines the directory and name of the file to be exported to the CSV file
$RetrieveDate = Get-Date 
$BasePath = "D:\Azure\"
$UserPBIProLicensesCSV = $BasePath + "PBIFree.csv"

# Get license users
$ADUsers = Get-AzureADUser -All $true | Select-Object ObjectId, ObjectType, CompanyName, Department, DisplayName, JobTitle, Mail, Mobile, `
            SipProxyAddress, TelephoneNumber, UserPrincipalName, UserType, @{Name="Date Retrieved";Expression={$RetrieveDate}}

<#
See MS Licensing Service Plan reference: 
https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference
#>
# Service Plan ID for Power BI FREE licenses
$PBIFreeServicePlanID = "2049e525-b859-401b-b2a0-e0a31c4b1fe4"

# Retrieve and export users with professional licenses based on the Power BI Free Service Plan ID ($PBIFreeServicePlanID)
# Each line represents a service plan for a specific user. This license detail is filtered only for the Power BI FREE service plan ID.
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        $UserDept = $ADUser.Department
        $AccountEnabled = $ADUser.AccountEnabled
        $AssignedTimestamp = $ADUser.AssignedTimestamp
        $CapabilityStatus = $ADUser.CapabilityStatus
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, SkuPartNumber, @{Name="UserName";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN}}, `
        @{Name="Department";Expression={$UserDept}},@{Name="RetrieveDate";Expression={$RetrieveDate}} -ExpandProperty ServicePlans
    }

$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIFreeServicePlanID}

#Get-AzureADUserLicenseDetail -ObjectId $UserObjectID | Get-Member
#Get-AzureADSubscribedSku | Get-Member
#Get-AzureADUser | Get-Member

$ProUsers | Export-Csv $UserPBIProLicensesCSV -force -notypeinformation -Encoding UTF8

Pro:

# Reference taken from the official link https://docs.microsoft.com/en-us/powershell/module/azuread/?view=azureadps-2.0
# To work, it is necessary to install some modules, link: https://docs.microsoft.com/pt-br/powershell/azure/azurerm/install-azurerm-ps?view=azurermps-6.13.0

# To execute the script without agreeing with the execution policy
Set-ExecutionPolicy Bypass -Scope Process

# Imports Azure modules
Import-Module AzureRM
Import-Module AzureAD

# Email and Azure account data to be able to connect
$PBIAdminUPN = “youremail@email.com.br”
$PBIAdminPW = “yourpassword”
$MyOrgTenantID = “your Azure tenant”
$MyOrgBIAppID = “your ID from your Azure app”
$MyOrgBIThumbprint = “thumbprint id”

# Run the credential according to the login, password and tenant ID above
$SecPasswd = ConvertTo-SecureString $PBIAdminPW -AsPlainText -Force

$myCred = New-Object System.Management.Automation.PSCredential($PBIAdminUPN,$SecPasswd)

Connect-AzureAD -TenantId “your Azure tenant” -Credential $myCred

# Defines the directory and name of the file to be exported to the CSV file
$RetrieveDate = Get-Date 
$BasePath = "D:\Azure\"
$UserPBIProLicensesCSV = $BasePath + "PBIFree.csv"

# Get license users
$ADUsers = Get-AzureADUser -All $true | Select-Object ObjectId, ObjectType, CompanyName, Department, DisplayName, JobTitle, Mail, Mobile, `
            SipProxyAddress, TelephoneNumber, UserPrincipalName, UserType, @{Name="Date Retrieved";Expression={$RetrieveDate}}

<#
See MS Licensing Service Plan reference: 
https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference
#>
# Service Plan ID for Power BI PRO licenses
$PBIFreeServicePlanID = "70d33638-9c74-4d01-bfd3-562de28bd4ba"

# Retrieve and export users with professional licenses based on the Power BI Free Service Plan ID ($PBIFreeServicePlanID)
# Each line represents a service plan for a specific user. This license detail is filtered only for the Power BI FREE service plan ID.
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        $UserDept = $ADUser.Department
        $AccountEnabled = $ADUser.AccountEnabled
        $AssignedTimestamp = $ADUser.AssignedTimestamp
        $CapabilityStatus = $ADUser.CapabilityStatus
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, SkuPartNumber, @{Name="UserName";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN}}, `
        @{Name="Department";Expression={$UserDept}},@{Name="RetrieveDate";Expression={$RetrieveDate}} -ExpandProperty ServicePlans
    }

$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIFreeServicePlanID}

#Get-AzureADUserLicenseDetail -ObjectId $UserObjectID | Get-Member
#Get-AzureADSubscribedSku | Get-Member
#Get-AzureADUser | Get-Member

$ProUsers | Export-Csv $UserPBIProLicensesCSV -force -notypeinformation -Encoding UTF8

Finally, we can work on the data that was exported in CSV files in a data warehouse, to obtain the necessary data from the insights made at the beginning of this post. To develop the architecture of this model I used Oracle Data Modeler, did the physical and logical modeling of the necessary tables, imported into Analysis Services Multidimensional (OLAP), and connected to Power BI.

These were some of the results I got:

Resume of Power BI Licenses

Licenses Report

This is it! Now we can have a better administration of the active licenses in the company, and with that we can have a better control of what can be governed and made available to users.

If you have any questions, feel free to leave any comments or questions.

Guilherme Matheus - Business Intelligence Analyst - Vivest | LinkedIn

Also the scripts can be found on my GitHub repository:

guimatheus92/Obtain-Power-BI-users-and-licenses-from-Azure-through-PowerShell (github.com)