Friday, February 27, 2009

Email Exchange 2007 Mailbox Counts using Google Charts

Since Glen Scales ( posted some great information about getting powershell scripts to talk to the Google Charts API, I decided I would give it a try by rewriting one of our existing powershell scripts that provides a report on the Exchange mailbox counts by database.

The original script just walks through each Exchange database, get the total mailbox count using a simple forearch loop before converting (ConvertTo-HTML) and exporting (Out-File) it all to a HTML document on the local hard drive. Then it grabs the HTML document off the drive and emails it out as an HTML email to a distribution list.

After quick re-write of the loop that actually returns the mailbox counts, I started to attack working with the Google Charts API. It was surprisingly easy to get the url formatted, stick it into a HTML email (img src)and send it off.

Problem was the email would basically lock up your email client every time you opened it because (in our environment) there were so many charts it needed to download from the internet each time you opened the message.

So opted to actually submit the data to the Google Charts API in the script and then have the script download the results (.PNG Images) to a temporary folder on the hard drive.


Once I had them all downloaded I simply looped through the directory for the images and sent them as embedded images in the email. This worked as expected but the email was now around 500K; which seemed a bit much for a simple daily report.

After some digging on my favorite sites I finally figured out how to convert the .png images to .gif images.


This dramatically reduced the size (and quality) of the charts which cut the size of the email down to something more comforting.

I should have stopped there but I knew there had to be a better way to handle the images rather than downloading them all to a drive, converting them and then finally embedding the converted ones. After a bit more digging enter System.IO.MemoryStrem

$bImage = $Client.DownloadData($url)
$MS = New-Object System.IO.MemoryStream(,$bImage)
$Image = [Drawing.Image]::FromStream($MS)
$Image.Save($File, [System.Drawing.Imaging.ImageFormat]::Gif)

As always, I know there are probably some tweaks left to be made but if you'd like to Download the File you can.

No comments: