Powershell commands for large amount of mailboxes

Question

Hi everyone,

I'm looking for a simple way to generate a report for all my users including the total item size and last logon time.

This is the original command I wrote:

Get-mailbox -Filter "(RecipientTypeDetails -eq 'UserMailbox')" -resultsize Unlimited | select DisplayName,WindowsEmailAddress,@{n="TotalItemSize";e={(Get-MailboxStatistics $.Identity -resultsize Unlimited).TotalItemSize}},@{n="LastLogonTime";e={(Get-MailboxStatistics $.Identity -resultsize Unlimited).LastLogonTime}} | Export-Csv C:\Users\Administrator\desktop\users.csv

It worked like a charm in my test tenant. However, I run into several issues when doing it in the real company.

We have over 5k users in the company. I can understand the command is slow. But sometimes it just stuck or not returning results.

It would save my day if you guys can help me find a more stable script to generate the report.

I found this document by Exchange Online team about the RobustCloudCommand module: https://techcommunity.microsoft.com/t5/exchange-team-blog/updated-running-powershell-cmdlets-for-large-numbers-of-users-in/ba-p/1000628

But somehow I just can't pull it off. Can anyone please shine some light on how I should write the script?

Any help will be appreciated!

Answer 1

@Evelynn0097

You could have a try with this script:

$Mailboxes = Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited  
$data = @()  
foreach ($Mailbox in $mailboxes){  
    $temp = Get-Mailbox $Mailbox.PrimarySmtpAddress | select DisplayName,WindowsEmailAddress,TotalItemSize,LastLogonTime  
    $temp2 = Get-MailboxStatistics $Mailbox.PrimarySmtpAddress | Select TotalItemSize,LastLogonTime  
    $temp.TotalItemSize = $temp2.TotalItemSize  
    $temp.LastLogonTime = $temp2.LastLogonTime  
    $data += $temp  
    Write-Host $Mailbox.PrimarySmtpAddress "Checked"  
}  
$data | Export-csv c:\temp\Result.csv -NoTypeInformation  

For 5K mailboxes, I would suggest you split mailboxes into multiple parts, such as:

$Mailboxes = Get-User -RecipientTypeDetails UserMailbox -ResultSize Unlimited | where {$_.Department -eq "321"}  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Start by breaking this one-liner into multiple cmdlets/functions. Save your progress periodically (i.e. add to an array variable), make sure the session state is still valid. Adding some artificial delays also doesn't hurt (Start-Sleep).

Better yet, consider using the new, REST-based cmdlets where applicable (Get-ExOMailbox, Get-EXOMailboxStatistics), as they can handle reconnects and are generally more performant.

Answer 3

1. You are missing the underscore with Identity in Get-MailboxStatistics, should be Get-MailboxStatistics $_.Identity - that results in $null
2. Are you using 'old' Remote Powershell or the ExchangeOnlineManagement module? I suggest you use the EOM module (install-module ExchangeOnlineManagement), those will leverage REST calls where possible, which are much faster and robust.
3. You use a filter - Get-Mailbox offers a parameter RecipientTypeDetails which does the same (and likely has the same resulting LDAP query in the back end)
4. You are performing 2 expensive Get-MailboxStatistics calls per mailbox; it might help storing that data in a variable to refer to it later.

Scripted for readability:

$Mbx= get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited
ForEach( $M in $Mbx) {
    $S= Get-MailboxStatistics -Identity $M.Identity
    $M | select DisplayName,WindowsEmailAddress,@{n='TotalItemSize';e={$S.TotalItemSize}},@{n='LastLogonTime';e={$S.LastLogonTime}}
}

One-liner:

Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited | ForEach{ $S= Get-MailboxStatistics -Identity $_.Identity; $_| Select DisplayName,WindowsEmailAddress,@{n='TotalItemSize';e={$S.TotalItemSize}},@{n='LastLogonTime';e={$S.LastLogonTime}}}