Office365 PowerShell: How to the find out mailbox sizes in Office365 (and Exchange 2016) using PowerShell

office365 powershell

In this post I’ll look at how to find out the mailbox sizes in Office365 using PowerShell.

I’m working with a few more companies now who use Office365 so I thought I would look at how to perform a few general Exchange administration tasks in Office365 using PowerShell so that I can manage and maintain them more easily.

Note: the steps below also work for Exchange 2016.

Your first task is to connect to Office365 using PowerShell, in a previous blog post I’ve detailed the components required locally and the process of connecting up to Office365. The link below will open in a new window.

How to connect to and manage Office365 using PowerShell

Once connected lets take a look at the the mailboxes. Type Get-Mailbox

office365 get-mailboxThis lists all the mailboxes in the organisation, the server they are located on and their quota (ProhibitSendQuota). In previous examples of how to find mailbox sizes on Exchange 2010 and Exchange 2007 I’ve used Get-MailboxStatistics -server SERVERNAME, but in the image above you can see that our mailboxes are stored on many different servers.

Now while we are honing our PowerShell command let’s just pick on one mailbox to save resource utilisation and to speed things up, type Get-Mailbox -Identity Temp.

office365 get-mailbox -identity temp

Now lets pipe the command above into Get-MailboxStatistics cmdlet. Type:

Get-Mailbox -Identity Temp | Get-MailboxStatistics

office365 get-mailbox -identity temp get-mailboxstatisticsNow by default we are getting a little more information about the mailbox, but still no current mailbox size information. Type Get-Help Get-MailboxStatistics to view the help about the command Get-MailboxStatistics.

get-help get-mailboxstatisticsThe second paragraph in the image above indicates we are using the correct cmdlet. “Use the Get-MailboxStatistics cmdlet to obtain information about a mailbox, such as the size of mailbox, the number of messages it contains…”

So next lets take a look at all the data Get-MailboxStatistics returns. Type:

Get-Mailbox -Identity Temp | Get-MailboxStatistics | fl

office365 get-mailbox -identity temp get-mailboxstatistics format-list 1office365 get-mailbox -identity temp get-mailboxstatistics format-list-2So above we can see all the attributes Get-MailboxStatistics returns. TotalItemSize is the data we are looking for, but we’ll combine it with DisplayName and ItemCount in the next command. We’ll also change the output from a list to a table. Type:

Get-Mailbox -Identity Temp | Get-MailboxStatistics | Format-Table DisplayName, TotalItemSize, ItemCount -Autosize

office365 get-mailbox -identity temp get-mailboxstatistics format-list displayname totalitemsize itemcount -autosizeSo now we can see the TotalItemSize and ItemCount for a single mailbox. All we need to do is remove the -Identity switch and pipe the results from Get-Mailbox into Get-MailboxStatistics. Type:

Get-Mailbox | Get-MailboxStatistics | Format-Table DisplayName, TotalItemSize, ItemCount -Autosize

office365 get-mailbox get-mailboxstatistics format-table displayname totalitemsize itemcount -autosizeNow we have the right data but it’s a bit jumbled up, so let’s sort it into descending order. To do this we have to change from the Format-Table cmdlet to Select. Ignore the red error, it’s unrelated to the commands we are running.

Get-Mailbox | Get-MailboxStatistics | Select DisplayName, TotalItemSize, ItemCount | Sort ItemCount -Descending

office365 get-mailbox get-mailboxstatistics select displayname totalitemsize itemcount sort itemcount -descendingOk, I’ve cheated a little bit here and sorted by ItemCount, because a direct sort on TotalItemSize jumbles up MB and GB! When I had to use the command in anger last week I exported the results to CSV and then in Excel split the TotalItemSize by the space character and then did a sort. It was a few extra steps but only took a minute.

Having spent a bit more time looking at Technet and MSDN, I’ve found a solution which I’ll share with you. The brains of the next bit of code I found on MSDN. You may want to use copy and paste, basically it splits the TotalItemSize field and then does the calculation on the bytes! Type:

Get-Mailbox -Identity Temp | Get-MailboxStatistics | Format-Table DisplayName, @{name=”TotalItemSize (GB)”;expression={[math]::Round((($_.TotalItemSize.Value.ToString()).Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1GB),2)}},ItemCount

get-mailbox-identity-temp-get-mailboxstatistics-format-table-displayname-totalitemsize-gb-expression-math-round-totalitemsizevaluetostring-replace-itemcountAs you can see above I ran the command twice, first with the size converted in MB, as the mailboxsize of the Temp account is small, then with the size converted to GB which in the real world will be far more useful.

So after modifying the code a little bit more to show all users and sort in size, type:

Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{name=”TotalItemSize (GB)”;expression={[math]::Round((($_.TotalItemSize.Value.ToString()).Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1GB),2)}},ItemCount | Sort “TotalItemSize (GB)” -Descending

get-mailbox-identity-temp-get-mailboxstatistics-select-displayname-totalitemsize-gb-expression-math-round-totalitemsizevaluetostring-replace-itemcount-sort-totalitemsize-descending

Finally let’s modify the command above to export the results to CSV. Type:

Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{name=”TotalItemSize (GB)”;expression={[math]::Round((($_.TotalItemSize.Value.ToString()).Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1GB),2)}},ItemCount | Sort “TotalItemSize (GB)” -Descending | Export-CSV c:\temp\Office365-MailboxSize-Report.csv

get-mailbox-identity-temp-get-mailboxstatistics-select-displayname-totalitemsize-gb-expression-math-round-totalitemsizevaluetostring-replace-itemcount-sort-totalitemsize-descending-export-csv

You now have a csv report of all the mailbox sizes in Office365 sorted by TotalItemSize in GB.




Resources:

Technet Get-Mailbox

Technet Get-MailboxStatistics

View Mailbox Sizes and Mailbox Quotas Using Windows PowerShell

Related Posts:

1. How to connect to and manage Office365 using PowerShell

2. Exchange 2013 Initial Configuration Settings

3. How to install Exchange 2013 (SP1) on Windows Server 2012 R2

4. Exchange PowerShell: How to find the mailbox sizes in Exchange 2010

3 thoughts on “Office365 PowerShell: How to the find out mailbox sizes in Office365 (and Exchange 2016) using PowerShell

  1. Ravage

    This is a great script.
    I do have a question though.
    Following your script
    Get-Mailbox | Get-MailboxStatistics | Select-Object DisplayName, @{name=”TotalItemSize (GB)”;expression={[math]::Round((($_.TotalItemSize.Value.ToString()).Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1GB),2)}},ItemCount | Sort “TotalItemSize (GB)” -Descending | Export-CSV c:\temp\Office365-MailboxSize-Report.csv

    It gives you the total list of mailboxes. Question is, how can you filter it, say trying to get only the Top 20 Mailboxes.

    Thanks!

    Reply
    1. James Tew

      you can use the -ResultSize switch on the Get-Mailbox commandlette.

      I use it as Get-Mailbox -ResultSize Unlimited because by default it limits to the 1st 1000 users.

      You can replace “Unlimited” with a numerical value of your choice but when you run the script you will get the following message
      WARNING: There are more results available than are currently displayed. To view them, increase the value for the ResultSize parameter.

      Reply
  2. oohgodyeah

    Can someone explain to me why this cmdlet works when there is a mismatch in the count of parenthesis? I count 9 left parens “(“, but only 8 right parens “)”. How is the expression valid without closing all pairs of parenthesis? Is one of these an escape character?

    Cmdlet:
    Get-Mailbox -Identity Temp | Get-MailboxStatistics | Format-Table DisplayName, @{name=”TotalItemSize (GB)”;expression={[math]::Round((($_.TotalItemSize.Value.ToString()).Split(“(“)[1].Split(” “)[0].Replace(“,”,””)/1GB),2)}},ItemCount

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *