microsoft365 – Modify PowerShell M365 Licenses Report script to connect to EXO Powershell V2

I would like to modify the attached scripts, so that the connection is made to EXO Powershell V2 and not the older module.

I have the new version of the ExchangeOnlineManagement module already installed, however I need to modify the scripts below to connect to the secure module and not to the older version of Exchange PowerShell cmdlets to be deprecated.

I also realize I have to change the Email send method as well, however I wanted to first accomplish this step to modify the main scripts to connect to the secure and latest ExchangeOnlineManagement module first.

@Eric Moreau gets the credit to the Script, as I simply tailored it to my needs.

Main Microsoft 365 Reports Script:

##########################################################################
# Script Part 1 - Summary of all Licences, followed by Details, then email
##########################################################################


Remove-Item -Path "$($PSScriptRoot)*.html"
Remove-Item -Path "$($PSScriptRoot)*.csv"
Remove-Item -Path "$($PSScriptRoot)*.docx"





$email1 = "email1@email.com"
$erroremail = "email1@email.com"

$from =  "email@email.com"
$smtpserver = "smtp.server.com"

$collection = @()

$output = new-item .office365report.csv -type file -force

##########Connect to MSOL#################################################

$encrypted = "...."
$user = "email1@email.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, ConsumedUnits
$embody1 = @()

$embody | foreach-object{
   
   $license = $_.AccountSkuId
   
   ###########################   VISIOCLIENT O365_BUSINESS_PREMIUM
   $licensevalue = @()
   switch -Wildcard ($license)
   {
      "*O365_BUSINESS_ESSENTIALS*" { $licensevalue += "Business Essentials " }
        "*O365_BUSINESS_PREMIUM*" { $licensevalue += "Business Premium " }
      "*ENTERPRISEPACK*" { $licensevalue += "E3" }
        "*VISIOCLIENT*" { $licensevalue += "Visio" }
        default { $licensevalue = "+" }
   }
   
   ########################
   
   
   $coll = "" | select License, ActiveUnits, ConsumedUnits
   
   $coll.License = $licensevalue
   $coll.ActiveUnits = $_.ActiveUnits
   $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])
         {
            if ([string]$provsts[$i] -eq "Success") { $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)
   {
      "*O365_BUSINESS_ESSENTIALS*" { $licensevalue += "Business Essentials " }
      "*O365_BUSINESS_PREMIUM*" { $licensevalue += "Business Premium " }
      "*ENTERPRISEPACK*" { $licensevalue += "E3" }
        "*VISIOCLIENT O365_BUSINESS_PREMIUM*" { $licensevalue += "Visio" }
        default { $licensevalue = "+" }
   }
   
   add-content $output "$FirstName,$LastName,$UserPrincipalName,$licensevalue,$prov1"
   
   
}

############################Send email report###############
$date = get-date




$a = "<style>"
$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 + "<br>"
#$a = $a + "<br>"
$a = $a + "</style>"
$a = $a + "<br>"
$a = $a + "<br>"

$embody1 | Select @{ Name = "License"; Expression = { $_.License } }, ActiveUnits, ConsumedUnits | ConvertTo-HTML -head $a | Out-File .Summarytot.html




###############################$Removed Superflour Licences Information########################

Get-Content "Summarytot.html" | Where-Object {$_ -notmatch '+'} | Set-Content "Summary.html"




##############################################################################################
# Script Part 2 - Detailed of all Licences, followed by amalgamation of html files, then email
##############################################################################################


#Variables to configure to fit your own environment

$SendTo = "email1@email.com"
$MailSubject = "Microsoft Office 365 Licences Report"  

#Modules to import
Import-Module MSOnline
Import-Module $PSScriptRootFunctions_ConnectToOffice365.ps1
Import-Module $PSScriptRootFunctions_SendEmail.ps1





   #Call ConnectToOffice365 
   ConnectToOffice365 


   #Call ConnectToOffice365 
   ConnectToOffice365 



#Some functions

function SizeToString
{
    #convert a number into a more readable size
    param([long]$pSize)

    if ($pSize -eq 0)
    {
        $return = "0 bytes"
    }
    else
    {
        $return = switch -Regex ([math]::truncate([math]::log($pSize,1024))) {
                              '^0' {"$pSize Bytes"}
                              '^1' {"{0:n2} KB" -f ($pSize / 1kb)}
                              '^2' {"{0:n2} MB" -f ($pSize / 1mb)}
                              '^3' {"{0:n2} GB" -f ($pSize / 1gb)}
                              '^4' {"{0:n2} TB" -f ($pSize / 1tb)}
                              Default {"{0:n2} PB" -f ($pSize / 1pb)}
                      }
    }
    return $return
}

