Combining HTML output inside for loop in PowerShell

Question

I have created PowerShell script to email HTML formatted content to users. In my code, SQL query is returning 3 rows so I should get HTML formatted 3 boxes but however I am only getting the last record that is being returned by SQL query.

cls

$logFile = "C:\test\abc.txt"
$server =".test"
$Query = "SELECT PackageName,TaskName,EventCode,EventDescription from Logs WHERE EventDate BETWEEN DATEADD(hh,-4,GETDATE()) AND GETDATE() AND EventType='OnError'"
$search = "End Error" 
$Results= $null
$Result = $null


$LineNumber = Select-String $search $logFile | Select-Object -ExpandProperty LineNumber
 if ($LineNumber -gt 0)
 {

$Result = Invoke-Sqlcmd -ServerInstance $server -Query $Query 
$Results = Invoke-Sqlcmd -ServerInstance $server -Query $Query | measure-object

for($i = 0; $i -lt $Results.count; $i++)
       {


$Table = "<tr><th>Task Name</th><th>Error Code</th><th>Error Description</th></tr><td>" + $Result[$i].TaskName + "</td><td>" + $Result[$i].EventCode + "</td><td>" + $Result[$i].EventDescription + "</td></table><br><br></div></body></html>"

  $Header = "Package Name :" + $Result[$i].PackageName
    $Body = '
    <html><head>
    <style type="text/css"> 
        H1 {
            font-size: 15px;
            font-weight: bold;
            font-family: calibri;
        }
        table {
            border-collapse: collapse;
            border-spacing: 0;
            width: 100%;
            margin: 0px;
            padding: 0px;
        }
        p {
            font-size: 15px;
            font-family: calibri;
            font-weight: bold;
            border-bottom: 3px solid #3B3131;
        }
        tr:hover th {
            background-color: #c9c1c1;
        }
        th {
            vertical-align: middle;
            border: 1px solid #000000;
            border-width: 0px 1px 1px 0px;
            text-align: left;
            padding: 7px;
            font-size: 12px;
            font-weight: bold;
            font-family: calibri;
            border-width: 0px 1px 0px 0px;
            border: 1px solid #000000;
            background-color: #cccccc;
        }
        td {
            vertical-align: middle;
            border: 1px solid #000000;
            border-width: 0px 1px 1px 0px;
            padding: 7px;
            font-size: 13px;
            font-family: calibri;
            font-weight: normal;
            border-width: 0px 1px 0px 0px;
            border: 1px solid #000000;
        }
        .FailureCell {
            background-color: #FF0000;
            font-weight: bold;
        }
    </style> 
    </head><body> '

    $ResultantBody = $null
    $emailSubject = $null
    $emailBody = $null
    $ResultantBody = $Body + "<div class=""style""><H1>" + $Header +"</H1><table>" + $Table 
    $emailBody+=$ResultantBody

    }
    if ($Results.count -gt 1)
    {
    $emailSubject ="Test - Multiple - SSIS Failed"
    }
    else
    {
    $emailSubject ="Test -" + $Result.PackageName +" - SSIS Failed"
    }

   $emailTo =  "[email protected]"
    Send-EMail -Subject $emailSubject -Body $emailBody -emailTo $emailTo
 }

Output that I am getting is only one row whereas I should be getting 3 rows from source query. Can some please check and let me know why results are not getting appended.

Sid · Accepted Answer · 2020-05-04 13:32:16Z

1

This is probably where you are going wrong.

$emailBody = $null
$ResultantBody = $Body + "<div class=""style""><H1>" + $Header +"</H1><table>" + $Table 
$emailBody+=$ResultantBody

You are doing this inside a loop, appending on the 3rd line but then assigning it to $null on the 1st.

So the end result, u are loosing everything u append.

answered May 4, 2020 at 13:32

Sid

2,6861 gold badge13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Combining HTML output inside for loop in PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related