I have multiple databases from various SQL Servers.
I am attempting to output SQL data to an HTML table using Powershell from these different databases/SQL Servers, and then send it via email. Although my current script is functioning, it generates multiple tables for each result in the html report. What I need is to concatenate the data into a single table (with all the data combined). I'm having some difficulty figuring out how to merge the information into just one 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];
$DB = ($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 = '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="572332242336333a3e39173b3834363b7934383a">[email protected]</a>';
To = '<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="701c1f13111c11141d191e301c1f13111c5e131f1d">[email protected]</a>';
Subject = 'Warning!!';
smtpserver = 'myrelay.test.com';
Body = $Body;
BodyAsHtml = $true;
};
Send-MailMessage @Mail;
}