Thursday, May 1, 2014

Mailboxes Reporting on Screen and In Excel

Hi folks,

I'm extremely happy to share with you some more of my experience that I recently had with Microsoft Exchange and scripting for it. This should be working for both Exchange 2010 and 2013.

I have been asked by my customer to automate reporting of the number of all mailboxes by their types. You know managers don't care much for the technical details but only for digits, digits and digits.

The commands I was using can be found anywhere in Technet or any good book on Exchange 2010/2013. However I'm still happy to remind them to you.

(Get-mailbox -resultsize unlimited).count reports total number of all kinds and sorts and breeds of mailboxes in your org. (Get-mailbox -resultsize Unlimited -RecipientTypeDetails RoomMailbox).count reports total number of room mailboxes in the or. You can replace RoomMailbox with EquipmentMailbox, LegacyMailbox, LinkedMailbox, UserMailbox, DiscoveryMailbox, SharedMailbox and many others. Of course all of them for Exchange 2010/2013 you can find here http://msdn.microsoft.com/en-us/library/microsoft.exchange.data.directory.recipient.recipienttypedetails(v=exchg.150).aspx , so I won't spend much time on it

If you have more than 1000 mailboxes in your org don't forget to add -resultsize Unlimited or type your magic number.

The tricky part was how to output all these values to a nicely formatted file. Of course commands like Out-File or even >filename.txt can be used. However I wanted it to look a lot nicer than a simple TXT file. My aim was to produce a CSV file that can be then processed in Excel, should this be needed. So I decided to export every breed of mailbox to its variable and then export variables into a CSV files, which I didn't know how to do. But as my ex-boss would always say "Good IT guy needs 2 things brain and Google". My brain helped me to define what I need, time was for Google to work. So I ended up here
http://social.technet.microsoft.com/Forums/en-US/97964fa0-e606-4389-8049-de07b419acac/how-to-export-multiple-variable-into-csv-file-using-exportcsv?forum=winserverpowershell . I tried the first response and it did the magic.

As said previously, I wanted the results to be reported on screen for me and in CSV file for the client. So my magic script ended up looking like this. (If you want any of the sections may be commented you. However I keep both which gives me a signal that the commands are actually executing there without error and I'm getting the result).


#Define variables for mailbox types

$TotalMbxText = "Total Mailboxes"
$TotalMbx = (Get-mailbox -resultsize unlimited).count
$RoomMbxText = "Room Mailboxes"
$RoomMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails RoomMailbox).count
$EqpMbxText = "Equipment mailboxes"
$EqpMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails EquipmentMailbox).count
$LgcMbxText = "Legacy mailboxes"
$LgcMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails LegacyMailbox).count
$LnkMbxText = "Linked mailboxes"
$LnkMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails LinkedMailbox).count
$UserMbxText = "User mailboxes"
$UserMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails UserMailbox).count
$DscMbxText = "Discovery mailboxes"
$DscMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails DiscoveryMailbox).count
$SharMbxText = "Shared mailboxes"
$SharMbx = (Get-mailbox -resultsize Unlimited -RecipientTypeDetails SharedMailbox).count


#Output variable values to screen

$TotalMbxText,$TotalMbx
$RoomMbxText,$RoomMbx
$EqpMbxText,$EqpMbx
$LgcMbxText,$LgcMbx
$LnkMbxText,$LnkMbx
$UserMbxText,$UserMbx
$DscMbxText,$DscMbx
$SharMbxText,$SharMbx


#Output variables to CSv files

$table=@"
MailboxType,Number
$TotalMbxText,$TotalMbx
$RoomMbxText,$RoomMbx
$EqpMbxText,$EqpMbx
$LgcMbxText,$LgcMbx
$LnkMbxText,$LnkMbx
$UserMbxText,$UserMbx
$DscMbxText,$DscMbx
$SharMbxText,$SharMbx
"@

#export to a csv file
$table | Set-Content D:\MessagingMetricsReports\MailboxesByTypeNumber.csv

And the outputs looked like this:

On the screen:




In Excel: