Archive for May, 2013

Office 365 Licensing Report

Reporting is not one of those things that people enjoy doing… and under “people”, I mean most people… and under “most people”, I really mean myself. So, when I am asked to produce some kind of report, I rather do the hard work once and try to re-use it later. This time requested reporting was associated with Office 365 Licensing. We have all seen plethora of “assign license” scripts, from one liners, to screens and screen of code. However, once licenses are assigned (or mis-assigned) you might need to pull-down all that licensed user information and analyze it in Excel.
While working with licenses in Office 365, I have found myself being a little nostalgic about times when I was doing much more C# coding than I can afford nowadays. So Office 365 licensing information that you can get with MSOL PowerShell cmdlets is actually quire rich. The challenge is in the way you need to retrieve that licensing information from well-packaged object-oriented model that licensing information is coming at you from PowerShell.
So, below is the script that should allow you to retrieve licensing information from your tenant and present it in CSV format that you can slice and dice for analysis.

— — —

## Importing MS Online Module for PowerShell
Import-Module MSOnline;
## Connecting to MS Online Service
## Variables
[System.String]$emptyGuid = [System.Guid]::Empty.ToString();
[System.String]$path = Split-Path -parent $MyInvocation.MyCommand.Definition;
[System.String]$FileName = [System.String]::Format("{0}\{1}{2}{3}{4}{5}{6}-log.csv", $path, [System.DateTime]::Now.Year, [System.DateTime]::Now.Month, [System.DateTime]::Now.Day, [System.DateTime]::Now.Hour, [System.DateTime]::Now.Minute, [System.DateTime]::Now.Second);
## Parsing MSOL User Object for licensing information
function Get-MsolUserLicense{
[Parameter(Position=0, Mandatory = $true, ValueFromPipeline = $true, HelpMessage="Microsoft Online/Azure AD Account object")]
[System.String]$upn = $Identity.UserPrincipalName;
if ($Identity.IsLicensed) {
[System.String]$accountSkuId = $Identity.Licenses[0].AccountSkuId;
$accountServiceStatus = $Identity.Licenses[0].ServiceStatus;
$resultingCollection = @();
ForEach ($license in $accountServiceStatus){
$resultingCollection += Set-Entry -UserPrincipalName $upn -Sku $accountSkuId -ServiceName $license.ServicePlan.ServiceName -PlanId $license.ServicePlan.ServicePlanId.ToString() -Status $license.ProvisioningStatus.ToString();
else {
$resultingCollection += Set-Entry -UserPrincipalName $upn -Sku "None" -ServiceName "None" -PlanId $emptyGuid -Status "None";
return $resultingCollection;
## Create custom PS object to store intermediate results
function Set-Entry {
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true, HelpMessage="UPN of an Azure Active Directory User")]
[Parameter(Position=1, Mandatory=$true, HelpMessage="License SKU")]
[Parameter(Position=2, Mandatory=$true, HelpMessage="License/Plan Name")]
[Parameter(Position=3, Mandatory=$true, HelpMessage="License/Plan GUID")]
[Parameter(Position=4, Mandatory=$true, HelpMessage="License provisioning status")]
$collection = New-Object System.Object;
$collection | Add-Member -Type NoteProperty -Name "UserPrincipalName" -Value $UserPrincipalName;
$collection | Add-Member -Type NoteProperty -Name "SkuId" -Value $Sku;
$collection | Add-Member -Type NoteProperty -Name "ServiceName" -Value $ServiceName;
$collection | Add-Member -Type NoteProperty -Name "ServicePlanId" -Value $PlanId;
$collection | Add-Member -Type NoteProperty -Name "ProvisioningStatus" -Value $Status;
return $collection;
## Getting MSOL User Account
## TODO: Modify scope of accounts to fit your needs
$msolUsers = Get-MsolUser -All | where {$_.isLicensed -eq "True"}
Write-Host ("Found accounts: {0}" -f $msolUsers.Count);
foreach ($msolUser in $msolUsers)
$userCollection += Get-MsolUserLicense -Identity $msolUser;
## Convert intermidiate results into CSV
ForEach ($entry in $userCollection)
$payload += [System.String]::Format("{0},{1},{2},{3},{4}`n`r", $entry.UserPrincipalName, $entry.SkuId, $entry.ServiceName, $entry.ServicePlanId, $entry.ProvisioningStatus);
Out-File $payload;