Use PowerShell to Generate HTML Reports

Before I get started, I first must give credit where credit is due. My first look at PowerShell was at a Professional Association for SQL Server (PASS) presentation by Allen White called "Using PowerShell to Get the Most Out of SQL Server." During that presentation, he demonstrated how to use PowerShell to generate HTML files. I've simply implemented what he demonstrated.

Related: End-User Reporting Tools

Where I work, we use custom jobs to monitor our environment and generate HTML reports. There are a large number of SQL Server machines, so we automate as many monitoring jobs as we can. For example, we have a job that runs daily to check for any databases that haven't been backed up. We generate an HTML file that lists any offenders so that a DBA can investigate why the database isn't getting backed up.

In SQL Server 2000, we generated the HTML file using sp_makewebtask. This stored procedure has been deprecated, so when we migrated our central reporting server to SQL Server 2008, we needed to find another solution. We decided to replace the sp_makewebtask step in our monitoring jobs with a PowerShell step. The new step runs the same T-SQL code as the old, but instead of using sp_makewebtask to generate the HTML file, it uses PowerShell's ConvertTo-HTML cmdlet.

Listing 1 shows the PowerShell step that we now include in all our monitoring jobs. (You can download this code by clicking the Download the Code Here button near the top of the page.) The code begins by defining five variables and setting up the HTML formatting. The variables, which callout A shows, specify the T-SQL command to execute, the SQL Server instance and database against which to run that command, and the title and location of the HTML report that will contain the command's results.

##setup data source$dataSource = "localhost"     ##SQL instance name$database = "DBAMonitoring"     ##Database name$sqlCommand = "exec usp_MyReport" ##The T-SQL command to execute$TableHeader = "My SQL Report"    ##The title of the HTML page$OutputFile = "C:MyReport.htm"   ##The file location##set HTML formatting$a = @""@$body = @"$TableHeader"@##Create a string variable with all our connection details $connectionDetails = "Provider=sqloledb; " +            "Data Source=$dataSource; " +            "Initial Catalog=$database; " +            "Integrated Security=SSPI;" ##Connect to the data source using the connection details and T-SQL command we provided above, and open the connection$connection = New-Object System.Data.OleDb.OleDbConnection $connectionDetails$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection$connection.Open() ##Get the results of our command into a DataSet object, and close the connection$dataAdapter = New-Object System.Data.OleDb.OleDbDataAdapter $command$dataSet = New-Object System.Data.DataSet$dataAdapter.Fill($dataSet)$connection.Close() ##Return all of the rows and pipe it into the ConvertTo-HTML cmdlet, and then pipe that into our output file$dataSet.Tables | Select-Object -Expand Rows |ConvertTo-HTML -head $a –body $body |Out-File $OutputFile

Next, the code creates a string variable that contains all the connection details. The code uses the variable to connect to the data source, then executes the T-SQL command specified in callout A against it. After the command's results are put into an ADO.NET DataSet object, the connection is closed.

Finally, the code returns all the rows from the query, piping them to the ConvertTo-HTML cmdlet. This cmdlet formats the results into an HTML page, which is piped to the output file.

The results in the HTML page essentially look the same as the results you'd receive if you ran the T-SQL command in Query Analyzer with the output set to Results to Grid. Figure 1 shows sample results from the pubs sample database. As you can see, the HTML formatting code provides a few cosmetic touch-ups.

Sample results from the pub database (click to enlarge)

As I mentioned previously, we use this PowerShell step for all our monitoring jobs. We simply modify the variables in callout A. Once the HTML file has been created, the job sends an email message that includes a link to the HTML file so that the DBAs can investigate any problems.

If we need to add columns to or remove columns from a report, we update the T-SQL code. The T-SQL code is rolled into a stored procedure, so we simply use an ALTER PROCEDURE statement to update it.

If you need to find a replacement for your sp_makewebtask jobs, you might want to take a look at  ConvertTo-HTML cmdlet. PowerShell is a very powerful tool. If you're new to it, I recommend that you check out the "Hey, Scripting Guy!" columns listed on TechNet's "Scripting with Windows PowerShell" web page. You can also check out the web-exclusive articles "Accessing SQL Server Data from PowerShell, Part 1" and "Accessing SQL Server Data from PowerShell, Part 2".