Which user does really need a Power BI Pro license?

In a Power BI Premium license environment, only the Report creators have a Power BI Pro license, because they create Reports, Dashboards and so on. The other users, who only consume the data, are assigned to a Free license. In the past, I saw so many organization’s where users have a Pro license, because they need a in the past, but now they do not do anything with it. For an administrator it can be horrible to find out, which user really need a license, because, when he does not use the activity logs, he must ask every user which has one assigned. In a previous Blog Post, I have explained how you can get the Activities of the last 90 days into CSV files. If you run this automatically and over a long term of time, you get a good time series (https://www.flip-design.de/?p=916). With this data you can easily create a filter, to check which user creates reports or so on:

 Now you need the information which user has a Pro license assigned. To get this information, you can request your Active Directory, get this data into a CSV file, and merge it with this data. The following script exports every user into the defined file who has a license.

$myPassword = '<password>'
$myUsername = 'philipp@plenz.onmicrosoft.com'

$password = ConvertTo-SecureString $myPassword -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($myUsername, $password)
Connect-AzureAD -Credential $credential

$RetrieveDate = Get-Date 
$BasePath = "C:\Users\plenz\OneDrive\Power BI WS"
$AzureADUsersCSV = $BasePath + "Users.csv"
$OrgO365LicensesCSV = $BasePath + "OrgO365Licenses.csv"
$UserPBIProLicensesCSV = $BasePath + "UserPBIProLicenses.csv"

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


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

$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}} 
   
$UserLicenseDetail = ForEach ($ADUser in $ADUsers)
    {
        $UserObjectID = $ADUser.ObjectId
        $UPN = $ADUser.UserPrincipalName
        $UserName = $ADUser.DisplayName
        $UserDept = $ADUser.Department
        Get-AzureADUserLicenseDetail -ObjectId $UserObjectID -ErrorAction SilentlyContinue | `
        Select-Object ObjectID, @{Name="User Name";Expression={$UserName}},@{Name="UserPrincipalName";Expression={$UPN}}, `
        @{Name="Department";Expression={$UserDept}},@{Name="Retrieve Date";Expression={$RetrieveDate}} -ExpandProperty ServicePlans
    }
    $ProUsers
$ProUsers = $UserLicenseDetail | Where-Object {$_.ServicePlanId -eq $PBIProServicePlanID}

$ProUsers | Export-Csv $UserPBIProLicensesCSV

After that, you can create a relationship between both tables and you can check, which user really needs the Pro license, or you can assign it to another user.