Formatted table in an email using Powershell

natm · 2021-11-09T23:03:02.000Z

Extreme newbie, but I recently read a prior article called Sending SQL Query Results as HTML formatted Table in an Email using PS and it was super helpful, and it got me most of the way there.

I modified the code from article and piped the results to ConvertTo-Html -Fragment as suggested. It worked for the most part, but I would like to understand the code better as I am unsure of why the highlighted columns are occurring.

bf929bd5-f40a-4e15-a351-074e0917a505-Screenshot_2021-11-09_165556.png800×147 53.9 KB

Here is the code. I am assuming the issue is in the For Each syntax but not sure as I tried commenting that out … and nothing changed!!

This code defines the search string in the database tables

$SQLServer = “SQL Alias”
$SQLDBName = “TestDBName”
$SQLUsername = “TestUser”
$SQLPassword = “TestPassword”
$SQLQuery = "select schedtaskname AS ‘Task Name’,
CASE
WHEN jobstatus=1 THEN ‘Never Ran’
WHEN jobstatus=3 THEN ‘Ready’
WHEN jobstatus=4 THEN ‘Executing’
WHEN jobstatus=6 THEN ‘Error’
WHEN jobstatus=7 THEN ‘Disabled’
END AS ‘Status’,
ISNULL(swp.workerpoolname, ‘’) AS ‘Task Group’,
latestexecutionstart AS ‘Last Execution Start Date/Time’

from hsi.schedulerjob sj
inner join hsi.schedulertask st on st.schedtasknum=sj.schedtasknum
left join hsi.schedulerworkerpool swp on swp.workerpoolnum=st.workerpoolnum

Where jobstatus = 6

ORDER BY ‘Task Group’,‘Task Name’
;"

This code connects to the SQL server and retrieves the data

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; uid = $SQLUsername; pwd = $SQLPassword”

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

This code outputs the retrieved data

$table = $DataSet.Tables[0]

$html = “

”
foreach ($row in $table.Rows)
{
$html += “”
}
$html = $DataSet.Tables[0] | ConvertTo-Html -Fragment

Send the email

$smtpserver = “smtp.Server.org”
$from = “Admins@Server.org”
$to = “JoeBlow@Server.org”
$subject = “PreProd Scheduled Workflow Tasks Status”
$body = “Hi there,
Here is a report listing all the Scheduled Tasks in an Error Status:

” + $html
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml

Thanks for any assistance you can provide.

Name	Dept
” + $row[0] + “	” + $row[1] + “

Neally · 2021-11-09T23:10:15.000Z

Welcome.

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

192033ab-bb8f-4032-88a5-8e2313af0344-codebutton_small.png

Neally Bot

natm · 2021-11-09T23:31:21.000Z

Sorry for that. Hopefully this is easier to read. Appreciate the suggestion

# This code defines the search string in the IssueTrak database tables
$SQLServer = "SQL Alias"
$SQLDBName = "TestDBName"
$SQLUsername = "TestUser"
$SQLPassword = "TestPassword"
$SQLQuery = "select schedtaskname AS 'Task Name',
CASE
	WHEN jobstatus=1 THEN 'Never Ran'
	WHEN jobstatus=3 THEN 'Ready'
	WHEN jobstatus=4 THEN 'Executing'
	WHEN jobstatus=6 THEN 'Error'
	WHEN jobstatus=7 THEN 'Disabled'
END AS 'Status',
ISNULL(swp.workerpoolname, '<Unassigned>') AS 'Task Group',
latestexecutionstart AS 'Last Execution Start Date/Time'

from hsi.schedulerjob sj
inner join hsi.schedulertask st on st.schedtasknum=sj.schedtasknum
left join hsi.schedulerworkerpool swp on swp.workerpoolnum=st.workerpoolnum

Where jobstatus = 6
	
ORDER BY 'Task Group','Task Name'
;"

# This code connects to the SQL server and retrieves the data
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; uid = $SQLUsername; pwd = $SQLPassword"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

# This code outputs the retrieved data
$table = $DataSet.Tables[0]

$html = "<table><tr><td>Name</td><td>Dept</td></tr>"
foreach ($row in $table.Rows)
{
    $html += "<tr><td>" + $row[0] + "</td><td>" + $row[1] + "</td></tr>"  
}
$html = $DataSet.Tables[0] | ConvertTo-Html -Fragment

