help with script converting text to html

$logship = SQLPS -command "Invoke-Sqlcmd 'select secondary_database,last_copied_file, last_restored_file,last_restored_date from msdb..log_shipping_monitor_secondary' -ServerInstance 'myserver.com' -Database 'msdb'" 

$logship | Select-Object | ConvertTo-Html -Fragment -Property secondary_database, last_copied_file, Last_restored_file,last_restored_date

<table>
</table>

the output is just showing table as above not the data please help. What am i missing?



November 1st, 2013 5:56pm

Hi,

Try this:

$logship = Invoke-Sqlcmd 'select secondary_database,last_copied_file, last_restored_file,last_restored_date from msdb..log_shipping_monitor_secondary' -ServerInstance 'myserver.com' -Database 'msdb'

$logship | ConvertTo-Html

You can output the data to an html file by adding "| Out-File .\file.html" after the ConvertTo-Html part.

Let me know if you have any questions.

Regards,

G-P

Free Windows Admin Tool Kit Click here and download it now
November 1st, 2013 6:53pm

Thanks I wanted to put the output into an email using powershell but need to get the output in a table for emailing. If I do what you have suggested this is what I get below. The output does not look like the value of the contents from my query which has me confused.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup>
<col/>
</colgroup>
<tr><th>*</th></tr>
<tr><td>0</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>119</td></tr>
<tr><td>0</td></tr>
<tr><td>0</td></tr>
</table>
</body></html>

November 1st, 2013 7:40pm
$path="$env:temp\logshipping_rpt.htm"

#Define an Empty Array to Hold all of the HTML Fragments
$fragments = @()

#Define the HTML style
$head = @"
    <style>
    body { background-color:#FAFAFA;
       font-family:Arial;
       font-size:12pt; }
    td, th { border:1px solid black; 
         border-collapse:collapse; }
    th { color:white;
         background-color:black; }
    table, tr, td, th { padding: 2px; margin: 0px }
    tr:nth-child(odd) {background-color: lightgray}
    table { margin-left:50px; }
    </style>
    <H2>This is a test report</H2>
"@

$results = invoke-sqlcmd -query "YOUR QUERY" -ServerInstance 'ServerName'
$fragments += "<br>This report is the output of the powershell script</i><br><br>"
$fragments += $results | ConvertTo-HTML -Fragment


ConvertTo-HTML -Head $head -Body $fragments -PostContent "<br><br><i>report generated: $(Get-Date)</i>" | Out-File -FilePath $path -Encoding ascii

#Define mail variables
$smtp = "SMTPSERVER" 
$to = "DESTINATION_EMAIL" 
$from = "SOURCE_EMAIL" 
$subject = "This is a test Email"  
       
#Format Body and send the email
$body = (get-content $path) | out-string

send-MailMessage -SmtpServer $smtp -To $to -From $from -Subject $subject -Body $body -BodyAsHtml

This script should output the result into a html file as well as emailing the result. 

Free Windows Admin Tool Kit Click here and download it now
November 1st, 2013 11:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics