Office 365 License Details with PowerShell – With Domain, License (SKU id) or Product, and Service Status Filters


By: Anshuman Mansingh, Technology Specialist, Microsoft Corporation, https://www.linkedin.com/in/anshumanmansingh/

I have often come across this requirement where I am asked for a custom report that returns the following.

  1. A division of licenses based on domains
  2. Types of licenses assigned

Fortunately, we have Microsoft Excel that can do much of the filtering – if we can export a list of all the details required above. And so, I wrote two PowerShell scripts.

Please find them at the end of this article.

Note: This script has been designed for information retrieval and does not change anything on Office 365. Also, it is not covered under Microsoft support – please treat this as a sample.

Outputs

Script – Version 1

Script Version 1 Output:

This script will find the desktop and post a comma-delimited CSV file. This file can be opened in the notepad. However, to make better sense, you can open it in MS Excel and then press “Ctrl + T” (and select “My table has headers”), to format the output as a table.


Once converted to a table, you can click on the small drop-down (down-arrow) buttons next to each column-head to filter the table as you need.

References on dealing with CSV data in Excel

  1. Formatting as a table

Columns in the output CSV:

  1. Display Name
  2. User Principal Name
  3. Each SKU or License-type (in the tenant) has a column in the CSV

Below is a sample.


Script Version 1 Time Taken:

This script returns value of the time taken. Below are some test numbers on time taken to connect to office 365, retrieve data, analyze and export to a csv file.

8000 users – 2 minutes 13 seconds

20000 users – 6 minutes 28 seconds


This looks like an acceptable time investment considering the once-in-a-month-type nature of the report.

However, the time taken increases with increasing user base and slower connection and is dependent on the client system configuration.

Script – Version 2

Script Version 2 Output:

This script will find the desktop and post multiple comma-delimited CSV files.

(You can format and filter these CSVs as tables – as explained above in output description of script-version-1.)

Columns in the first CSV:

  1. Display Name
  2. User Principal Name
  3. Each SKU or License-type (in the tenant) has a column in the CSV

Columns in the second CSV:

  1. Display Name
  2. User Principal Name
  3. Each Office 365 Service (in the tenant) has a column in the CSV

Below is a sample from the second CSV


Script Version 2 Time Taken:

The script returns the value of time taken.

8000 users – 2 minutes 31 seconds

20000 users – 6 minutes 48 seconds

    

Steps to Run

Step 1:

Only for the first run: Prepare PowerShell to run Office 365 related scripts.

  • Open Windows PowerShell as an administrator
  • Run the command: Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
  • Confirm “Yes” or “Yes to All” when prompted.
  • Close PowerShell Window.

Step 2:

Only for the first run: Download and install the necessary PowerShell modules.

Step 3:

Only for the first run:
Ready the script

  • Copy the script on to notepad.
  • Save the file as a “.ps1” file on the desktop.

Step 4:

Running the script – There are two options.

  1. Action Item: Starting the script

    Option 1: Right click on the .ps1 file and click on “Run with PowerShell”

    Option 2: Open Windows PowerShell > Type in the path of the file at the prompt – (e.g. “c:\users\anshuman\script.ps1”) > hit Enter key.

  2. Action Item: The script will prompt for credentials. Please enter your Office 365 Global Administrator credentials.
  3. Wait & Watch: The script will then keep you waiting as it starts to connect and retrieve users.


  4. Wait & Watch: It will then start reading into each user’s licensing information. And while it does that, you will see a progress bar.


  5. Wait & Watch: Once, the processing is over, this script is going to write the licensing information on to a CSV file – and post it on your desktop.

SCRIPTS

#Version 1


