Wednesday, April 23, 2014

Mailbox Database Reporting Headache

Hi folks,

This morning I woke up with the terrible headache. Even sleeping one more hour wasn't very helpful, thus I came to the office.

Another headache was awaiting me. Preparing data for monthly messaging report which has been handed over to me.

One of the reports was the hardest. Preparing report on how mailbox databases are used. In particular client needs a unified report which includes information of the mailbox databases size, white space size and the number of users that actually provisioned to these databases and use all of this space.

I had 2 obstacles. First of all the way Get-MailboxDatabase command outputs information about mailbox database size and white space size like on this screenshot




It is nice but absolutely useless if you want to process data in MS Excel. So in order to output information about size only in digits you need to use expressions like these: @{N="AvailableNewMailboxSpace GB";E={$_.AvailableNewMailboxSpace.ToGB()}} and @{N="DatabaseSize GB";E={$_.DatabaseSize.ToGB()}}  for white space and total database space respectively.

Additionally my client needed the number of mailboxes in the database which is even trickier. After some exercises that even increased my headache I came up with this expression: @{N="Mailboxes Count";E={(get-mailbox -database $_.name).count}}

And of course I wanted my report to look nicer as databases are arranged in the alphabetical order. Which was pretty easy to achieve by using Sort-Order Name command.

As a result I have got the following code:

$DBs = get-mailboxdatabase -Status |Sort-Object Name
$DBs | Select-Object name,@{N="AvailableNewMailboxSpace GB";E={$_.AvailableNewMailboxSpace.ToGB()}},@{N="DatabaseSize GB";E={$_.DatabaseSize.ToGB()}},@{N="Mailboxes Count";E={(get-mailbox -database $_.name).count}} | export-csv D:\MessagingMetricsReports\SpaceMbxNumberReport.csv -NoTypeInformation

Which in turn produced a report like this:

And of course the names are sanitized for the sake of ethics.

Now it's time for me to have some aspirin, as my headache is still there. However I have got the script which can produce me the nice report any time I want it.

And of course any other database related attributes can be added to the code if more info is needed in the report.