Powershell : Display the table headers once at the top in a html report

I am trying to create an email report to display a list of failed/succeeded backup jobs.

Here is my script:

$servers = @(gc config\dbs.txt)
foreach($server in $servers)
{
  $dt = new-object "System.Data.DataTable"
  $cn = new-object System.Data.SqlClient.SqlConnection "server=$server;database=msdb;Integrated Security=sspi"
  $cn.Open()
  $sql = $cn.CreateCommand()
  $sql.CommandText = $(get-content config\query.sql)
  $rdr = $sql.ExecuteReader()
  $dt.Load($rdr)
  $cn.Close()
  $dt |ft Server,RunStatus,JobName >> $log
  $dt | ConvertTo-Html Server,JobName,RunStatus -head $a | ac logs\backups\report.htm    
}

Server      JobName     RunStatus
srv1        job1        success
srv1        job2        success
Server      JobName     RunStatus
srv2        job1        fail
srv2        job2        success
srv2        job3        success
Server      JobName     RunStatus
srv3        job1        fail
srv3        job2        success
srv3        job3        success
Server      JobName     RunStatus
srv4        job1        fail
srv4        job2        success
srv4        job3        success
Server      JobName     RunStatus
srv5        job1        fail

I am getting the header names multiple times as the content to the h开发者_JS百科tml file is being added after every foreach enumeration. Can I change my script so that the header [Server JobName RunStatus] is only displayed once at the top?

Also I want to know if it is possible to display the report.htm as inline content in the mail rather than an attachment. Is this possible?

Thanks in advance.

steeluser

Table headers are displayed for each enumeration because you are using ConvertTo-HTML in each enumeration :). The same applies to format-table. Try this out:

$dts = @()

@(gc config\dbs.txt) | % {
  $dt = new-object "System.Data.DataTable"
  $cn = new-object System.Data.SqlClient.SqlConnection "server=$server;database=msdb;Integrated Security=sspi"
  $cn.Open()
  $sql = $cn.CreateCommand()
  $sql.CommandText = $(get-content config\query.sql)
  $rdr = $sql.ExecuteReader()
  $dt.Load($rdr)
  $cn.Close()
  $dts += $dt
}

$dts |ft Server,RunStatus,JobName >> $log
$dts | ConvertTo-Html Server,JobName,RunStatus -head $a | ac logs\backups\report.htm