# Send the email
$smtpserver = "smtp.Server.org"
$from = "Admins@Server.org"
$to = "JoeBlow@Server.org"
$subject = "PreProd Scheduled Workflow Tasks Status"
$body = "Hi there,<br />Here is a report listing all the Scheduled Tasks in an Error Status:<br /><br />" + $html
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml

Neally · 2021-11-09T23:51:58.000Z

spicehead-9d6ao:

I am unsure of why the highlighted columns are occurring

That means the content of that is an array.
You are trying to convert an array, via ‘convertto-html’ to a string, but the contents are an array, so you get what you are seeing.

@() # declares an array in powershell

sample:

@(1,2,3,4).ToString()
----> System.Object[]

if you know it’s an array, you can e.g. join the content.

(@(1,2,3,4) -join ",").ToString()
----> 1,2,3,4

Without sample data, this is really hard.

Do you have sample data?
can you show a sample of what ‘$table’ looks like? (please sanitize if you post it)

Neally · 2021-11-10T00:10:28.000Z

Your screenshot does not seem to align with your code, you are trying to have a ‘Name’ and ‘Department’ column?

sample:

# This code outputs the retrieved data
#$table = $DataSet.Tables[0]
# ¯\_(ツ)_/¯ Dunno what's in $table

#region - mockup sample data for $table
$table = 
[pscustomobject]@{
    rows  = 
        @(
           "testName","TestDepartment"
        ),
        @(
           "testName2",@('dep01','dep02')
        )

}
#endregion

$html =
foreach ($row in $table.Rows){
    [pscustomobject]@{
        Name       = $row[0] -join ","
        Department = $row[1] -join ","
    }
}

