This will scrape mailbox statistics from Exchange Online and present them to you in a meaningful way. It displays the mailbox size, item counts, the user, the user’s office location, and the datacenter used for that mailbox. At the end, it will also list all of the mailbox folders in Exchange that have exceeded the 100k item limit. You can use this data to troubleshoot service problems. This will prompt for O365 credentials, what you provide will be used to connect to Exchange Online to run this report, it will also be used to send the email for the report. You need https access to outlook.com and port 587 for the office365 smtp relay. This will most likely already be the case for you. But that last requirement can be bypassed by using your own smtp relay of choice. The script requires 1 modification, and that is to edit the to and from addresses, to send the report. You can do that on lines 70 and 71.
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session
$Datacenter = @{}
$Datacenter["CP"]=@("LAM","Brazil")
$Datacenter["GR"]=@("LAM","Brazil")
$Datacenter["HK"]=@("APC","Hong Kong")
$Datacenter["SI"]=@("APC","Singapore")
$Datacenter["SG"]=@("APC","Singapore")
$Datacenter["KA"]=@("JPN","Japan")
$Datacenter["OS"]=@("JPN","Japan")
$Datacenter["TY"]=@("JPN","Japan")
$Datacenter["AM"]=@("EUR","Amsterdam, Netherlands")
$Datacenter["DB"]=@("EUR","Dublin, Ireland")
$Datacenter["HE"]=@("EUR","Helsinki, Finland")
$Datacenter["VI"]=@("EUR","Vienna, Austria")
$Datacenter["BL"]=@("NAM","Virginia, USA")
$Datacenter["SN"]=@("NAM","San Antonio, Texas, USA")
$Datacenter["BN"]=@("NAM","Virginia, USA")
$Datacenter["DM"]=@("NAM","Des Moines, Iowa, USA")
$Datacenter["BY"]=@("NAM","San Francisco, California, USA")
$Datacenter["CY"]=@("NAM","Cheyenne, Wyoming, USA")
$Datacenter["CO"]=@("NAM","Quincy, Washington, USA")
$Datacenter["MW"]=@("NAM","Quincy, Washington, USA")
$Datacenter["CH"]=@("NAM","Chicago, Illinois, USA")
$Datacenter["ME"]=@("APC","Melbourne, Victoria, Australia")
$Datacenter["SY"]=@("APC","Sydney, New South Wales, Australia")
$Datacenter["KL"]=@("APC","Kuala Lumpur, Malaysia")
$Datacenter["PS"]=@("APC","Busan, South Korea")
$Datacenter["YQ"]=@("CAN","Montreal, Quebec, Canada")
$Datacenter["YT"]=@("CAN","Toronto, Ontario, Canada")
$Datacenter["MM"]=@("GBR","Durham, England")
$Datacenter["LO"]=@("GBR","London, England")
$csvreport = "$env:TEMP\MbxStatistics.csv"
$AllMailBoxes = Get-Mailbox
$AllMailBoxStatistics = $AllMailBoxes | Get-MailboxStatistics
$ItemLimit = @()
foreach ($mboxstat in ($AllMailBoxStatistics | where {$_.ItemCount -gt 100000})) {
$ItemLimit += $AllMailBoxes | where {$_.name -match $mboxstat.displayname} | Get-MailboxFolderStatistics | where {$_.itemsinfolder -gt 100000}}
$csvreportTEMP = $AllMailBoxStatistics | Select-Object DisplayName, Identity, ItemCount, TotalItemSize, LastLogonTime, servername, databasename | Sort-Object Totalitemsize -Descending
$regex = [regex] "(?<=\().*(?=\))"
foreach ($Mbox in $AllMailBoxes) {
$csvreportTEMP -match $Mbox | Add-Member -MemberType NoteProperty -Force -Name "DataCenterLocation" -Value ($Datacenter.item(($csvreportTEMP -match $Mbox).ServerName.SubString(0,2))[1])
$csvreportTEMP -match $Mbox | Add-Member -MemberType NoteProperty -force -Name "OfficeLocation" -Value $Mbox.Office
$sizeingb = ($regex.match(($csvreportTEMP -match $Mbox | foreach {$_.totalitemsize.value.ToString()}))).Value.Split(" ")[0].Replace(",","")/1gb
$sizeingb = [math]::Round($sizeingb,4)
$csvreportTEMP -match $Mbox | Add-Member -MemberType NoteProperty -Force -Name "TotalItemSizeinGB" -Value $sizeingb}
$csvreportTEMP | Sort-Object TotalItemSizeinGB -Descending | Export-Csv -Path $csvreport -Force
#Email Instructions
$BodyofEmail = "
<br>
Here is the report (More data\columns available in the attached csv file):
$($csvreportTEMP | Sort-Object TotalItemSizeinGB -Descending | ConvertTo-Html -Property displayname, totalitemsize, servername, datacenterlocation, officelocation)
<br>
<br>
<br>
Here is a list of folders in Exchange that are pushing the 100k item limit, per folder:
$($ItemLimit | where{$_.Name -ne "Inbox"} | Sort-Object itemsinfolder -Descending | ConvertTo-Html -property Identity,Name,FolderPath,foldersize,Itemsinfolder -)"
$Subject = "Exchange Online Report"
$ToAddress = "##@####.com"
$FromAddress = "##@####.com"
Send-MailMessage -To $ToAddress -From $FromAddress -Body $BodyofEmail -SmtpServer smtp.office365.com -Credential $UserCredential -Subject $Subject -BodyAsHtml -Attachments $csvreport
#Clean up
Get-PSSession | Remove-PSSession
Remove-Item $csvreport