Create report for mailbox usage space by email domain in Powershell 365 hosted? Tenant has multiple domains

Boe Dillard 666 Reputation points
2023-03-07T16:08:28.5733333+00:00

Hello,

I have a tenant with multiple domains. I have a script to export the list of users and their mailbox size but it mixes all the users and their main domain with users belonging to another company in the same domain. Is it possible to have it sort it by their login name or primary email address so all the users at acme.com show up in one list and all the users in @widgets.com show up in a seperate section or sheet? I'm also fine if there is a way of searching specifically e.g. add a line to the script so it only pulls the info for users @acme.com

Open powershell AS ADMINISTRATOR with 365/azure connect

 

Set-ExecutionPolicy RemoteSigned

$UserCredential = Get-Credential

 

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session -DisableNameChecking
Set-ExecutionPolicy RemoteSigned

 

Login info

admin@acme.... (whatever account you use)

Password for account

 

Write-Host "Gathering Stats, Please Wait.."
$Mailboxes = Get-Mailbox -ResultSize Unlimited | Select UserPrincipalName, identity, ArchiveStatus
$MailboxSizes = @()
foreach ($Mailbox in $Mailboxes) {
$ObjProperties = New-Object PSObject
$MailboxStats = Get-MailboxStatistics $Mailbox.UserPrincipalname | Select DisplayName, LastLogonTime, TotalItemSize, ItemCount
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Display Name" -Value $MailboxStats.DisplayName
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "UserPrincipalName" -Value $Mailbox.UserPrincipalName
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Last Logged In" -Value $MailboxStats.LastLogonTime
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Mailbox Size (GB)" -Value ([math]::Round(($MailboxStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))

if ($Mailbox.ArchiveStatus -eq "Active") {
$ArchiveStats = Get-MailboxStatistics $Mailbox.UserPrincipalname -Archive | Select TotalItemSize, ItemCount
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value ([math]::Round(($ArchiveStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))
}
else {
Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value "0"

}
$MailboxSizes += $ObjProperties
}
$MailboxSizes | Export-csv "C:\inboxsizes.csv" -NoTypeInformation

Get-PSSession | Remove-PSSession

 

 

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
3,964 questions
Exchange Server Development
Exchange Server Development
Exchange Server: A family of Microsoft client/server messaging and collaboration software.Development: The process of researching, productizing, and refining new or existing technologies.
518 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,161 questions
{count} votes

3 answers

Sort by: Most helpful
  1. AhmedAli Shaik 80 Reputation points
    2023-03-07T16:55:49.0433333+00:00

    Please try the below:

    Write-Host "Gathering Stats, Please Wait.."

    $Mailboxes = Get-Mailbox -ResultSize Unlimited | Select UserPrincipalName, identity, ArchiveStatus

    $MailboxSizes = @()

    foreach ($Mailbox in $Mailboxes) {

    $Domain = $mailbox.PrimarySmtpAddress -split '@'

    $ObjProperties = New-Object PSObject

    $MailboxStats = Get-MailboxStatistics $Mailbox.UserPrincipalname | Select DisplayName, LastLogonTime, TotalItemSize, ItemCount

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Display Name" -Value $MailboxStats.DisplayName

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "UserPrincipalName" -Value $Mailbox.UserPrincipalName

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Last Logged In" -Value $MailboxStats.LastLogonTime

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Mailbox Size (GB)" -Value ([math]::Round(($MailboxStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Email Address" -Value $Mailbox.PrimarySmtpAddress

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Domain" -Value $Domain[1]

    if ($Mailbox.ArchiveStatus -eq "Active") {

    $ArchiveStats = Get-MailboxStatistics $Mailbox.UserPrincipalname -Archive | Select TotalItemSize, ItemCount

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value ([math]::Round(($ArchiveStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))

    }

    else {

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value "0"

    }

    $MailboxSizes += $ObjProperties

    $MailboxReport = $MailboxSizes | sort-Object Domain

    }

    $MailboxSizes | Export-csv "C:\inboxsizes.csv" -NoTypeInformation

    Get-PSSession | Remove-PSSession


  2. Boe Dillard 666 Reputation points
    2023-03-07T18:12:51.57+00:00

    Thanks - I tried this - I only want users with an acme.com domain email but got nothing. There are several domains so instead of saying not onedomain, I thought I'd try to get the only domain I care about.

    Connect-ExchangeOnline

    Write-Host "Gathering Stats, Please Wait.."

    $Mailboxes = Get-Mailbox -ResultSize Unlimited | Select UserPrincipalName, identity, ArchiveStatus

    $MailboxSizes = @()

    foreach ($Mailbox in $Mailboxes) {

    $Domain = $mailbox.PrimarySmtpAddress -split '@'

    $ObjProperties = New-Object PSObject

    $MailboxStats = Get-MailboxStatistics $Mailbox.UserPrincipalname | Select DisplayName, LastLogonTime, TotalItemSize, ItemCount

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Display Name" -Value $MailboxStats.DisplayName

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "UserPrincipalName" -Value $Mailbox.UserPrincipalName

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Last Logged In" -Value $MailboxStats.LastLogonTime

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Mailbox Size (GB)" -Value ([math]::Round(($MailboxStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Email Address" -Value $Mailbox.PrimarySmtpAddress

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Domain" -Value $Domain[1]

    if ($Mailbox.ArchiveStatus -eq "Active") {

    $ArchiveStats = Get-MailboxStatistics $Mailbox.UserPrincipalname -Archive | Select TotalItemSize, ItemCount

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value ([math]::Round(($ArchiveStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1GB),2))

    }

    else {

    Add-Member -InputObject $ObjProperties -MemberType NoteProperty -Name "Archive Size (GB)" -Value "0"

    }

    $MailboxSizes += $ObjProperties

    $MailboxReport = $MailboxSizes | sort-Object Domain

    }

    $MailboxReport | ? { $_.Domain -contains "acme.com"} | Export-csv "C:\temp\inboxsizes.csv" -NoTypeInformation

    Get-PSSession | Remove-PSSession

    0 comments No comments

  3. Rich Matheisen 45,111 Reputation points
    2023-03-07T20:11:45.23+00:00

    I neatened up your code a little bit. I replaced the New-Object and Add-Member cmdlets and used a hash to organize the values. I also replaced the Select-Object after the Get-Mailbox and Get-MailboxStatistics. There's no need to add additional pipelines to create new objects that are ephemeral and hold values already present in another object.

    I don't have access to an Exchange O365 environment so the code hasn't been run by me.

    EDIT: added "-Properties" to Get-Mailbox. Moved Get-MailboxStatistics immediately below ForEach-Object.

    Edit2: PrimaySmtpAddress is not a property of a mailbox. Remove -Properties from Get-Mailbox. Added code to extract the primary smtp address from emailaddresses property.

    Edit3: email addresses is an array of strings. Look for uppercase SMTP as indicator of the primary address.

    Edit4: Replaced doublequote with colon in 'SMTP"' (should be 'SMTP:')

    Connect-ExchangeOnline
    Write-Host "Gathering Stats, Please Wait.."
    Get-Mailbox -ResultSize Unlimited |
        ForEach-Object{
            $PrimarySMtpAddress = "NoAddress@Local.Domain"      # used as a filler if no primary smtp present
            foreach ( $a in ($_ | Select-Object -ExpandProperty emailAddresses) ){
                if ($a -clike 'SMTP:*'){                     # a case-sensitive comparison to find the primary SMTP address
                    $PrimarySmtpAddress = ($a -split ":")[1]
                    break
                }
            }
            $MailboxStats = Get-MailboxStatistics $_.UserPrincipalname
            $props = [ordered]@{                
                "Display Name" = $_.DisplayName
                UserPrincipalName = $_.UserPrincipalName
                "Last Logged In" = $MailboxStats.LastLogonTime         # Very likely to be inaccurate
                "Mailbox Size (GB)" = 0
                "Email Address" = $PrimarySmtpAddress
                Domain = ($PrimarySmtpAddress -split '@')[1]
                "Archive Size (GB)" = 0
                "Archive Total Items" = 0
            }
            $props."Mailbox Size (GB)" = ([math]::Round(($MailboxStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",", "") / 1GB), 2))
            if ($_.ArchiveStatus -eq "Active"){
                $ArchiveStats = Get-MailboxStatistics $_.UserPrincipalname -Archive
                $props."Archive Size (GB)" = ([math]::Round(($ArchiveStats.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",", "") / 1GB), 2))
                $props."Total Items" = $ArchiveStats.totalitems
            }
            [PSCustomObject]$props
        } | Where-Object ($_.Domain -eq "Acme.com") | 
                Export-Csv "C:\temp\inboxsizes.csv" -NoTypeInformation
    Get-PSSession | Remove-PSSession