office 365 License Report by Email

New version: https://techwizard.cloud/2022/05/19/office-365-license-report-by-email-v2/

below is retired:

This script is extension to my below script with some modifications requested by followers.

Prerequisites:

Install Microsoft Online Services Module for Windows PowerShell 32 bit/64 bit both are available when you will browse the link.

Refer:

Connecting Office 365 via PowerShell

Ones you are setup, just download the zip file from below link.

Update the values in below .ps1 file

————————————————–

$email1 = “O365LicenseReport@labtest.com” # this account will receive dthe report

$erroremail = “o365admin@labtest.com” # if script result in error than this account will be notified
$from = “O365LicenseReport@labtest.com”

$smtpserver = “smtp.labtest.com”

—————————————————

$encrypted = “01000000d08c9ddf0115d1118c7a00c04fa930353fc86af161d62af27a082839eb967c8e”

$user = “Lab-O365-SVC@company.onmicrosoft.com”

—————————————————-

I have included the encryt pass folder so that you can generate the encrypted password to be inserted in the script for making cloud connection.

“Run the batch file inside it to encrypt the password, securepassword.txt file will get genrated & from that you can copy the password inside the script.”

Now just schedule the batch file via task scheduler (screenshot of important settings is below as admins genrally miss startin folder)

You will receive below report (summary in HTML & details in CSV)

Note: you can edit /add the values in switch case statement to make it appear as friendly names for SKUs.

Example if a SKU is there that is coming asnot found in the report Just add it inswitch satement to get detected.

PowerShell

############################################################### 
#            Author: Vikas Sukhija (http://techwizard.cloud) 
#            Date: 8/4/2016 
#            Reviewer: 
#            updated: 8/11/2016 (created heders/CSV logic) 
#            Updated: 8/15/2016 (fixed service status when m,ultiple licenses are there) 
#            Updated: 10/7/2016 (updated for licensed users only) 
#            Updated: 10/9/2016 (updated with html body to include total counts) 
#            Description: Office 365 users Report 
# 
############################################################### 
 
$email1 = "O365LicenseReport@labtest.com" 
$erroremail = "o365admin@labtest.com" 
 
$from =  "O365LicenseReport@labtest.com" 
$smtpserver = "smtp.labtest.com" 
 
$collection = @() 
 
$output = new-item .\office365report.csv -type file -force 
 
##########Connect to MSOL################################################# 
 
$encrypted = "01000000d08c9ddf0115d1118c7a00c04fa930353fc86af161d62af27a082839eb967c8e" 
$user = "Lab-O365-SVC@company.onmicrosoft.com" 
$password = ConvertTo-SecureString -string $encrypted 
 
$Credential = New-Object System.Management.Automation.PSCredential -ArgumentList $User, $password 
 
 
Connect-MsolService -Credential $Credential 
 
if($error -ne $null){ 
$msg = new-object Net.Mail.MailMessage 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$msg.From = $from 
 
#mail recipient 
$msg.To.Add($erroremail) 
$msg.Subject = "Error occured in Connecting to cloud" 
$msg.Body = $error 
$smtp.Send($msg) 
exit 
}  
 
################################################################################ 
 
$lict = Get-MsolAccountSku 
$embody = $lict | Select AccountSkuId,ActiveUnits,WarningUnits,ConsumedUnits 
$embody1=@() 
 
