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

  • Article
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
  2. Filtering data in 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

  • Anonymous
    October 09, 2017
    Hi,One minor comment to your code... Due to encoding issues (my language uses accents and other characters like 'ç'), I would add to the Export-Csv lines an -Enconding UTF8.Thanks for sparing me a few hours to code this... ;)