Skip to content
Contact us

Using Power Bi to report licenses

Lets start with the challenges:


It is very difficult to get reporting on licenses on Azure and use this information to make useful decision on purchase, renewal or True-up/True-Down

If you have multi companies this becomes even more complex to manage and time consuming to get a proper report/dashboard with license data. 

Over-licensing without cleanup causing users not able to access resources. However, this may also be caused by :

  • licenses not yet available by supplier
  • new licenses deployed on the Azure portal with limited quantities thus having over assigned licenses and a new license without any assignments

  • What is needed
  • The Script
    • Get list of users
    • Get licenses
    • Change license names to simpler names
    • Export to Storage Account table and blob
    • Get disabled users
    • Export to Storage Account blob

  • Connect Power BI to the storage table
  • Work with the data
  • Build report 
  • Automate report

Prequisites

  1. Ensure Certificate is not expired in Keyvault on Azure
  2.  RUN VS CODE AS ADMIN
  3. Install Graph module
  4. Install-Module Microsoft.Graph -AllowClobber -Force
  5. Install EXO module
  6. Install-Module -Name ExchangeOnlineManagement
  7. install-module importexcel
  8.  install-module aztable
  9. Ensure AZ.tables module installed
  10. Install Az.Storage Module
  11. install-module az.Storage -Force
Script

Connect-ExchangeOnline -CertificateThumbPrint "certificate thumprint added on application registration" -AppID "app id with permissions on exchange online" -Organization "tenantname.onmicrosoft.com"
 
##APP ID permissions. Note that you may not need all of these permissions however I can reuse this app for other ##queries/reporting

#DeviceManagementManagedDevices.Read.All             Application   Read Microsoft Intune devices      Admin consent:Yes
#DeviceManagementManagedDevices.ReadWrite.All Application   Read and write Microsoft Intune devices Admin #consent:Yes
#Directory.Read.All                                                                       Application  Read directory data                             Admin consent:Yes
#Policy.Read.All                                                                              Application  Read your organization's policies Admin consent:Yes
#User.Read                                                                                        Delegated      Sign in and read user profile        Admin Consent: No
# Office 365 Exchange Online (1)
# Exchange.ManageAsApp                                                         Application   Manage Exchange As Application Admin #consent:Yes 
 

 
$users=@()

#Get list of users
 
$users=get-user -ResultSize unlimited  | Select-Object company,userprincipalname,Displayname

 $userlicensereport =@()
 

 $params=@{
  UPN='1'
  Company='1'
  Dname='1'
  userassignmenttype='1'
  object='1'
  sku='1'
  license='1'
  Group='1'
 }

 #If there are errors with some commands, run the command below in a new powershell session and the connect-#exchangeonline at the start of the script
 
Connect-MgGraph -CertificateThumbPrint "Certificate Thumbprint" -ClientID "application ID" -TenantID "Tenant ID"
 

#Connect-MgGraph  -ClientID "application ID" -CertificateThumbPrint "Certificate Thumbprint" -TenantID #"Tenant ID" | Connect-MgGraph -Scopes Directory.Read.All
 
 #Install-Module Microsoft.Graph -AllowClobber -Force
 
 Import-Module Microsoft.Graph.Identity.DirectoryManagement
 Import-Module Microsoft.Graph.Users

 Import-Module Microsoft.Graph.Security


#Get license for users
#added new column license that will have the same values as Skupartnumber.
#The license column will be used to filter licenses needed in the report.
#And Skupartnumber will be a column in the excel report

foreach($username in $users){
  $UPN=$username.UserPrincipalName
  $Company=$username.Company
  $Dname=$username.DisplayName
   
#get licenses by user
 
$report=Get-MgUserLicenseDetail -UserId $UPN |  Select-Object  @{Name="UPN";Expression={ $UPN }} , @{Name="Company";Expression={ $Company }}, @{Name="DisplayName";Expression={ $Dname }}, SkuPartNumber ,@{Name="License";Expression='SkuPartNumber'}


$userlicensereport +=$report
}


#Replace some of the skupartnumber values with simple names for the report