$embody  | foreach-object{ 
 
$license=$_.AccountSkuId 
 
########################### 
$licensevalue=@() 
switch -Wildcard ($license){ 
    "*DESKLESSPACK*" {$licensevalue+="Office 365 (Plan K1)"}  
        "*DESKLESSWOFFPACK*" {$licensevalue+="Office 365 (Plan K2)"}  
        "*LITEPACK*" {$licensevalue+="Office 365 (Plan P1)"}  
        "*EXCHANGESTANDARD*" {$licensevalue+="Office 365 Exchange Online Only"}  
        "*STANDARDPACK*" {$licensevalue+="Office 365 (Plan E1)"}  
        "*ENTERPRISEPACK*" {$licensevalue+="Office 365 (Plan E3)"}  
        "*ENTERPRISEPACKLRG*" {$licensevalue+="Office 365 (Plan E3)"} 
        "*ENTERPRISEWITHSCAL*" {$licensevalue+="Office 365 (Plan E4)"} 
        "*STANDARDPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A1) for Students"} 
    "*ENTERPRISEPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A3) for Students"} 
    "*ENTERPRISEWITHSCAL_STUDENT*" {$licensevalue+="Office 365 (Plan A4) for Students"} 
    "*STANDARDPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A1) for Faculty"} 
    "*STANDARDWOFFPACKPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A2) for Faculty"} 
    "*ENTERPRISEPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A1) for Students"} 
    "*STANDARDPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A1) for Students"}     
    "*ENTERPRISEPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A3) for Faculty"} 
    "*ENTERPRISEWITHSCAL_FACULTY*"{$licensevalue+="Office 365 (Plan A4) for Faculty"} 
    "*ENTERPRISEPACK_B_PILOT*" {$licensevalue+="Office 365 (Enterprise Preview)"} 
    "*STANDARD_B_PILOT*" {$licensevalue+="Office 365 (Small Business Preview)"} 
        "*VISIOCLIENT*"{$licensevalue+="Office 365 (Visio)"} 
        "*PROJECTCLIENT*"{$licensevalue+="Office 365 (PROJECTCLIENT)"} 
        "*POWER_BI_STANDARD*"{$licensevalue+="Office 365 (POWER_BI)"} 
        "*DEVELOPERPACK*"{$licensevalue+="Office 365 (DEVELOPERPACK)"} 
        "*PROJECTPROFESSIONAL*"{$licensevalue+="Office 365 (PROJECTPROFESSIONAL)"} 
         default {$licensevalue="not found"} 
} 
 
######################## 
 
 
$coll = ""| select License,ActiveUnits,WarningUnits,ConsumedUnits 
 
$coll.License = $licensevalue 
$coll.ActiveUnits = $_.ActiveUnits 
$coll.WarningUnits = $_.WarningUnits 
$coll.ConsumedUnits = $_.ConsumedUnits 
 
$embody1 +=$coll} 
 
 
$services = $lict.ServiceStatus 
 
$csv = @() 
$csv1=$null 
 
foreach($service in $services){ 
$provisionstatus = $service.ProvisioningStatus 
$servicename = $service.ServicePlan.ServiceName 
$csv += $servicename} 
 
$expcsv=$csv | sort -Unique 
 
$countcs= $expcsv.count 
 
foreach($cs in $expcsv){$csv1 += [string]$cs + ","} 
 
$csv2 = $csv1.Substring(0,$csv1.Length-1) 
 
add-content $output "FirstName,LastName,UserPrincipalName,License,$csv2" 
 
##################GEt values from users##################### 
 
$allusers = get-msoluser -ALL | where{$_.isLicensed -eq $true} 
 
foreach($user in $allusers){ 
 
$FirstName = $user.FirstName 
$LastName = $user.LastName 
$UserPrincipalName = $user.UserPrincipalName 
 
Write-host "Processing..................$UserPrincipalName" -foregroundcolor green 
 
$licenses = $user.Licenses.AccountSkuId 
$license=$null 
if($licenses -notlike $null){ 
foreach($lic in $licenses){$license += $lic + ","} 
$license= $license.Substring(0,$license.Length-1)} 
 
$serviceplan = $user.Licenses.ServiceStatus.serviceplan 
$provsts = $user.Licenses.ServiceStatus.provisioningstatus 
$spcount = $serviceplan.count 
 
[int]$spcount1=$spcount -1 
[int]$countcs1=$countcs -1 
 
################Compare the Header with Values################ 
 
$provval1=@(0..[int]$countcs1) 
 
for($j=0;$j -le [int]$countcs1;$j++){$provval1[$j]="Not Active"} 
 
for($i=0;$i -le [int]$spcount1;$i++){ 
    for($j=0;$j -le [int]$countcs1;$j++){ 
    if($serviceplan[$i].serviceName -eq $expcsv[$j]){ 
    $provval1[$j] = [string]$provsts[$i]} } 
} 
 
$prov=$null 
 
for($j=0;$j -le [int]$countcs1;$j++){ 
$prov += [string]$provval1[$j] + ","} 
 
$prov1= $prov.Substring(0,$prov.Length-1) 
 
#####################License values######################### 
$licensevalue=@() 
switch -Wildcard ($license){ 
    "*DESKLESSPACK*" {$licensevalue+="Office 365 (Plan K1)"}  
        "*DESKLESSWOFFPACK*" {$licensevalue+="Office 365 (Plan K2)"}  
        "*LITEPACK*" {$licensevalue+="Office 365 (Plan P1)"}  
        "*EXCHANGESTANDARD*" {$licensevalue+="Office 365 Exchange Online Only"}  
        "*STANDARDPACK*" {$licensevalue+="Office 365 (Plan E1)"}  
        "*ENTERPRISEPACK*" {$licensevalue+="Office 365 (Plan E3)"}  
        "*ENTERPRISEPACKLRG*" {$licensevalue+="Office 365 (Plan E3)"} 
        "*ENTERPRISEWITHSCAL*" {$licensevalue+="Office 365 (Plan E4)"} 
        "*STANDARDPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A1) for Students"} 
    "*ENTERPRISEPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A3) for Students"} 
    "*ENTERPRISEWITHSCAL_STUDENT*" {$licensevalue+="Office 365 (Plan A4) for Students"} 
    "*STANDARDPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A1) for Faculty"} 
    "*STANDARDWOFFPACKPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A2) for Faculty"} 
    "*ENTERPRISEPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A1) for Students"} 
    "*STANDARDPACK_STUDENT*" {$licensevalue+="Office 365 (Plan A1) for Students"}     
    "*ENTERPRISEPACK_FACULTY*" {$licensevalue+="Office 365 (Plan A3) for Faculty"} 
    "*ENTERPRISEWITHSCAL_FACULTY*"{$licensevalue+="Office 365 (Plan A4) for Faculty"} 
    "*ENTERPRISEPACK_B_PILOT*" {$licensevalue+="Office 365 (Enterprise Preview)"} 
    "*STANDARD_B_PILOT*" {$licensevalue+="Office 365 (Small Business Preview)"} 
        "*VISIOCLIENT*"{$licensevalue+="Office 365 (Visio)"} 
        "*PROJECTCLIENT*"{$licensevalue+="Office 365 (PROJECTCLIENT)"} 
        "*POWER_BI_STANDARD*"{$licensevalue+="Office 365 (POWER_BI)"} 
        "*DEVELOPERPACK*"{$licensevalue+="Office 365 (DEVELOPERPACK)"} 
        "*PROJECTPROFESSIONAL*"{$licensevalue+="Office 365 (PROJECTPROFESSIONAL)"} 
         default {$licensevalue="not found"} 
} 
 
add-content $output "$FirstName,$LastName,$UserPrincipalName,$licensevalue,$prov1" 
 
 
} 
 
############################Send email report############### 
$date= get-date 
 
$a = "" 
$a = $a + "BODY{background-color:peachpuff;}" 
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}" 
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}" 
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;}" 
$a = $a + "" 
 
$embody1 | Select @{Name="License";Expression={$_.License}},ActiveUnits,WarningUnits,ConsumedUnits | ConvertTo-HTML -head $a | Out-File .\body.html 
$cont = get-content .\body.html 
  
$msg = new-object Net.Mail.MailMessage 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$msg.From = $from 
$attach = new-object Net.Mail.Attachment($output)  
 
#mail recipient 
$msg.To.Add($email1) 
$msg.IsBodyHtml = $True  
$msg.Subject = "Office 365 License Report - $date" 
$msg.Body = $cont 
$msg.Attachments.Add($attach) 
$smtp.Send($msg) 
 
###############################$error reporting################################ 
 
if($error -ne $null){ 
$msg = new-object Net.Mail.MailMessage 
$smtp = new-object Net.Mail.SmtpClient($smtpServer) 
$msg.From = $from 
 
#mail recipient 
$msg.To.Add($erroremail) 
$msg.Subject = "ErrorReport for Office 365 Licesne Report" 
$msg.Body = $error 
$smtp.Send($msg) 
}  
################################################################################