With the release of a new auditing event for Power BI, you can use PowerShell to automate Power BI Pro license assignments.
Auditing within Power BI
Auditing with Power BI has been available for a few months within the US and more recently within all regions except Europe and Australia as a preview. This provides auditing on certain events to understand what your organization is doing with the service. You can look at the documentation for a full list of activities audited by Power BI.
The new activity that has been added is when a free user signs up for the 60 day Power BI Pro Trial within the Power BI service. This activity is called OptInForProTrial.
Using PowerShell to retrieve Power BI Audit Logs
Within the documentation, we list out how you can use PowerShell to search the audit logs for Power BI activities. This will retrieve all activities for Power BI. You can further filter the list down by a specific activity. The parameter is called Operations. If we set Operations to OptInForProTrial, we will only see the entries for Power BI Trials.
Search-UnifiedAuditLog -StartDate 1/1/2017 -EndDate 1/6/2017 -RecordType PowerBI -Operations OptInForProTrial -ResultSize 1000 | Format-Table | More
Assigning Power BI Pro licenses
We can also use PowerShell to assign licenses to users. This is done with the Set-MsolUserLicense cmdlet which is part of the Azure AD PowerShell Module. There is also a good document that walks through how to assign licenses to user accounts with Office 365 PowerShell (AAD PowerShell).
The Power BI Pro license is represented by the following SKU: POWER_BI_PRO. The actual AccountSkuId will be <tenant>:POWER_BI_PRO. You can also get a listing of all of the license types available for your tenant by running Get-MsolAccountSku.
An example of assigning a user a Power BI Pro license would be something similar to the following. This assumes that there are licenses available and the user exists within your organization.
Set-MsolUserLicense -UserPrincipalName "user@guyinacube.com" -AddLicenses "guyinacube:POWER_BI_PRO"
Requirements
There are a few requirements that need to be met in order to run the script below.
#1 – Have access to the audit log
You need to have access to the audit log in order to retrieve the entries. For more information about how to access the audit log, see Auditing Power BI in your organization. You can also check out a video I made where I talk about how you can assign auditing rights to non-global admin users called Power BI Auditing for a Non-Admin.
#2 – Be an admin that can assign licenses
The whole point of the script is that we will be assign licenses to users within your O365 tenant. You need to have admin rights to assign the license.
#3 – Install the Azure AD PowerShell Cmdlets
The commands used to assign the license are part of the Azure AD cmdlets. You can get the download link from the MSOnline Module documentation.
Automating license assignment based on audit log entries
Now let’s put this all together and look at a script that will add a Power BI Pro license to each user that signed up for the 60 day Power BI Pro Trial within the Power BI service.
First we need to provide credentials to authenticate against the end points for Office 365/Exchange Online. I have two options below that we can use.
# Option 1 – This can be used to be prompted for credentials
$UserCredential = Get-Credential
Connect-MsolService -credential $UserCredential# Option 2 – If you really want to automate the script, you will
# want to hard code the credentials to log into Azure AD.
$User = "<ADMIN USER>"
$PWord = ConvertTo-SecureString -String "<PASSWORD>" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
Connect-MsolService -credential $UserCredential
Option 1
This option will prompt you for your credentials. You can type these in and it will proceed with the script. This is great for a one off run of the script.
Option 2
If you want to automate the script and not have to deal with the prompt for credentials, you will want to use option 2. Realize that you are storing your credential information in plain text with this approach.
Note about multi-Factor authentication
If you have enabled multi-factor authentication for your account, option 2 will not work as it is written above. Two-factor authentication is not supported with PowerShell for Office 365. You will need to use an administrator account that does not have two-factor authentication enabled.
We then need to connect to Exchange Online to retrieve audit log entries.
# Create the session to Exchange Online
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection# Import the Exchange Online commands
Import-PSSession $Session
For this example, I’ve created a search that will look for the last 24 hours. If you were automating this, and running it every day, this may be helpful for you. Otherwise, you can just specify the start and end date directly in the search command if you are spanning a given date range.
We want to stuff the results of the search into a variable that we will use later.
# Setup our start and end dates to pull back events
$start = Get-Date
$end = Get-Date# Perform the Audit search looking for Power BI Pro Trials.
# This is for a given date range. Useful for automation.
$PowerBIAudits = Search-UnifiedAuditLog -StartDate $start.ToShortDateString() -EndDate $end.AddDays(1).ToShortDateString() -RecordType PowerBI -Operations OptInForProTrial -ResultSize 1000
We then need to look up the tenant name for use when we are assigning the actual Power BI Pro license.
# Get the Tenant name so we can automate the license assignment.
$MSOLAccountSKU = Get-MsolAccountSku | Where-Object {$_.AccountSkuID -like '*:POWER_BI_PRO'}
$TenantName = $MSOLAccountSKU.AccountName
Now we have everything we need to loop through the audit events and validate the license on the given user. If the Power BI Pro license does not exist on that user, we will assign it. This assumes that you have Power BI Pro licenses available to assign within your tenant.
# Go through each audit entry and assign a license
ForEach($audit in $PowerBiAudits)
{
$audituser = $audit.UserIds
Write-Host $audituser -foregroundcolor cyan# Get licenses already assigned to that users.
# Checking to see if they already have a Power BI Pro license assigned.
$Licenses = Get-MsolUser -UserPrincipalName $audituser | Select-Object -ExpandProperty Licenses | Where-Object {$_.AccountSkuID -eq $TenantName + ':POWER_BI_PRO'}# We only want to assign a license if they
# don't have one assigned.
If ($Licenses.Count -lt 1)
{
Set-MsolUserLicense -UserPrincipalName $audituser -AddLicenses $TenantName":POWER_BI_PRO"
Write-Host "License added." -foregroundcolor cyan
}
Else {Write-Host "License already exists." -foregroundcolor cyan}
}
Full Script
Note: Be sure to pick one of the authentication options and remote the other. They should not both be present in the script!
# Option 1 – This can be used to be prompted for credentials
$UserCredential = Get-Credential
Connect-MsolService -credential $UserCredential
# Option 2 – If you really want to automate the script, you will
# want to hard code the credentials to log into Azure AD.
$User = "<ADMIN USER>"
$PWord = ConvertTo-SecureString -String "<PASSWORD>" -AsPlainText -Force
$UserCredential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $User, $PWord
Connect-MsolService -credential $UserCredential
# Create the session to Exchange Online
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
# Import the Exchange Online commands
Import-PSSession $Session
# Setup our start and end dates to pull back events
$start = Get-Date
$end = Get-Date
# Perform the Audit search looking for Power BI Pro Trials.
# This is for a given date range. Useful for automation.
$PowerBIAudits = Search-UnifiedAuditLog -StartDate $start.ToShortDateString() -EndDate $end.AddDays(1).ToShortDateString() -RecordType PowerBI -Operations OptInForProTrial -ResultSize 1000
# Get the Tenant name so we can automate the license assignment.
$MSOLAccountSKU = Get-MsolAccountSku | Where-Object {$_.AccountSkuID -like '*:POWER_BI_PRO'}
$TenantName = $MSOLAccountSKU.AccountName
# Go through each audit entry and assign a license
ForEach($audit in $PowerBiAudits)
{
$audituser = $audit.UserIds
Write-Host $audituser -foregroundcolor cyan
# Get licenses already assigned to that users.
# Checking to see if they already have a Power BI Pro license assigned.
$Licenses = Get-MsolUser -UserPrincipalName $audituser | Select-Object -ExpandProperty Licenses | Where-Object {$_.AccountSkuID -eq $TenantName + ':POWER_BI_PRO'}
# We only want to assign a license if they
# don't have one assigned.
If ($Licenses.Count -lt 1)
{
Set-MsolUserLicense -UserPrincipalName $audituser -AddLicenses $TenantName":POWER_BI_PRO"
Write-Host "License added." -foregroundcolor cyan
}
Else {Write-Host "License already exists." -foregroundcolor cyan}
}
Other uses
Get-MsolGroupMember
If you didn’t want to use this with the audit log, you could also use PowerShell based on group membership of an Azure AD group itself. You could use the Azure AD PowerShell cmdlets to get a list of members from a group and then loop through those to verify if those users have a Power BI Pro license assigned to them. This could be useful if you have a group for your business analysts that you want to make sure they get a Pro license. Or, just general users within your organization. For Azure AD, you can use the Get-MsolGroupMember command.
Get-UnifiedGroupLinks
You could also use the O365 PowerShell cmdlets to get members of a universal group and do the same. There are a lot of different users to automate Power BI Pro license assignment within your organization using PowerShell. This can be done by way of Get-UnifiedGroupLinks. Get-UnifiedGroupLinks is part of the Exchange Online command set and not the Azure AD cmdlets. You will need to run the New-PSSession and Import commands above in order to use this command.
Note: I found that the Get-UnifiedGroupLinks only returned the members that had an Exchange Online license assigned to them. You could alternatively use Get-MsolGroupMember on an O365 Unified Group to get all of the members regardless of license assignment.
Give this a whirl and let me know your thoughts in the comments.