XML Gives Me a Green Light (and Orange…and Red)!

Editor's Note:
In this month's Reader to Reader section, Paul Munkenbeck, third runner-up in SQL Server Magazine's 2003 Innovator Awards contest, shares his winning solution. Paul is a Senior Database Consultant for Maritz Europa Ltd. in Marlow, England.

XML Gives Me a Green Light (and Orange…and Red)!

After I designed and built a SQL Server 2000 application to capture details of server-service availability, I needed to publish reports showing hours of actual availability compared to target hours. SQL Server's XML support gave me the ability to make these reports flexible, effective, and colorful, and it saved programming resources and gave me fewer lines of code to maintain.

The SQL Server application I designed examines server event logs and selects records that show the startup or shutdown of a service such as SQL Server or Windows. From the timestamp on the event-log entries, the application calculates the length of time a service has been up or down. Each service has two availability targets: one that covers core business hours and one for 24 x 7 uptime. Various stored procedures produce reports showing the availability of individual services or groups of services measured against these two targets. These reports are important for showing compliance with our service-level agreements.

Examining the reports for services that had failed a target was tedious. I needed a "traffic-light" system to highlight problem services: red if the service missed the core business-hours target, orange if it missed the 24 x 7 target, and a soothing green for "all targets met." I didn't have any programming resources for developing or maintaining this solution in Visual Basic (VB) or ASP, but I had SQL Server 2000's XML support.

Using the FOR XML clause, I had no trouble converting the stored procedures to output XML. By configuring SQL XML Support in IIS, I could use template files to call the stored procedures and associate each template with an Extensible Stylesheet Language (XSL) style sheet. The style sheet reformats the resulting XML into an HTML table. It also examines each row of XML and adds an appropriate HTML color tag based on a comparison of the actual availability figures against the targets. The final result is a standard HTML document containing an easy-to-read color-coded report like the one that Figure 1 shows.

People on my team can open the new report in a browser and scan it visually in seconds rather than minutes. Because we have to perform this task daily, this solution has saved several hours during the year. The final output is standard HTML, so I can save the results and download it to my handheld device. Because the color-coding is applied as the last step, I can format all reports this way, regardless of any filtering or aggregation. Finally, because XSL is such a powerful tool, this solution achieves the desired result in fewer than fifty lines of code and has needed no developer resources for maintenance.

—Paul Munkenbeck
[email protected]