Search code examples
htmlsql-serverwindowspowershellautomation

Append SQL result to HTML table via Powershell

I have multiple databases from multiple SQL Servers.

I am trying a output SQL data to an HTML table using Powershell from these databases/SQL Servers, then send a mail. The script I made works, but it will output multiple tables for each result in the html report. What I want is to append the data to the same table (Only 1 table containing all data). Struggling a bit on how to concatenate the data to only 1 table.



#Set flag to 0 for email notification (if any)
$alert= 0


$List = Get-Content -Path "C:\Users\testadmin\Documents\Log\serverlist.txt"


Foreach($Server in $ServerList){

$SQL = ($Server -split '=')[0] 
$ = ($Server -split '=')[1]



$Query = "select host, Service, Status from Table with(nolock)"

$Value = Invoke-Sqlcmd -ServerInstance $SQL -Database $DB -Query $Query 




foreach($mylocal in $Value ){

    $hostname = $mylocal.host
    $Service= $mylocal.Service
    $Status = $mylocal.Status 



         if($Status -ne 'Running'){
         
         $alert= 1

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

          <style>
            TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
            TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
            TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
            
            table.center {
            margin-left: auto; 
            margin-right: auto;
            }
          </style>

          <title>Warning</title>
          </head> 
          <body> 

          <h3>Apps are Stopped !</h3>

          <h4>Timestamp: $(Get-Date)</h4>
          
        <table>

          <tr>
            <th>SQL</th>
            <th>DB</th>
            <th>Service Status</th>
          </tr>

          <tr>
            <td>$SQL</td>
            <td>$DB</td>
            <td>$Status</td>
          </tr>

          </table>
          </body>
          " 
         } 


         else{
            Write-Host ("no alert") 
         }
  }  

}

  if($alert-eq '1'){

          $Mail = @{
            From = 'testadmin@local.com' 
            To = 'localadmin@local.com'
            Subject = 'Warning!!'
            smtpserver = 'myrelay.test.com'
            Body = $Body
            BodyAsHtml = $true
          } 


          Send-MailMessage @Mail
        }
Solution

  • You need to create your base html (the one containing the head / body / table title / table headers) outside of the loop. In the loop, you create only the table cells.

    From there, you build your complete HTML.

    Example

    
# Just some data... I used calculated field to have the same field name as you.
$Value = Get-Service | Select @{'Name' = 'host' ; 'Expression' = { "localhost" } },
@{'Name' = 'Service' ; 'Expression' = { $_.ServiceName } }, Status


$HtmlTemplate = @{
    Base_Date_TableLoop          = @'
<head> 
          <meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>

          <style>
            TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
            TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
            TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
            
            table.center {
            margin-left: auto; 
            margin-right: auto;
            }
          </style>

          <title>Warning</title>
          </head> 
          <body> 

          <h3>Apps are Stopped !</h3>

          <h4>Timestamp: {0:Date}</h4>
          
        <table>

            <tr>
                <th>SQL</th>
                <th>DB</th>
                <th>Service Status</th>
            </tr>

          {0:TableLoop}

          </table>
          </body>
'@
    TableLoop_SQL_DB_Status = @'
        <tr>
            <td>{0}</td>
            <td>{1}</td>
            <td>{2}</td>
        </tr>
'@
}

 $Date = Get-Date
$Table = [System.Text.StringBuilder]::new()




foreach ($mylocal in $Value ) {

    $hostname = $mylocal.host
    $Service = $mylocal.Service
    $Status = $mylocal.Status

    $Table.AppendLine(($HtmlTemplate.TableLoop_SQL_DB_Status -f $hostname,$Service,$Status)) | Out-Null

}

$HtmlBody = $HtmlTemplate.Base_Date_TableLoop.Replace('{0:Date}',$Date).Replace('{0:TableLoop}',$Table.ToString())

$HtmlBody | Out-File 'SomeReport.html'

    Design choices

    I like to have all my html elements in a single variable. In the previous example, I used $HtmlTemplate to store all the pieces. These pieces are not meant to be edited directly but serves as my building blocks.

    For the elements themselves, I personally like to have something along the lines of Base_Date_TableLoop where each underscores delimit something I need to replace so it is easier later on to do the replace without having to think about what goes where.

    Additional notes

    I used a mix of '{0}' with the -f for the loop piece and {0:TableLoop} with .replace for the base html piece. This is because the base html contains brackets {} used for the format operator, which would need to be doubled down for the -f operator to ignore them. Since I don't want to arbitrarily modify the html, I use the .replace .Net method, which allow me to replace the things I need without having to modify the html completely. Also, .Replace is used here instead of -Replace because the latter is a Regex operator, and I don't want that.