Use PowerShell to Create an Exchange 2010 Database Report

Use PowerShell to Create an Exchange 2010 Database Report

  • Comments 25
  • Likes

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?

—TH

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

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

{

param($fileName)

$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

{

param($fileName)

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 = $db.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

}

else

{

$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

{

param($name,$svr,$edb,$edbSize,$whiteSpace,$mbxCount,$topMailbox,$topMailboxSize,$lastBackup,$howOldBkp)

$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>"

}

else

{

$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

$smtp.send($msg)

}

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 scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • "I don’t like the Times New Roman font J"

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

  • 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 = @"

    <html>

    <head>

    ...

    "@

    add-content $filename $header

    JT

  • 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.

    thanks

    thiyagu

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

  • if you look at the full code here "gallery.technet.microsoft.com/.../d9af4a66-a908-4a3f-af10-380aa63c3a74" , in the last line, i call the sendemail function

    "sendEmail User@Domain.com User@DOMAIN.com "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

    $smtp.send($msg)

    }

  • 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.com User@domanin.com "Database Report" Server1 $filename.  Thanks again!

  • 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?

  • @brock

    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:\Users\thiyagu\Documents\DBReport.htm

    New-Item -ItemType file -Path C:\Users\ADMIN\Documents -Name dbreport.htm

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

  • 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.

  • 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

  • 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

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

    Thanks,

    Rob

  • @joek66

    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"

  • @Chandru,

    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.

    www.mikepfeiffer.net/.../creating-scheduled-tasks-for-exchange-2010-powershell-scripts

  • 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.

    ~t