Office365 License Report

Office365 License report.

Before you can run this report make sure you have Azure AD installed. if not you can find the instruction here

In this script, I have used my O365 Mailbox to send email report out which I described here.

You can download the PS1 here.

Your PowerShell program may restrict the ps1 to unlock the same run the "Unblock-File" cmd.

#######################################
# 
#  Get-MSONLINE Licence status report detailed
#  Author = Sunil chauhan
#  Email = sunilkms@hotmail.com
#  
########################################


#####-Update Email settings and Receipent List Here#--------------

$cred = Get-Credential
$from = $cred.UserName
$to="sunilkms@hotmail.com"
$smtpServer = "smtp.office365.com"


###-update Log and Report file Location here# -------------------------
$Reporthome = "$home\Desktop\Reports\"

##Report and Log file section# *Do not edit.
$reportName = "MSOLicense-Report-$(Get-date -Format dd-MMM).csv"
$report = $Reporthome + $reportName 
$Logs= $Reporthome + "Logs.txt"
Clear-Content -Path $Logs

#Report Subject
$subject = "Daily Office365 License Report Detailed#$(Get-date -Format dd-MMM)"

write-host "Starting Script.."
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Starting Script..")

# Importing MS online Module & Connect to MSOnline
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Trying to connect to MSOL")

Import-Module MSOnline

try {

Connect-MsolService -Credential $cred -ErrorAction Stop

 } catch {
 
 Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Connection Failed to MSOL")
 Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + $($Error[0].Exception))
 break
 
 }

"Successfully Connected to MSOL." 
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Successfully Connected to MSOL."  )

#Getting All the MSOL Users
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Querying All users in MSOL.."  )

#$Users = Get-MsolUser -MaxResults 100
$Users = Get-MsolUser -All

Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Finished Querying All users."  )

Write-host  "Total MSOL Users" $Users.count

# Seprating all Licensed Users
$LicensedUsers = $users | ? {$_.IsLicensed -eq "True"}
Write-host "Total Licensed Users" $LicensedUsers.count

# Declaring an empty array to save report data.
$RR=@()

<# 

Microsoft may add new service or change the license type order in there system, so it is advised to corss check
time to time and update the same in the scirpt.

User with Proplus License ServicePlan
-----------
[0] SWAY                                                      
[1] INTUNE_O365
[2] YAMMER_ENTERPRISE
[3] RMS_S_ENTERPRISE
[4] OFFICESUBSCRIPTION
[5] MCOSTANDARD
[6] SHAREPOINTWAC
[7] SHAREPOINTENTERPRISE
[8] EXCHANGE_S_ENTERPRISE 


User without proplus ServicePlan
-----------
[0] SWAY
[1] INTUNE_O365
[2] YAMMER_ENTERPRISE
[3] SHAREPOINTWAC
[4] SHAREPOINTENTERPRISE
[5] RMS_S_ENTERPRISE
[6] MCOSTANDARD
[7] EXCHANGE_S_ENTERPRISE 

#>

Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Preparing Report.."  )

foreach ($user in $LicensedUsers) {

#

$sku = Get-MsolAccountSku

# [0] LetsExchange:POWER_BI_INDIVIDUAL_USER
# [1] LetsExchange:ENTERPRISEPACKWITHOUTPROPLUS
# [2] LetsExchange:ENTERPRISEPACK
# [3] LetsExchange:POWER_BI_ADDON
# [4] LetsExchange:MCOEV
# [5] LetsExchange:PLANNERSTANDALONE
# [6] LetsExchange:POWER_BI_STANDARD
# [7] LetsExchange:ECAL_SERVICES
# [8] LetsExchange:EMS
# [9] LetsExchange:MCOPSTN2


$ent = $sku[0].accountname + ":ENTERPRISEPACK"

if ( $user.Licenses.AccountSkuid -eq $ent) {

$ob = New-Object –TypeName PSObject
$ob | Add-Member –MemberType NoteProperty –Name UPN –Value $User.UserPrincipalName
$ob | Add-Member –MemberType NoteProperty –Name LicensedType –Value $user.Licenses.Accountskuid
$ob | Add-Member –MemberType NoteProperty –Name SWAY –Value $user.Licenses.ServiceStatus.ProvisioningStatus[0]
$ob | Add-Member –MemberType NoteProperty –Name INTUNE –Value $user.Licenses.ServiceStatus.ProvisioningStatus[1]
$ob | Add-Member –MemberType NoteProperty –Name Yammer –Value $user.Licenses.ServiceStatus.ProvisioningStatus[2]
$ob | Add-Member –MemberType NoteProperty –Name RMS –Value $user.Licenses.ServiceStatus.ProvisioningStatus[3]
$ob | Add-Member –MemberType NoteProperty –Name Office –Value $user.Licenses.ServiceStatus.ProvisioningStatus[4]
$ob | Add-Member –MemberType NoteProperty –Name OfficeOnline –Value $user.Licenses.ServiceStatus.ProvisioningStatus[6]
$ob | Add-Member –MemberType NoteProperty –Name Sharepoint –Value $user.Licenses.ServiceStatus.ProvisioningStatus[7]
$ob | Add-Member –MemberType NoteProperty –Name Mailbox –Value $user.Licenses.ServiceStatus.ProvisioningStatus[8]
$ob | Add-Member –MemberType NoteProperty –Name Lync –Value $user.Licenses.ServiceStatus.ProvisioningStatus[5]

$rr+=$ob

}

else 

{

$ob = New-Object –TypeName PSObject
$ob | Add-Member –MemberType NoteProperty –Name UPN –Value $User.UserPrincipalName
$ob | Add-Member –MemberType NoteProperty –Name LicensedType –Value $user.Licenses.Accountskuid
$ob | Add-Member –MemberType NoteProperty –Name SWAY –Value $user.Licenses.ServiceStatus.ProvisioningStatus[0]
$ob | Add-Member –MemberType NoteProperty –Name INTUNE –Value $user.Licenses.ServiceStatus.ProvisioningStatus[1]
$ob | Add-Member –MemberType NoteProperty –Name Yammer –Value $user.Licenses.ServiceStatus.ProvisioningStatus[2]
$ob | Add-Member –MemberType NoteProperty –Name RMS –Value $user.Licenses.ServiceStatus.ProvisioningStatus[5]
$ob | Add-Member –MemberType NoteProperty –Name Office –Value "NA"
$ob | Add-Member –MemberType NoteProperty –Name OfficeOnline –Value $user.Licenses.ServiceStatus.ProvisioningStatus[3]
$ob | Add-Member –MemberType NoteProperty –Name Sharepoint –Value $user.Licenses.ServiceStatus.ProvisioningStatus[4]
$ob | Add-Member –MemberType NoteProperty –Name Mailbox –Value $user.Licenses.ServiceStatus.ProvisioningStatus[7]
$ob | Add-Member –MemberType NoteProperty –Name Lync –Value $user.Licenses.ServiceStatus.ProvisioningStatus[6]

$rr+=$ob

}

}

$RR | Export-csv $report -NoTypeInformation

Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Finished preparing report."  )

# Prepare Email Body -- You can Edit the body they you whould like to be.

$EmailBody = @"

 Office365 License Report: $(Get-date -Format dd-MMM-yy)

 Orgnization: $($sku[1].AccountName)
 Total Assigned Licensed: $($LicensedUsers.count)

 License Summary : ENTERPRISE PACK
 ---------------------
 ActiveUnits: $($sku[2].ActiveUnits)
 ConsumedUnits: $($sku[2].consumedUnits)
 Available: $($($sku[2].ActiveUnits) - $($sku[2].consumedUnits))

 License Summary : ENTERPRISE PACK WITHOUT PROPLUS
 ----------------------
 ActiveUnits: $($sku[1].ActiveUnits)
 ConsumedUnits: $($sku[1].consumedUnits)
 Available: $($($sku[1].ActiveUnits) - $($sku[1].consumedUnits))

 Thanks
 
"@

try {

Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Trying to email report."  )

# use below cmd to send email using mail relay server.
#Send-MailMessage -From $from -To $to -Subject $subject -Body $EmailBody -SmtpServer mail.letsExchange.in -Attachments $report

#use below cmd to send email using your own mailbox in 365
# NOTE** - This required your admin account to have a mailboxes to send email using o365 mailbox.

Send-MailMessage -From $from -To $to -Subject $subject -Body $EmailBody -SmtpServer $smtpServer `
-Attachments $report -Credential $cred -UseSsl -ErrorAction Stop

} catch { 

write-host "Error Occured while sending report" -ForegroundColor Yellow
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "Error occured while sending report."  )
Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + $($Error[0].Exception))
break

}

Add-Content -Path $Logs -Value $($(GEt-date -Format HH:mm:ss-dd-MMM#) + "finished Reporting License"  )
Send-MailMessage -From $from -To $to -Subject "Logs: O365 Licenses Report - $(Get-date -Format MM-dd-yy)" `
 -Body "O365 License Report Logs for $(Get-date -Format MM-dd-yy)" `
 -SmtpServer $smtpServer -Attachments $logs -Credential $cred -UseSsl -ErrorAction Stop

"finished Reporting License"