Automate Mailbox Size Reporting in Office 365

Author by Michael Epping

Office 365 grants users relatively large mailboxes at 25 GB per person, but in some organizations a few users may push the limits on a mailbox even this large.  Luckily, remote PowerShell with Office 365 allows us to generate reports on mailbox sizes for all users in an organization.  The first step is to connect to Office 365 remotely.  You can see how to do that on my blog post here http://www.concurrency.com/blog/create-a-shortcut-to-remote-powershell-for-office-365-and-exchange-online/.  Once you are connected you can run this command to get a list of all users and their mailbox sizes:
Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName,TotalItemSize,ItemCount
The problem with this is that an administrator needs to remember to run this periodically to make sure that users aren't nearing their mailbox size limits.  It's better to be proactive about generating a report like this, so let's automate it using a PowerShell script and the Windows Task Scheduler.  We can even design the script so it will email you the report on a set schedule!  Follow these steps to get this automated report set up:
  • Choose which machine you are going to create the Task on.  It should preferably be a server or workstation that is on 24/7 so the script will always run when scheduled.  The machine also needs to be Windows 7 or Windows Server 2008 R2 or later.
  • Choose an Office 365 account for generating the reports.  The account should have Organization Management permissions in Exchange Online and should also have a mailbox in Office 365.  Generally, any user account with a mailbox that is also an Office 365 Global Administrator will work.
  • We need to make sure that your machine allows unsigned scripts to run.  Open a PowerShell window as an administrator and run this:
Set-ExecutionPolicy Unrestricted
  • Enter Y at the prompt and hit enter.  This will allow your machine to run unsigned scripts.
  • Create a folder that will be used just for generating the reports in this script.  On my machine I created a new folder called C:Office365.  Make sure to change any references to C:Office365 in the following commands to reflect what you named your folder.
  • The next step is critical, and only needs to be done once per machine that you are setting this script up on.  We need to embed your Office 365 credentials in the script because the Windows Task Scheduler isn't going to be able to enter them for you.  We want to do this in a secure way since storing passwords in clear text is a bad idea, especially on a server others may have access to.  Start by opening a PowerShell window.
  • Run this command: Read-Host -AsSecureString "Enter your Office 365 password" | ConvertFrom-SecureString | Out-File C:Office365cred.txt
2013-03-27 17_06_48
  • The PowerShell window will ask you for the password to the Office 365 account you chose in a previous step.  Enter the password and hit enter.  This generates a text file that contains your password, but it is encrypted.  If you navigate to C:Office365 and open cred.txt it will look something like this:
Looks a lot better than a clear text password!
  • Now we are ready to create the script that will generate our report.  Open Notepad and then copy and paste this in:
cd C:Office365
$password = Get-Content C:Office365cred.txt | ConvertTo-SecureString
$cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList user1@domain.com,$password
$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $cred -Authentication Basic -AllowRedirection
$importresults = Import-PSSession $s
$date = Get-Date -Format "MM-dd-yyyy"
$data = Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName,TotalItemSize,ItemCount | Export-Csv "C:Office365MailboxSizeReport-$date.csv" -NoTypeInformation
$attachment = Get-ChildItem C:Office365 | Where-Object { $_.Extension -eq ".csv" } | Select-Object -Last 1
Send-MailMessage -To user1@domain.com,user2@domain.com -From user1@domain.com -Subject "Monthly Office 365 Mailbox Size Report" -Body "The monthly mailbox size report is attached to this message as a CSV file." -SmtpServer pod51011.outlook.com -Credential $cred -UseSsl -Attachments $attachment
Remove-PSSession $s
  • Change user1@domain.com  in line 3 to your Office 365 account's email address.
  • Change user1@domain.com and user2@domain.com after -To in line 10 to the recipients you would like the report to be emailed to.
  • Change user1@domain.com after -From in line 10 to Office 365 administrator you selected in earlier steps.
  • Make sure you change any references to C:Office365 to reflect the folder that you chose to store data in.
  • The last thing we need to change is the pod51011.outlook.com part.  This happens to be my account's SMTP relay server in Office 365, but your Office 365 account's may be different.  Log into mail.office365.com with your Office 365 account.
  • Click on Options in the upper right corner and then See All Options.
2013-03-27 17_19_16
  • Next, click on Settings for POP, IMAP, and SMTP access.
2013-03-27 17_20_13
  • A window will pop up with an SMTP setting area.  Record the Server name listed here because it will be the server you enter into the script.
2013-03-27 17_21_33
  • Change the script so that the pod51011.outlook.com value is changed to whatever your recorded in the previous step.
  • Save the file to C:Office365.  I chose to call mine MailboxSizeReport.
  • Change the file's type from .txt to .ps1.
  • Next, open the Windows Task Scheduler.  Click Create Task. in the right pane.
2013-03-27 17_26_15
  • Give the task a name on the General tab.  Choose Run whether user is logged on or not.
2013-03-27 17_28_53
  • Go to the Trigger tab and select New.
  • Make sure that On a schedule is selected at the top of the New Trigger page.  I am going to choose to schedule my script to generate reports on the first of every month, but you can specify it to run daily, weekly, or at some other interval.  Make sure the Enabled box is checked and click OK.
2013-03-27 17_31_06
  • Go to the Actions tab and click New.
  • Make sure the Action selected is Start a program.  Put powershell.exe in the Program/script box and put -file "C:Office365MailboxSizeReport.ps1" in the Add arguments box.  Click OK.
2013-03-27 17_34_07
  • When you are back on the Actions tab click OK.  A credentials box will pop up.  These are your AD credentials that will be used to run the task on this machine, not necessarily your Office 365 credentials (although they could be the same if you are using AD FS and Dirsync).  They should preferably have administrator permissions on the machine you are creating the task on.
2013-03-27 17_37_33
  • Click OK.
At this point we should be done!  It's always a good idea to test though.  Click on Task Scheduler Library in the left pane of the Task Scheduler MMC and find the task you just created.  If it doesn't show up you may need to click refresh in the right pane.  Right click the task and select Run.  If successful, the recipients you specified in the script will each receive an email with an attached CSV file that conveniently includes the date the report was generated in the file name.  This file will also be stored in C:Office365 on the machine running the task. 2013-03-27 17_42_15 If you open the report in Excel you will see a list of users, the size of each user's mailbox, and the number of items in each user's mailbox.  I only have four users listed in my example below, but larger organizations will have hundreds or thousands of rows of users, which can be manipulated in Excel as needed. 2013-03-27 17_44_08  
Author

Michael Epping

Systems Engineer