Use PowerShell to Create an Exchange 2010 Database Report

Summary: Learn how to use Windows PowerShell to create an HTML report of Exchange 2010 databases by using conditional formatting and thresholds.

Hey, Scripting Guy! I need to learn how to produce reports on my Exchange 2010 databases. Is this something that I can do by using Windows PowerShell?

—TH

Hello TH,

Microsoft Scripting Guy, Ed Wilson, here. It is time for a Guest Blogger Week. You will love the lineup we have this week. Today our guest is Thiyagu.

Here is what Thiyagu has to say about himself:

I work for a large investment bank as an Exchange administrator. I have been scripting for more than seven years. I am good at VBScript, but when I first laid my eyes on Windows PowerShell, I realized this is the coolest thing ever in scripting. I automate nearly all of my Exchange and Active Directory tasks. I am also good at WMI, ADSI, and generating reports. I write a blog at www.myExchangeWorld.com. I have developed custom apps in C# for automation. I love to automate things. Scripting and Exchange Server are really my passions.

Today I want to show you how to get a colorful report for your daily export jobs or any important reports that you might be running.

Who likes raw data such as the output seen in the following image? OK, maybe administrators (like me) like it, but this is not something that you can give to your boss.

Yeah, it looks nice if you play around with it; but when you want to produce a nice report, it does not work very well. That’s what I want to show you today—how to produce a better report.

Displaying output is one of the skills in the 2011 Scripting Games. Thiyagu’s information will be useful study material for those who are contemplating entering this year’s event. Other areas of concentration are detailed in the 2011 Scripting Games Study Guide.

Most you might already be thinking, “Well, there is my little friend called “Convertto-HTML,” and then I have my nice little HTML report (see the following image).” To be honest, I don’t like “Convertto-HTML” that much.

I don’t like the Times New Roman font J…maybe it’s just me (sorry if I offended any Times New Roman font lovers). Refer to this PowerShell Tip of the Week for discussion about customizing the Convertto-Html cmdlet. Keep in mind that in Windows PowerShell 2.0, the power of the cmdlet has expanded significantly.

I like to have more control over those HTML tags, and I want to customize them per our needs.

What I am planning to do is to write a script that will generate an HTML report about your Exchange databases and run through the following preconfigured thresholds:

· Database Size

· Mailbox Count

· Top Mailbox Size

· Backup Days

If any of these thresholds is exceeded, it will be marked with red in your HTML report.

This way, just by glancing at your report, you will know where your problems are. You can see the different thresholds that I setup here.

You can tweak them to suit the needs of your environment.

After this, what I have to do is to create a file, and then add an HTML header with head, body, and style tags.

It might look scary, but it is not—please don’t lose heart J because you don’t really have to fully understand this. This is just information that you need in the HTML file for the colors and fonts. I welcome anyone who want to learn it to give it a try…it is really easy.

The previous function is self-explanatory—I loop through all databases and calculate the required information. Then in the last line, I send the values to another function called writeData as shown here.

Actually, I could add more to that function; I limited the information to show one item where I perform a comparison. If you notice, this comparison is for checking edbSize. I am using a simple condition to check if the size is greater than a particular threshold.

If the value is greater, the bgcolor property of the tag changes to red; otherwise, it is going to be green.

I guess you are now getting the gist of it.

After all the conditions are checked, all those HTML tags are stored in the variable $tableEntry and then written to the HTML file.

TH, that is all there is to use Windows PowerShell to generate Exchange database reports. Thank you, Thiyagu, for taking the time to share your knowledge and your script with us. Guest Blogger Week will continue tomorrow when Microsoft MVP, Shay Levy, will be our guest.

I have the same problem with this script when I run it as a scheduled task in that the emailed report keeps adding in the content over and over so I get more copies in every email it sends. I tried the -force parameter as you stated but that did not work. Any ideas as to how to get this to work in a scheduled task with just one copy of the report just like when I run it manually in a powershell window? Thanks

Hi, thank you this is great. One thing I ran into though. Every time I run the script (scheduled task) I get appended reports. Somehow it remember the last report so after running the script each morning for 5 days I get one email with 5 reports in it. Anyway to clear the previous report so that I just get the current data?

Thanks for the followup, but I still do not see where a $to = "EmailAddress@something.com" Statement? I understand the Function has Parameters. Oh now I see it when you call the function you are setting them with user@Domain.comUser@domanin.com "Database Report" Server1 $filename. Thanks again!

Mike has written a nice post on how to schedule scripts which are specific for Exchange 2010, it tells you how to load and connect to the exchange shell , so you get all the cmdlets loaded for ur powershell session, so the scripts can work properly.

I'm getting the same problem with each run of the script appending its output to the previous runs. I'm thinking this is a problem with scoping of variables? It's a very useful script and the output looks great.