Spring cleaning at the Exchange server

One of our clients had a problem this morning -- mail just wouldn't arrive. The mail server was up, but unfortunately the c:-disk had become so filled that Exchange hit the parking break. I managed to squeeze some air from temporary files and the mail flow continued.

The root problem was that many users have huge mailboxes. As a collective excercise with the customer, i decided to try and hit them with some stats, so that only the people with actually huge mailboxes need be bothered with "clean up your mess"-nags from the administration.

So here's what i did. It's quick and dirty and it's built on mostly manual labour.

Step one - get the numbers 

Connect to the Exchange server. Fire up Exchange PowerShell. Enter the following chant:

Get-MailboxStatistics |ft displayname,totalitemsize,itemcount

Since our customer only has as many mailboxes that can fit one screen, i then selected all the text lines, copied them and pasted them into Excel twenty-ten (or whatever your poison).

Massage the data 

After pasting the lines, click the Data tab and the Text to columns button. The defaults will guess what you want, which is fixed-width data.

After getting your data into three columns, manually remove the "B" symbol at the end of each line. I'm sure you can do that with some nifty scripting, but with only sixty or seventy boxes, a succession of F2-Del-Enter pressings was the fastest. If you have a proper number of mailboxes, it's worth thinking of the automation bits.

Prettify

Give your columns some nifty headings (Mailbox, size, items). Add a new column. Call it Size in MB and give it the value of the left-neighbour cell divided by (1024*1024). Mark all of your data and choose Home -> Format as a table. Hide the column with size in bytes.

Now select the values in the column Size in MB. Select Conditional formatting -> Data bars -> Gradient fill (go for red ones for effect).

Send to the nice person in the admin at the customer and smile.