Foreach ($item in $userlicensereport){

    if($item.SkuPartNumber -eq 'ENTERPRISEPACK'){$item.SkuPartNumber='E3'}
    if($item.license -eq 'ENTERPRISEPACK'){$item.license='E3'}
    if($item.SkuPartNumber -eq 'EMS'){$item.SkuPartNumber='M3'}
    if($item.license -eq 'EMS'){$item.license='M3'}
    if($item.SkuPartNumber -eq 'ATP_ENTERPRISE'){$item.SkuPartNumber='ATP'}
    if($item.license -eq 'ATP_ENTERPRISE'){$item.license='ATP'}
    if($item.SkuPartNumber -eq 'VISIOCLIENT'){$item.SkuPartNumber='VISIO_P2'}
    if($item.license -eq 'VISIOCLIENT'){$item.license='VISIO_P2'}
    if($item.SkuPartNumber -eq 'PROJECTPROFESSIONAL'){$item.SkuPartNumber='Project_P3'}
    if($item.license -eq 'PROJECTPROFESSIONAL'){$item.license='Project_P3'}
    if($item.SkuPartNumber -eq 'STANDARDPACK'){$item.SkuPartNumber='E1'}
    if($item.license -eq 'STANDARDPACK'){$item.license='E1'}
    if($item.SkuPartNumber -eq 'AAD_PREMIUM'){$item.SkuPartNumber='P1'}
    if($item.license -eq 'AAD_PREMIUM'){$item.license='P1'}
    if($item.SkuPartNumber -eq 'POWER_BI_PRO'){$item.SkuPartNumber='POWER_BI_PRO'}
    if($item.license -eq 'POWER_BI_PRO'){$item.license='POWER_BI_PRO'}
    if($item.SkuPartNumber -eq 'INTUNE_A_VL'){$item.SkuPartNumber='Intune'}
    if($item.license -eq 'INTUNE_A_VL'){$item.license='Intune'}
    if($item.SkuPartNumber -eq 'FLOW_FREE'){$item.SkuPartNumber='FLOW_FREE'}
    if($item.license -eq 'FLOW_FREE'){$item.license='FLOW_FREE'}
    if($item.SkuPartNumber -eq 'Microsoft_Teams_Exploratory_Dept'){$item.SkuPartNumber='Teams_Exploratory'}
    if($item.license -eq 'Microsoft_Teams_Exploratory_Dept'){ $item.license='Teams_Exploratory'}
    if($item.SkuPartNumber -eq 'SPE_E3'){$item.SkuPartNumber='E3_UPGRADE'}
    if($item.license -eq 'SPE_E3'){ $item.license='E3_UPGRADE'}
   
}
 
#get date month and year for the excel report name 
$day=(get-date).tostring('dd')
$year=(get-date).tostring('yyyy')
$Month=(get-date).tostring('MMM')
 

$sheetname="WanCount" + $Month + $year
 
#install-module importexcel
Import-Module ImportExcel
 
#Export a copy of the report

$userlicensereport | Export-Excel c:\temp\O365_"$Month"_"$year"_Licenses_v1.xlsx `
  -TableName O365_Licenses_"$Month"_"$year"  `
  -AutoSize `
  -AutoFilter `
  -IncludePivotTable `
  -PivotRows "Company", "UPN" `
  -PivotColumns "SkuPartNumber" `
  -PivotData @{license="count"} -PivotFilter License `
  -PivotTableName  Dashboard_"$Month"_"$year"
 
 # -WorksheetName Dashboard_"$Month"_"$year"

#Define variables and Export the results to an Azure storage table that will be used in powerbi.
#Note that Tables and blobs need to be created first
 
$StorageAccountName='Azure Storage name'
$tableName = 'UserLicensesReport'
$sasTokenBlob='SAS Token from Storage Account'

 
#install-module az.Storage -Force
import-module Az.Storage


$storageCtx = New-AzStorageContext -StorageAccountName $StorageAccountName -SasToken $sasTokenBlob
 
$table=(Get-AzStorageTable -Name $tableName -Context $storageCtx).CloudTable

#install-module aztable
 
#Clear table contents
Get-AzTableRow -table $table | Remove-AzTableRow -table $table

foreach ($row in $userlicensereport) {
  $UPN=$row.UPN
  $Company=$row.Company
  $Dname=$row.DisplayName
  $license=$row.SkuPartNumber
  $Sku=$row.SkuPartNumber

  if($Company-eq ""){$Company='NA'}
       
         
  Add-AzTableRow -table $table -partitionKey ((New-Guid).tostring()) -rowKey ([guid]::NewGuid().tostring()) -property @{"UPN"=$UPN;"DisplayName"=$Dname;"company"=$Company;"License"=$license;"SkuPartNumber"=$Sku}
 
}
 
#Export a copy of the results to the Storage Account BLOB
#Note that Tables and blobs need to be created first
 
$file= $day+$month+$year+"License.xlsx"
$blob=$userlicensereport | Export-Excel $file
Set-AzStorageBlobContent -Container license -File $file -BlobType Block -Context  $storageCtx -Blob $blob
#Set-AzStorageBlobContent -  $report -Container license -Context $storageCtx -Blob O365_"$Month"_"$year"_Licenses_v1.xlsx
 
 
#Export Get disabled users
#Note that Tables and blobs need to be created first

$Disabledusersfile= $day+$month+$year+"O365License_DISABLED_v1.xlsx"
$Disabledusers=Get-MgUser -Filter 'accountEnabled eq false' -All -property AccountEnabled, DisplayName | Select-Object DisplayName, AccountEnabled, userprincipalname

$DUsers= $Disabledusers | Export-Excel $Disabledusersfile
#$Disabledusers |Export-CSV c:\temp\disabledusers-$day.csv
Set-AzStorageBlobContent -Container license -File $Disabledusersfile -BlobType Block -Context  $storageCtx -Blob $DUsers -Force