$purdyHeader = 
@"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {text-align: center;border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #FFA500;}
TD {text-align: center;border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@

$htmlReport = $html | ConvertTo-Html -Head $purdyHeader

$body =
@"
Hi there,<br>
Here is a report listing all the Scheduled Tasks in an Error Status:<br><br>
$htmlReport
"@

$mailMsg = @{
    smtpserver = "smtp.Server.org"
    from       = "Admins@Server.org"
    to         = "JoeBlow@Server.org"
    subject    = "PreProd Scheduled Workflow Tasks Status"
    body       = $body 
    bodyashtml = $true
}
Send-MailMessage @mailMsg

This is the output:

73f84f45-df6a-43f4-b615-273c505d2416-2021-11-09_18-07-45.png

You see, without the join, I’d get the same message about the system. object

22cee73b-2c7e-4618-98c2-3b9216f1bb45-2021-11-09_18-08-09.png

13c40296-6409-44d4-b53a-de5aaffe8383-2021-11-09_18-07-58.png

natm · 2021-11-10T02:22:41.000Z

Thanks for the detailed responses, and let’s see if I can clarify. You said the code does not match the output, and that is probably my fault as I took someone else’s code and tried to use for what I wanted to accomplish. So here is what I am trying to accomplish, and maybe that is where I should start. I am running SQL query to figure out which one of over 180 timers are not running due to being in an error status. Unfortunately, there are not any notification options for these scheduled timers, so I saw a post where someone runs a PowerShell script as a scheduled task, and that kicks off an email with the scheduled task information included in the body of the email. The Query results should list each task on a separate line, and there should be 4 columns: Task Name, Status, Task Group, and the Last Execution Start Date/Time. See image below

840edbdf-3e03-4a3c-b8a1-06df0e34967b-Screenshot_2021-11-09_201052.png792×121 32.3 KB

Someone who used to work here, had a script that did this, but the formatting did not work for this as it output everything as a single line string, so I started researching & experimenting. I will include the original script here:

#Start-Transcript C:\Temp\PSTask.log

$rp1 = "C:\Temp\WFScheduledTaskErrors.csv"   

$DailyReport = "

select schedtaskname AS 'Task Name',
CASE
	WHEN jobstatus=1 THEN 'Never Ran'
	WHEN jobstatus=3 THEN 'Ready'
	WHEN jobstatus=4 THEN 'Executing'
	WHEN jobstatus=6 THEN 'Error'
	WHEN jobstatus=7 THEN 'Disabled'
END AS 'Status',
ISNULL(swp.workerpoolname, '<Unassigned>') AS 'Task Group',
latestexecutionstart AS 'Last Execution Start Date/Time'

from hsi.schedulerjob sj
inner join hsi.schedulertask st on st.schedtasknum=sj.schedtasknum
left join hsi.schedulerworkerpool swp on swp.workerpoolnum=st.workerpoolnum

Where jobstatus = 6
	
ORDER BY 'Task Group','Task Name'
;

" 

Invoke-Sqlcmd -username user -password password -ServerInstance "TestDBserver,1234" -Database Test -Query $DailyReport | Export-CSV $rp1 -Force -NoTypeInformation

$smtpServer = "smtp.test.gov"
$smtpFrom = "Admins@test.gov"
$smtpTo = "nobody@test.gov"
#$smtpTo = "sombebody@test.gov"
$messageSubject = "PreProd Scheduled WF Task that have an Error Status"

$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true

$body = Get-Content "C:\Temp\WFScheduledTaskErrors.csv" | Out-String -Stream
$body = $body -replace '"', ""
$body = $body -replace '^(\S+)\,+(\S+)\,+(\S+)', '<tr><th style = "border: 1px solid black; background: #dddddd; padding: 5px;">$1</th><th style = "border: 1px solid black;   background: #dddddd; padding: 5px;">$2</th><th style = "border: 1px solid black; background: #dddddd; padding: 5px;">$3</th></tr>'
$body = $body -replace '\n(\S+)\,+(\S+)\,+(\S+)', '<tr><td style = "border: 1px solid black; padding: 5px;">$1</td><td style = "border: 1px solid black; padding: 5px;">$2</td><td style = "border: 1px solid black; padding: 5px;">$3</td></tr>'
$body = '<body><table style = "border: 1px solid black; border-collapse: collapse;">' + $body + '</table></body>'
$message.Body = $body

$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

Remove-Variable  * -Scope Global -ErrorAction SilentlyContinue
#rm "C:\Temp\WFScheduledTaskErrors.csv" -ea ig

Here is what the output looks like using original code

d47e86ef-c7d3-4645-aeec-f41ba6c96479-Screenshot_2021-11-09_202803.png800×55 9.83 KB

I am not sure if it is easier to use my original code, or to code I modified. I do understand the concept of the array, but am clueless on the syntax of what I am trying to do.

Regards,

Nat

natm · 2021-11-10T14:03:52.000Z

So here is where I am adding the Department and Name columns? I just want the results from the SQL query and nothing else.

c1654fe4-e3d6-4154-962e-56beeda31699-Screenshot_2021-11-10_075816.png742×181 21.3 KB

So the code should maybe be:

$html =

and the foreach remains the same??

natm · 2021-11-10T23:18:40.000Z

#region - mockup sample data for $table
$table = 
[pscustomobject]@{
    rows  = 
        @(
           "testName","TestDepartment"
        ),
        @(
           "testName2",@('dep01','dep02')
        )

}
#endregion

$html =
foreach ($row in $table.Rows){
    [pscustomobject]@{
        Name       = $row[0] -join ","
        Department = $row[1] -join ","
    }
}

so I re-read your post, and I think I get what you are saying.

Initially I tried piping my query results | Out-Stream -Stream but the results were comma separated single line.

My next attempt was piping the results | Format -Table -Auto but I do not think I set up message correctly as the body does not have any results

Finally, I read the post from a few years ago here, and piped query results | Convertto-HTML and if I understand your response that turned the results into an array.

You mentioned using JOIN, but in your example above you type in the values you want in the rows in Results, but I have no idea what the values will be until the query returns the results.

In the for each, I can definitely add what columns I expect as those will always be the same.

Anyhow, I will keep trying

Neally · 2021-11-11T03:24:05.000Z

so you are trying to add ‘department’ and ‘name’ to Task Name, Status, Task Group, and the Last Execution Start Date/Time ?

again, can you please provide some raw data?

$dataset.tables[0]

what does that output look like.

in my example I just tried to mock it up when possibly could be your data, I have no idea what your data actually looks like.

I’m not following how you want to add ‘name’ and ‘department’ to the data you show in the screenshot ( Task Name, Status, Task Group, and the Last Execution Start Date/Time)

What do you want it to look like once it’s complete?

Nat M:

piped query results | Convertto-HTML and if I understand your response that turned the results into an array.

no, the other way around you feed it an object (that possibly has arrays) and ‘convertto-html’ turns it into string(s).

ConvertTo-Html returns series of strings that comprise valid HTML.

Neally · 2021-11-11T03:25:55.000Z

Nat M:

You mentioned using JOIN, but in your example above you type in the values you want in the rows in Results, but I have no idea what the values will be until the query returns the results.

doesn’t matter.

just use join.

if it’s not an array but a single element, no problem it can’t join it. if it’s an array, no problem it will separate the values by comma rather than showing system.object

Neally · 2021-11-11T03:35:41.000Z

Nat M:

Initially I tried piping my query results | Out-Stream -Stream but the results were comma separated single line.

My next attempt was piping the results | Format -Table -Auto but I do not think I set up message correctly as the body does not have any results

never heard of ‘out-stream’ is that part of the SQL module?

‘format-table’ should be avoided as it breaks the object, it’s ok as the last element in a pipe and you don’t need to process it further.

natm · 2021-11-11T18:46:41.000Z

f046b946-3271-4617-8296-2dcaff804dc7-Screenshot_2021-11-11_120832.png

Neally,

Sorry, I thought I provided the raw data earlier, but here is a screenshot and I also attached a CSV if that is easier.

As for ‘Department’ and ‘Name’? I do not want to add those values, I copied code from another post on this forum, and did not see the code until you pointed it out. All I want to show are the results formatted similar to above image.

I appreciate your clarification of how the code is creating the array, I am not intentionally trying that, I just need to understand the logic of the PS script better apparently. Just an example of a PS newbie with limited understanding.

I think the format-table was just another approach I saw on the intranet, and would rather do it right. Once I understand, I can more accurately comment my code so it can be re-used

As for the Out-Stream I think I need to clarify. Original script I tried to use got content from a CSV and then piped it to | Out-String -Stream. I just borrowed a script we use for other emails, but could not get it to work correctly ,and through research discovered other possible approaches to convert to strings

I am totally onboard with using the JOIN, but as my question clumsily tried to convey, I was inferring from your example, that it needed to have an input and your mocked up some data that would be sent to a for each clause and was not sure how to define the results from the query as the input??

Hopefully this makes more sense.

$body = Get-Content "C:\Temp\WFScheduledTaskErrors.csv" | Out-String -Stream

$html =
foreach ($row in $table.Rows){
    [pscustomobject]@{
        Task Name       = $row[0] -join ","
        Status = $row[1] -join ","
	Task Group = $row[2] -join ","
	Last Execution Start Date/Time = $row[3] -join ","
    }
}

2c78ebeb-5622-4f5a-a19d-05648d82eaf3-Query_Results.csv (1.2 KB)

Neally · 2021-11-12T00:54:00.000Z

have you tried to just pipe $table to ‘convertto-html’ ?

What do you get when you run this:

$table.gettype()

$Table = import-csv .\Query_Results.csv

$purdyHeader = 
@"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {text-align: center;border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #FFA500;}
TD {text-align: center;border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@

$htmlReport = $table | ConvertTo-Html -Head $purdyHeader

$body =
@"
Hi there,<br>
Here is a report listing all the Scheduled Tasks in an Error Status:<br><br>
$htmlReport
"@

$mailMsg = @{
    smtpserver = "smtp.Server.org"
    from       = "Admins@Server.org"
    to         = "JoeBlow@Server.org"
    subject    = "PreProd Scheduled Workflow Tasks Status"
    body       = $body 
    bodyashtml = $true
}
Send-MailMessage @mailMsg

e1e6a21d-cb2b-453a-89c7-16951d584ad9-Capture.PNG800×238 64.2 KB

natm · 2021-11-12T16:19:34.000Z

Thanks @alexw that is perfect… I am going to set it up to send only if it returns results.

I saw a post from you suggesting I use something like:

if ($DataSet.Tables[0].Count -ge 1) {
   
    $mail = @{
        From       = "smtp.server.gov"
        To         = "user@server.gov"
        Cc         = "distlist@server.gov"
        body       = $DataSet.Tables[0] | format-table -autosize | out-string
        Subject    = "PreProd Scheduled Workflow Tasks Status"
        SMTPServer = "mail"
            }
    send-mailmessage @mailMsg
}

I am hoping this works and thanks again!!

natm · 2021-11-12T19:01:58.000Z

@alexw

Not sure if I need to mark this resolved, but it is. For the sending emails only if there are results, that is a separate thing and I should probably open that separately. I really appreciate the assistance.

Nat

Neally · 2021-11-12T19:04:33.000Z

Yeah you can mark it closed.

You can test it just this should be enough, as powershell’s IF automagically checks for values

if ($table){
 # send email
}