Chat with us, powered by LiveChat

Blog

Back

Export all Mailbox Sizes to CSV using Powershell on Office 365

23 Aug 2012 by Emma Robinson

Want to save this blog for later? Download it now.

This Powershell script will connect to Office 365 and export all mailbox sizes to a CSV file. If you don’t have time to get into Powershell, why not try our Office 365 Reporting product?

Otherwise, a lot of people suggest we just run the following command:
get-mailbox | get-mailboxstatistics | select DisplayName,ItemCount,TotalItemSize | export-csv "MailboxSizes.csv"

This does almost the same thing as our script below, but offers a lot less control. Also, we love writing scripts!

The script accepts three parameters.

  1. Your Office 365 Administrator Username
  2. Your Office 365 Administrator Password
  3. An input file path (optional)

When you run the script without an input file specified it will connect to Office 365 and collect the mailbox sizes for all users in the tenant. It will output those to a file called MailboxSizes.csv with the following format
UserPrincipalName,NumberOfItems,MailboxSize
user1@cogmotive.com,1768,509.58
user2@cogmotive.com,1450,17.86
test_shared@cogmotive.onmicrosoft.com,5,0.03

Read more Optimizing PowerShell for large Office 365 tenants.

You may notice this line and wonder what is going on?
$strMailboxSize = [math]::Round(($objUserMailbox.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)

Office 365 outputs the TotalItemSize as a string rather than numbers. In Exchange 2010 we are normally able to do something like $objUserMailbox.TotalItemSize.Value.ToMB(). Unfortunately, this functionality doesn’t exist in Office 365.
PS C:\> $objUserMailbox.TotalItemSize.Value.ToMB()
Method invocation failed because [Deserialized.Microsoft.Exchange.Data.ByteQuantifiedSize] doesn't contain a method nam
ed 'ToMB'.
At line:1 char:29
+ $mb.TotalItemSize.Value.ToMB <<<< ()
+ CategoryInfo : InvalidOperation: (ToMB:String) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

PS C:\>

Here is the script:
################################################################################################################################################################
# Script accepts 3 parameters from the command line
#
# Office365Username - Mandatory - Administrator login ID for the tenant we are querying
# Office365Password - Mandatory - Administrator login password for the tenant we are querying
# UserIDFile - Optional - Path and File name of file full of UserPrincipalNames we want the Mailbox Size for. Seperated by New Line, no header.
#
#
# To run the script
#
# .\Get-AllMailboxSizes.ps1 -Office365Username admin@xxxxxx.onmicrosoft.com -Office365Password Password123 -InputFile c:\Files\InputFile.txt
#
# NOTE: If you do not pass an input file to the script, it will return the sizes of ALL mailboxes in the tenant. Not advisable for tenants with large
# user count (< 3,000)
#
# Author: Alan Byrne
# Version: 1.0
# Last Modified Date: 19/08/2012
# Last Modified By: Alan Byrne
################################################################################################################################################################
#Accept input parameters
Param(
[Parameter(Position=0, Mandatory=$true, ValueFromPipeline=$true)]
[string] $Office365Username,
[Parameter(Position=1, Mandatory=$true, ValueFromPipeline=$true)]
[string] $Office365Password,
[Parameter(Position=2, Mandatory=$false, ValueFromPipeline=$true)]
[string] $UserIDFile
)
#Constant Variables
$OutputFile = "MailboxSizes.csv" #The CSV Output file that is created, change for your purposes
#Main
Function Main {
#Remove all existing Powershell sessions
Get-PSSession | Remove-PSSession
#Call ConnectTo-ExchangeOnline function with correct credentials
ConnectTo-ExchangeOnline -Office365AdminUsername $Office365Username -Office365AdminPassword $Office365Password
#Prepare Output file with headers
Out-File -FilePath $OutputFile -InputObject "UserPrincipalName,NumberOfItems,MailboxSize" -Encoding UTF8
#Check if we have been passed an input file path
if ($userIDFile -ne "")
{
#We have an input file, read it into memory
$objUsers = import-csv -Header "UserPrincipalName" $UserIDFile
}
else
{
#No input file found, gather all mailboxes from Office 365
$objUsers = get-mailbox -ResultSize Unlimited | select UserPrincipalName
}
#Iterate through all users
Foreach ($objUser in $objUsers)
{
#Connect to the users mailbox
$objUserMailbox = get-mailboxstatistics -Identity $($objUser.UserPrincipalName) | Select ItemCount,TotalItemSize
#Prepare UserPrincipalName variable
$strUserPrincipalName = $objUser.UserPrincipalName
#Get the size and item count
$ItemSizeString = $objUserMailbox.TotalItemSize.ToString()
$strMailboxSize = "{0:N2}" -f ($ItemSizeString.SubString(($ItemSizeString.IndexOf("(") + 1),($itemSizeString.IndexOf(" bytes") - ($ItemSizeString.IndexOf("(") + 1))).Replace(",","")/1024/1024)
$strItemCount = $objUserMailbox.ItemCount
#Output result to screen for debuging (Uncomment to use)
#write-host "$strUserPrincipalName : $strLastLogonTime"
#Prepare the user details in CSV format for writing to file
$strUserDetails = "$strUserPrincipalName,$strItemCount,$strMailboxSize"
#Append the data to file
Out-File -FilePath $OutputFile -InputObject $strUserDetails -Encoding UTF8 -append
}
#Clean up session
Get-PSSession | Remove-PSSession
}
###############################################################################
#
# Function ConnectTo-ExchangeOnline
#
# PURPOSE
# Connects to Exchange Online Remote PowerShell using the tenant credentials
#
# INPUT
# Tenant Admin username and password.
#
# RETURN
# None.
#
###############################################################################
function ConnectTo-ExchangeOnline
{
Param(
[Parameter(
Mandatory=$true,
Position=0)]
[String]$Office365AdminUsername,
[Parameter(
Mandatory=$true,
Position=1)]
[String]$Office365AdminPassword
)
#Encrypt password for transmission to Office365
$SecureOffice365Password = ConvertTo-SecureString -AsPlainText $Office365AdminPassword -Force
#Build credentials object
$Office365Credentials = New-Object System.Management.Automation.PSCredential $Office365AdminUsername, $SecureOffice365Password
#Create remote Powershell session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $Office365credentials -Authentication Basic –AllowRedirection
#Import the session
Import-PSSession $Session -AllowClobber | Out-Null
}
# Start script
. Main

If you found this blog post useful, and want to refer to it again, why not download it as a PDF?