function GetMailboxCounters
{
    #parse folders to split calendar items from contacts from emails and so on
    param([string]$pIdentity)

    #get all the properties of the mailbox folders
    $folders = Get-MailboxFolderStatistics -Identity $pIdentity | 
                Select FolderType, ItemsInFolder, FolderSize

    #initialize some counters
    $EmailsLiveNb = 0
    $EmailsLiveSize = 0
    $EmailsDeletedNb = 0
    $EmailsDeletedSize = 0
    $CalendarNb = 0
    $CalendarSize = 0
    $ContactsNb = 0
    $ContactsSize = 0

    foreach ( $folder in $folders) 
    { 
        $size = 0
        #get only the size in bytes
        $size = $folder.FolderSize.Split("(")[1].Split(" ")[0].Replace(",","")

        if (($folder.FolderType -eq "user created") -or 
            ($folder.FolderType -eq "inbox") -or 
            ($folder.FolderType -eq "sentitems"))
        {
            $EmailsLiveNb += $folder.ItemsInFolder
            $EmailsLiveSize += $size
        }
        elseif ($folder.FolderType -eq "deleteditems")
        {
            $EmailsDeletedNb += $folder.ItemsInFolder
            $EmailsDeletedSize += $size
        }
        elseif ($folder.FolderType -eq "Calendar")
        {
            $CalendarNb += $folder.ItemsInFolder
            $CalendarSize += $size
        }
        elseif ($folder.FolderType -contains "contacts")
        {
            $ContactsNb += $folder.ItemsInFolder
            $ContactsSize += $size
        }
    }

    #return values
    [hashtable]$Return = @{} 
    $Return.EmailsLiveNb = $EmailsLiveNb
    $Return.EmailsLiveSize = $EmailsLiveSize
    $Return.EmailsDeletedNb = $EmailsDeletedNb
    $Return.EmailsDeletedSize = $EmailsDeletedSize
    $Return.CalendarNb = $CalendarNb 
    $Return.CalendarSize = $CalendarSize
    $Return.ContactsNb = $ContactsNb
    $Return.ContactsSize = $ContactsSize

    Return $Return
}

function CreateBodyHeader
{
    #create the start of the email body

    $body = ""
    $body += "<html>"
    $body += "<body>"
    $body += "<h3>Office 365 - User Mailbox Statistics Report</h3>" 
    $body += "<h3>As of " + $(get-date).ToString("yyyy/MM/dd") + " </h3>"
    $body += "<br><br>"

    # TABLE Header
    $body += "<table border=1>" 
    $body += "<tr bgcolor="#7C7C7C">" 
    $body += "<th>#</th>"
    $body += "<th>Account Name</th>" 
    $body += "<th>Live Emails<br>(size)</th>"
    $body += "<th>Live Emails<br>(count)</th>"
    $body += "<th>Deleted Emails<br>(size)</th>"
    $body += "<th>Deleted Emails<br>(count)</th>"
    $body += "<th>Calendar items<br>(count)</th>"
    $body += "<th>Contacts<br>(count)</th>"
    $body += "<th>Total Account<br>(size)</th>" 
    $body += "<th>Total items<br>(count)</th>" 
    $body += "<th>Last Logon</th>" 
    $body += "<th>Recipient Type</th>"
    $body += "<th>Is Licensed</th>"
    $body += "<th>Licenses</th>"
    $body += "</tr>" 

    return $body
}

function CreateBodyFooter
{
    $body = ""

    $body += "</table>"
    $body += "</body>"
    $body += "</html>"

    return $body
}