#Editables
$NameOfOutputFile_LicenseDetails = "LicenseStats.csv"
$NameOfOutputFile_Errors = "Errors.csv"
$PathOfOutputFiles = [Environment]::GetFolderPath("Desktop")
#ErrorsRefreshed
$Error.Clear()
#Prompts for Office 365 Administrator Credential
if(!$cred){$cred = Get-Credential -Message "Office 365 Global Administrator Credentials"}
$TimeFlag1 = (Get-Date) #First Time Flag
#Initiates Remote PowerShell connection
Write-Progress -Activity "Connecting Office 365" -Id 1
Import-Module MSOnline; Connect-MsolService -Credential $cred
#Start of Script
Write-Progress -Activity "Retrieving User Information" -Id 1
$AllUsers = Get-MsolUser -All | select DisplayName, UserPrincipalName, Licenses
$AccountSkuId = ($AllUsers.Licenses.AccountSkuId | group).Name
$paras = @(); $paras += "DisplayName"; $paras += "UserPrincipalName"; $paras += "Domain"; $paras += $AccountSkuId
$List = @(); $i = 1; $AllCount = ($AllUsers).Count
$DesktopPath = $PathOfOutputFiles
#Start of User Loop
Write-Progress -Activity "Working on Retrieved Data" -Id 1
$AllUsers | foreach{
$cUser = $_
$cList = "" | select $paras
$cList.DisplayName = $cUser.DisplayName
$cList.UserPrincipalName = $cUser.UserPrincipalName
$cList.Domain = (($cUser.UserPrincipalName).split("@")[1])
$cUser.Licenses.AccountSkuid | foreach{
if($_){$cList."$_" = "Yes"}
}
$List += $cList; $i+=1; if($i -le $AllCount){Write-Progress -Activity "Analysing per-User Licensing Infomation" -PercentComplete (($i/$AllCount)*100) -CurrentOperation "$i of $AllCount" -ParentId 1}
Clear-Variable cUser;
}
#End of User Loop
$List | Export-Csv ($DesktopPath + "\" + $NameOfOutputFile_LicenseDetails) -NoTypeInformation
$Error | Export-Csv ($DesktopPath + "\" + $NameOfOutputFile_Errors) -NoTypeInformation
$TimeFlag2 = (Get-Date); Write-Host "Time Taken: " ($TimeFlag2 - $TimeFlag1) " for " $AllCount " users."
$host.EnterNestedPrompt();#$host.ExitNestedPrompt()
#End of Script



#Version 2


#Editables
$NameOfOutputFile_LicenseDetails = "LicenseStats.csv"
$NameOfOutputFile_ServiceStatus = "ServiceStatus.csv"
$NameOfOutputFile_Errors = "Errors.csv"
$PathOfOutputFiles = [Environment]::GetFolderPath("Desktop")
#ErrorsRefreshed
$Error.Clear()
#Prompts for Office 365 Administrator Credential
if(!$cred){$cred = Get-Credential}
$TimeFlag1 = (Get-Date) #First Time Flag
#Initiates Remote PowerShell connection
Write-Progress -Activity "Connecting Office 365" -Id 1
Import-Module MSOnline; Connect-MsolService -Credential $cred
#Start of Script
Write-Progress -Activity "Retrieving User Information" -Id 1
$AllUsers = Get-MsolUser -All | select DisplayName, UserPrincipalName, Licenses
#ColumnsStart
$AccountSkuId = ($AllUsers.Licenses.AccountSkuId | group).Name
$paras = @(); $paras += "DisplayName"; $paras += "UserPrincipalName"; $paras += "Domain"; $paras += $AccountSkuId
$ServicePlans = ($AllUsers.licenses.servicestatus.serviceplan.ServiceName | group).Name
$paras2 = @(); $paras2 += "DisplayName"; $paras2 += "UserPrincipalName"; $paras2 += "Domain"; $paras2 += $ServicePlans
#ColumnsEnd
$List = @(); $List2 = @(); $i = 1; $AllCount = ($AllUsers).Count
$DesktopPath = $PathOfOutputFiles
#Start of User Loop
Write-Progress -Activity "Working on Retrieved Data" -Id 1
$AllUsers | foreach{
$cUser = $_
$cList = "" | select $paras; $cList2 = "" | select $paras2;
$cList.DisplayName = $cUser.DisplayName; $cList2.DisplayName = $cUser.DisplayName;
$cList.UserPrincipalName = $cUser.UserPrincipalName; $cList2.UserPrincipalName = $cUser.UserPrincipalName;
$cList.Domain = (($cUser.UserPrincipalName).split("@")[1]); $cList2.Domain = $cList.Domain;
$cUser.Licenses.AccountSkuid | foreach{
if($_){$cList."$_" = "Yes"}
}
$cUser.licenses.servicestatus | foreach{
if($_){$cList2name = $_.serviceplan.ServiceName; $cList2stat = $_.ProvisioningStatus; $cList2.$cList2name = $cList2stat}
}
$List += $cList; $List2 += $cList2;
$i+=1; if($i -le $AllCount){Write-Progress -Activity "Analysing per-User Licensing Infomation" -PercentComplete (($i/$AllCount)*100) -CurrentOperation "$i of $AllCount" -ParentId 1}
Clear-Variable cUser;
}
#End of User Loop
$List | Export-Csv ($DesktopPath + "\" + $NameOfOutputFile_LicenseDetails) -NoTypeInformation
$List2 | Export-Csv ($DesktopPath + "\" + $NameOfOutputFile_ServiceStatus) -NoTypeInformation
$Error | Export-Csv ($DesktopPath + "\" + $NameOfOutputFile_Errors) -NoTypeInformation
$TimeFlag2 = (Get-Date); Write-Host "Time Taken: " ($TimeFlag2 - $TimeFlag1) " for " $AllCount " users."
$host.EnterNestedPrompt();#$host.ExitNestedPrompt()
#End of Script


Comments (0)

Skip to main content