tmac tmac - 1 year ago 142
SQL Question

extra columns in powershell table

I have a script that reads from a MySQL database using a DataAdapter. There are 3 columns in the table idInstance, IPAddress and the auto id column. I only want IdInstance and IPAddress to show in report, But when I run the script and out put to table I get 7 columns the extra 5 are RowError, RowState, Table, ItemArray and HasErrors. How can I show only the 2 columns I want IdInstance and IpAddress columns. See image of extra columns. Moreover the output to the console is correct it only shows idinstance and ipaddress. so it must be something in the code for building the HTML table?

function Execute-MySQLQuery([string]$query) {
$command = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $conn)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet, "data")
return $dataSet.Tables["data"]

# So, to produce a table of results from a query...
$Header = @"
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TR:Nth-Child(Even) {Background-Color: #dddddd;}
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;}

Patch Report
$Pre = "<h1>Patch Report </h1>"
$Pre += "<h2>These systmes are Not Running Patch Script</h2>"
$Post = "Run on "
$Post += Get-Date

$query = " SELECT prodinstances.Idinstance, prodinstances.IPAddress
FROM prodinstances
WHERE prodinstances.Idinstance
NOT IN (select prodwinupdates.ServersId FROM prodwinupdates) order by IPAddress ASC"

$result = Execute-MySQLQuery $query
$strfound = ("Found " + $result.rows.count + " Systems that are not currnetly running Patch Audit Script.")
$result | Format-Table

$emailSmtpServer = "mysmtpserver"
$emailFrom = ""
$emailTo = ""
$emailSubject = $strfound
$emailBody = $result | ConvertTo-HTML -Head $Header -PreContent $Pre -PostContent $Post | Out-String
Send-MailMessage -To $emailTo -From $emailFrom -Subject $emailSubject -Body $emailBody -BodyAsHtml -SmtpServer $emailSmtpServe

enter image description here

Answer Source

Specify the properties you want ConvertTo-Html to include with the -Property parameter:

$emailBody = $result | ConvertTo-HTML -Property idInstance,IPAddress -Head $Header -PreContent $Pre -PostContent $Post
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download