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! Question

  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?


Hey, Scripting Guy! Answer 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.

Image of 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 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.

Image of raw data

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.

Image of HTML table

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.

Image of report

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.

Function writeHtmlHeader



$date = ( Get-Date ).ToString('yyyy/MM/dd')

Add-Content $fileName "<html>"

Add-Content $fileName "<head>"

Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"

Add-Content $fileName '<title>myExchangeWorld.COM Database Report</title>'

Add-Content $fileName '<STYLE TYPE="text/css">'

Add-Content $fileName "<!--"

Add-Content $fileName "td {"

Add-Content $fileName "font-family: Tahoma;"

Add-Content $fileName "font-size: 11px;"

Add-Content $fileName "border-top: 1px solid #999999;"

Add-Content $fileName "border-right: 1px solid #999999;"

Add-Content $fileName "border-bottom: 1px solid #999999;"

Add-Content $fileName "border-left: 1px solid #999999;"

Add-Content $fileName "padding-top: 0px;"

Add-Content $fileName "padding-right: 0px;"

Add-Content $fileName "padding-bottom: 0px;"

Add-Content $fileName "padding-left: 0px;"

Add-Content $fileName "}"

Add-Content $fileName "body {"

Add-Content $fileName "margin-left: 5px;"

Add-Content $fileName "margin-top: 5px;"

Add-Content $fileName "margin-right: 0px;"

Add-Content $fileName "margin-bottom: 10px;"

Add-Content $fileName ""

Add-Content $fileName "table {"

Add-Content $fileName "border: thin solid #000000;"

Add-Content $fileName "}"

Add-Content $fileName "-->"

Add-Content $fileName "</style>"

Add-Content $fileName "</head>"

Add-Content $fileName "<body>"

Add-Content $fileName "<table width='100%'>"

Add-Content $fileName "<tr bgcolor='#CCCCCC'>"

Add-Content $fileName "<td colspan='7' height='25' align='center'>"

Add-Content $fileName "<font face='tahoma' color='#003399' size='4'><strong>myExchangeWorld.COM Database Report - $date</strong></font>"

Add-Content $fileName "</td>"

Add-Content $fileName "</tr>"

Add-Content $fileName "</table>"


I put in some normal HTML headers, and then I created a style with all the custom table properties like color, font, and size.

When that is done, we simply create a new table structure and put in all the table headers, like the database name and size.

Function writeTableHeader



Add-Content $fileName "<table width='100%'><tbody>"

Add-Content $fileName "<tr bgcolor=#CCCCCC>"

Add-Content $fileName "<td width='10%' align='center'>Database Name</td>"

Add-Content $fileName "<td width='10%' align='center'>Server</td>"

Add-Content $fileName "<td width='15%' align='center'>Database File</td>"

Add-Content $fileName "<td width='10%' align='center'>Database Size(MB)</td>"

Add-Content $fileName "<td width='7%' align='center'># of Mailboxes</td>"

Add-Content $fileName "<td width='10%' align='center'>WhiteSpace(MB)</td>"

Add-Content $fileName "<td width='10%' align='center'>Top Mailbox</td>"

Add-Content $fileName "<td width='10%' align='center'>Top Mailbox Size</td>"

Add-Content $fileName "<td width='10%' align='center'>Last Full Backup</td>"

Add-Content $fileName "<td width='15%' align='center'>No Backup Since?</td>"

Add-Content $fileName "</tr>"


Quick tip: <TR> tags mean table row, and <TD> is table data or the actual cell.

Let’s analyze one line from the previous code.

"<td width='10%' align='center'>Database Name</td>"

What that exactly translates to is: Make this cell width 10%, center align the text, and the text inside the cell is Database Name.

After we have written all the table headers, we can get the actual data from the servers, then build HTML tags and add them to the HTML file.

Following is the function that calculates all the required data and then sends the information for HTML processing.

Function get-DBInfo


$dbs = Get-MailboxDatabase -Status

foreach($db in $dbs)


$name = $

$svr = $db.servername

$edb = $db.edbfilepath

$edbSize = $db.DatabaseSize.Tobytes()

$whiteSpace = $db.AvailableNewMailboxSpace.Tobytes()/1mb

$mbxCount = (Get-Mailbox -Database $db).count

$topMailbox = Get-Mailbox -Database $db | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending |Select-Object DisplayName -First 1 | Format-Table Displayname -HideTableHeaders | Out-String

$topMailboxSize = Get-Mailbox -Database $db| Get-MailboxStatistics | Sort-Object TotalItemSize -Descending | Select-Object totalitemsize -First 1

$topMailboxSize = $topMailboxSize.TotalItemSize.Value.ToBytes()

$lastBackup = $db.LastFullBackup; $currentDate = Get-Date

if ($lastBackup -eq $null)


$howOldBkp = $null




$howOldBkp = $currentDate - $lastBackup

$howOldBkp = $howOldBkp.days


writedata $name $svr $edb $edbSize $whiteSpace $mbxCount $topMailbox $topMailboxSize $lastBackup $howOldBkp



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.

Function WriteData



$tableEntry = "<tr><td>$name</td><td>$svr</td><td>$edb</td>"

#Checking if EDB size is greater than the set Threshold

#If it is greater than the table cell will be marked red, else green.

if ($edbSize -gt $dbSizeThreshold)


$edbSize = $edbSize/1mb

$tableEntry += "<td bgcolor='#FF0000' align=center>$edbSize</td>"




$edbSize = $edbSize/1mb

$tableEntry += "<td bgcolor='#387C44' align=center>$edbSize</td>"


Add-Content $fileName $tableEntry

Write-Host $tableEntry


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.

Finally, I use a simple sendEmail function to email the report.

Note: I set the Bodyhtml property to true in the code.

Function sendEmail

{ param($from,$to,$subject,$smtphost,$htmlFileName)

$body = Get-Content $htmlFileName

$smtp= New-Object System.Net.Mail.SmtpClient $smtphost

$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body

$msg.isBodyhtml = $true



Here is an image of the report the way I like to see it in my environment—all green!

Image of report

In the following image, you can see where some of the thresholds have been crossed, and therefore the field changes to red.

Image of report

Because the report is HTML, I do not need to save the report to a file and then open it in Internet Explorer. I can read it directly in the email. The report as it appears in my Inbox is shown here.

Image of report

The complete script is located in the Scripting Guys Script Repository.

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 invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Comments (26)

  1. jrv says:

    ConvertTo-HTML does all of this and more and y can have your own style sheet.

    HELP ConvertTo-HTML -full

    Note that this cmdlet can generate fragments of html as tables or lists.  All can be combined with multiple CSS style sheets to produce very complex pages.

  2. Anonymous says:

    Hi Guy ,

    I need to include Incrementalbackup in this report

  3. Anonymous says:

    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

  4. Anonymous says:

    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?

  5. sukkerfri says:

    "I don’t like the Times New Roman font J"

    That "J" could be a smiley that Outlook converts to Microsofts proprietary Windings font.

  6. jtrimble says:

    Why not use a here-string for adding all of the header lines to the HTML file?  Saves a lot of disk access and needless space in the script.  Might make it slightly easier to read too.

    $header = @"




    add-content $filename $header


  7. thiyagu says:

    hi jtrimble, yes that is good approach as well, you can also add the static content to a .txt file and then grab the entire .txt file and then write it back to the htmlheader.



  8. John says:

    Hello,  Where are the Vars $to,$from,$subject,$smtphost and $htmlfilename set in the script?

  9. thiyagu says:

    if you look at the full code here "…/d9af4a66-a908-4a3f-af10-380aa63c3a74" , in the last line, i call the sendemail function

    "sendEmail "Database Report" server1 $fileName"

    parameters passed to this function are used inside the sendmail function, "$from, $to, etc., " here is the complete sendEMail function:

    Function sendEmail

    { param($from,$to,$subject,$smtphost,$htmlFileName)

    $body = Get-Content $htmlFileName

    $smtp= New-Object System.Net.Mail.SmtpClient $smtphost

    $msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body

    $msg.isBodyhtml = $true



  10. John says:

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

  11. thiyagu says:


    i looked at the code and this is what is causing the issue:

    the first two lines of the code looks like this:

    Remove-Item C:UsersthiyaguDocumentsDBReport.htm

    New-Item -ItemType file -Path C:UsersADMINDocuments -Name dbreport.htm

    make sure the path is the same in both the lines and then add -force switch in the second line.

  12. chandru says:

    How should i use this script for my exchagne environment. Shall i copy the entire code and save it as .ps1 and run. will it run?? Please help

  13. Rob Moritz says:

    This a great script, but I want to expand on one aspect of it and I’m having difficulties doing so.

    What I’d like to have it get a report of the top 20 users along with listing the OU they are in and mailbox size that’s over 4 gig.

    Love the script!!



  14. ~T says:


    You can actually build a filename for each day, so you keep it as record , for example:

    $backupFileName = "Exchange_Backup_Report" + (get-date -f yyyyMMdd) + ".html"

  15. ~t says:


    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.…/creating-scheduled-tasks-for-exchange-2010-powershell-scripts

  16. ~t says:

    Hi Rob,

    What you are aiming for is really possible but you have to slightly expand on what is currently available on the script.

    1. to get top 20 mbxes, just modify this line

    $topMailbox = Get-Mailbox -Database $db | Get-MailboxStatistics | Sort-Object TotalItemSize -Descending |Select-Object DisplayName -First 1 | Format-Table Displayname -HideTableHeaders | Out-String

    , instead of First 1, change it to First 20

    2.for their ou , you might have to do some ad lookups or may be even one of the get-mailbox has the attribute already.

    3.mailbox size over 4 gb again is a comparision of getting the size of the mailboxes over the limit of 4gb and adding it over.

    i suggest to first start with the first 2 points and then you can run with the last one.

    let me know if you need more details.


  17. Rick says:

    I have this error

    You cannot call a method on a null-valued expression.

    At line:14 char:66

    +     $topMailboxSize = $topMailboxSize.TotalItemSize.Value.ToBytes <<<< ()

       + CategoryInfo          : InvalidOperation: (ToBytes:String) [], RuntimeException

       + FullyQualifiedErrorId : InvokeMethodOnNull

  18. Chris says:

    I am getting the same error as Rick as well as the email function is not working indicating Authentication is required.

  19. Simon says:

    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.

  20. T says:

    @rick, @Chris,

    Issue is fixed and update the script on the same repository link:…/d9af4a66-a908-4a3f-af10-380aa63c3a74

    Issue was that , sometimes the value might be null for mailbox size, i didnt account for that in the script.

    please check the new script and let me know if you face any issues.


  21. T says:


    I was using hard coded paths in the script, try the new script updated to the same repository:…/d9af4a66-a908-4a3f-af10-380aa63c3a74

    i have changed the logic to have the file created in the same folder as where you are running the script from.


  22. Denali2012 says:

    I've run the script and like the output butI seem to be having problems sending mail:

    Exception calling "Send" with "1" argument(s): "Failure sending mail."

    At C:Usersuser1DesktopScriptsServer ScriptsDiskspace2.ps1:131 char:11

    + $smtp.send <<<< ($msg)

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : DotNetMethodException

    Where in the script do I need to put in my SMTP server name, from address and to address?

    Thanks!! –

  23. Mahmoud Hanafi says:

    Hi Thiyagu  very amazing script, but I faced the following warning after run it in PowerShell, so how to modify this parameter ?

    WARNING: By default, only the first 1000 items are returned. Use the ResultSize parameter to specify the number of

    items returned. To return all items, specify "-ResultSize Unlimited". Be aware that, depending on the actual number of

    items, returning all items can take a long time and consume a large amount of memory. Also, we don't recommend storing

    the results in a variable. Instead, pipe the results to another task or script to perform batch changes.

  24. Could you have a look at this?…/the-most-amazing-exchange-2010-dashboard-report-script-monitor

    It is one of the most advance exchange reports out there. Tell me what you think. I wrote it.

  25. Ammar says:

    I have worked alot with Exchange and Powershell charts to display nice charts.

  26. Sanju says:

    Hey Thiyagu

    nice one

    I got the same sort of this but only for DBs.. Now My problem is I have more than 500 DBs and scrolling through the report for the boss is a pain.

    Is there anyway we can have this output

    The Output is some thing like below


    1.6 GB


    2.8 GB

    What I am looking is to put a break say after 10 or 12 rows so it looks some thing like the below

    DB001 DB002 DB003 DB004

    1.6GB 2.3 GB 4.56 GB 8.2 GB

    Thanks in advance for your help

Skip to main content