function CreateCollectionItem
{
    param($pMailbox)

    #Get other details from the account
    $mailboxCounters = GetMailboxCounters $pMailbox.Identity.ToString()  
    $mailboxStat = Get-MailboxStatistics $pMailbox.UserPrincipalName | 
                    Select ItemCount, TotalItemSize, LastLogonTime
    $userInfo = Get-MsolUser -UserPrincipalName $pMailbox.UserPrincipalName | 
                Select IsLicensed, @{Name="Licenses"; Expression ={$_.Licenses.AccountSkuId}}  

    #manipulate some values
    $size = $mailboxStat.TotalItemSize.value.ToString().Split("(")[1].Split(" ")[0].Replace(",","")
    $lastLogon = ""            
    if($mailboxStat.LastLogonTime) 
    {
        $lastLogon = $mailboxStat.LastLogonTime.ToString("yyyy/MM/dd HH:mm:ss")
    }

    $newItem = New-Object System.Object
    $newItem | Add-Member -MemberType NoteProperty -Name "UserPrincipalName" -Value $pMailbox.UserPrincipalName
    $newItem | Add-Member -MemberType NoteProperty -Name "DisplayName" -Value $pMailbox.DisplayName
    $newItem | Add-Member -MemberType NoteProperty -Name "EmailsLiveSize" -Value (SizeToString($mailboxCounters.EmailsLiveSize))
    $newItem | Add-Member -MemberType NoteProperty -Name "EmailsLiveNb" -Value $mailboxCounters.EmailsLiveNb.ToString("#,##0") 
    $newItem | Add-Member -MemberType NoteProperty -Name "EmailsDeletedSize" -Value (SizeToString($mailboxCounters.EmailsDeletedSize)) 
    $newItem | Add-Member -MemberType NoteProperty -Name "EmailsDeletedNb" -Value $mailboxCounters.EmailsDeletedNb.ToString("#,##0") 
    $newItem | Add-Member -MemberType NoteProperty -Name "CalendarNb" -Value $mailboxCounters.CalendarNb.ToString("#,##0")
    $newItem | Add-Member -MemberType NoteProperty -Name "ContactsNb" -Value $mailboxCounters.ContactsNb.ToString("#,##0")
    $newItem | Add-Member -MemberType NoteProperty -Name "TotalItemSize" -Value (SizeToString($size)) 
    $newItem | Add-Member -MemberType NoteProperty -Name "TotalItemCount" -Value $mailboxStat.ItemCount.ToString("#,##0") 
    $newItem | Add-Member -MemberType NoteProperty -Name "LastLogonTime" -Value $lastLogon
    $newItem | Add-Member -MemberType NoteProperty -Name "RecipientTypeDetails" -Value $pMailbox.RecipientTypeDetails
    $newItem | Add-Member -MemberType NoteProperty -Name "IsLicensed" -Value $userInfo.IsLicensed
    $newItem | Add-Member -MemberType NoteProperty -Name "Licenses" -Value ($userInfo.Licenses -replace 'reseller-account:')
    $newItem | Add-Member -MemberType NoteProperty -Name "TotalItemSizeRaw" -Value ([long]$size)

    return $newItem
}


#Start of the script ----------------------------------------------------------------------------------------------------------------------------------


    cls

    Write-Output "Getting Office 365 accounts statistics" 
    Write-Output "Script starting @ $(get-date)" 

   #Remove all existing Powershell sessions
   Get-PSSession | Remove-PSSession
   
   #Call ConnectToOffice365 
   ConnectToOffice365    

    #Get the list of accounts
    $mailboxes = Get-Mailbox | 
                Select Identity, DisplayName, UserPrincipalName, RecipientTypeDetails |
                Sort DisplayName
         
    #Processing 
    Write-Output ("Number of accounts to process = " + $mailboxes.Count.ToString())

    $collectionWithItems = New-Object System.Collections.ArrayList

    $mailboxCount = 0
    foreach ($mailbox in $mailboxes) 
    { 
        $mailboxCount ++
        Write-Output ("Processing $mailboxCount - $mailbox")
        $collectionWithItems.Add((CreateCollectionItem $mailbox)) | Out-Null
    } 

    #Sort the list from largest to smallest account
    $collectionWithItems = $collectionWithItems | 
                            sort-object @{Expression={ $_.Licenses}; Ascending=$true}

    #format the list in HTML
    $mailboxCount = 0
    $htmlOutput = ""
    $htmlOutput += CreateBodyHeader
    foreach ($item in $collectionWithItems)
    {
        $mailboxCount ++
        $htmlOutput += "<tr>" 
        $htmlOutput += "<td align=center>$mailboxCount</td>" 
        $htmlOutput += "<td>" + $item.DisplayName + "</td>" 
        $htmlOutput += "<td align=center>" + $item.EmailsLiveSize + "</td>" 
        $htmlOutput += "<td align=center>" + $item.EmailsLiveNb + "</td>" 
        $htmlOutput += "<td align=center>" + $item.EmailsDeletedSize + "</td>" 
        $htmlOutput += "<td align=center>" + $item.EmailsDeletedNb + "</td>" 
        $htmlOutput += "<td align=center>" + $item.CalendarNb + "</td>" 
        $htmlOutput += "<td align=center>" + $item.ContactsNb + "</td>" 
        $htmlOutput += "<td align=center>" + $item.TotalItemSize + "</td>" 
        $htmlOutput += "<td align=center>" + $item.TotalItemCount + "</td>" 
        $htmlOutput += "<td align=center>" + $item.LastLogonTime + "</td>" 
        $htmlOutput += "<td align=center>" + $item.RecipientTypeDetails + "</td>"
        $htmlOutput += "<td align=center>" + $item.IsLicensed + "</td>" 
        $htmlOutput += "<td>" + $item.Licenses + "</td>"
        $htmlOutput += "</tr>" 
    }
    $htmlOutput += CreateBodyFooter


    $htmlOutput | Out-File -FilePath "c:....somepathDetails.html"


    #combines both html files into one
    New-Item -ItemType file "O365Report.html"
    $file1 = Get-Content "Summary.html"
    $file2 = Get-Content "Details.html"
    Add-Content "O365Report.html" $file1
    Add-Content "O365Report.html" $file2


    #specifies the htmloutput file to use
    $htmlOutput = Get-Content -Path C...somepathO365Report.html | Out-String

    



    


    #Send the email
    #SendEmail $SendTo $MailSubject $htmlOutput $attachments

    cmd /c C:...somepathConvertHTMLtoDoc.bat

$filename="C:...somepath...O365Report.docx"

# start Word
$objWord = New-Object -ComObject Word.Application
$objWord.Visible = $True
# open the file
$doc = $objWord.documents.open($filename)
# set orientation to landscape
$doc.PageSetup.Orientation = 1
# set paper size to legal
$doc.PageSetup.PaperSize = 4
# set font name and size
$doc.Range().Font.Name="Calibri"
$doc.Range().Font.Size = 7
# remove spaces between paragraphs
$doc.Paragraphs.SpaceAfter = -0
# save doc
$doc.Save()
# close and exit
$doc.Close()
$objWord.Quit()



#SendEMail $SendTo $MailSubject $htmlOutput 


#Send Email 
$EncryptedCredential = "C......txt" 
$EmailUsername = "email1@email.com" 
$EncryptedPW = Get-Content $EncryptedCredential | ConvertTo-SecureString 
$Credential = New-Object System.Management.Automation.PsCredential($EmailUsername, $EncryptedPW) 
$EmailFrom = "email1@email.com" 
$EmailTo = "email1@email.com"
$EmailAttachments = Get-ChildItem -Path 'C:somepath' -Filter *.docx | Select-Object -ExpandProperty FullName
$SMTPServer = "smtp.server.com" 
$SMTPPort = 123

$SMTPSsl = $true 

$param = @{ 
    SmtpServer = $SMTPServer 
    Port = $SMTPPort 
    UseSsl = $SMTPSsl   
    Credential = $Credential   
    From = $EmailFrom   
    To = $EmailTo   
    Subject = $MailSubject
    Body = $htmlOutput
    BodyAsHtml = $true
    Attachments = $EmailAttachments
} 

Send-MailMessage @param

The secondary Script which this imports, is as follows and is called:

Functions_ConnectToOffice365.ps1

    #Variables to configure to fit your own environment
$Office365Username = "email1@email.com"
$Office365PasswordFile = $PSScriptRoot + "O365Pwd.txt"


#Some re-usable functions

function ConnectToOffice365
{   
    #connect to the Office 365 tenant
      
   #Build credentials object
   $cred = New-Object `
                -TypeName System.Management.Automation.PSCredential `
                -ArgumentList $Office365Username, (Get-Content $Office365PasswordFile | ConvertTo-SecureString)
   
   #Create remote Powershell session
   $Session = New-PSSession -ConfigurationName Microsoft.Exchange `
                            -ConnectionUri https://ps.outlook.com/powershell `
                            -Credential $cred `
                            -Authentication Basic `
                            –AllowRedirection       

   #Import the session
    Import-PSSession $Session -AllowClobber | Out-Null

    Connect-MsolService -Credential $cred
}

I would need to modify the above scripts, so any assistance is much appreciated.

Leave a Comment