Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.
Solved
Simple example of sending SQL query result as HTML formatted table in an email using Powershell
Posted on 2016-07-20
I tried piecing together a couple scripts, one that send a single value to an email address and another that writes a query result to a text file, but I don't know Powershell well enough to get it to work.
I want to send the result of an SQL query of just a few columns as an HTML formatted table in an email message using Send-MailMessage.
Here is what I've got. It prints the table to the console and the output file, but the email fails with an error.
If there's a cleaner or better with to do this, I'd like to learn it. Thank you!
I want to send the result of an SQL query of just a few columns as an HTML formatted table in an email message using Send-MailMessage.
Here is what I've got. It prints the table to the console and the output file, but the email fails with an error.
<# connection string and SQL command to execute #>
[String] $now = Get-Date
[String] $to = 'dmegnin@careersourcebroward.com'
[String] $connectionString = 'Server=MSQLServ; Database=ITASync; Integrated Security=SSPI;'
[String] $query = @'
SELECT Provider, LoginDate, DATEDIFF(day, LoginDate, GetDate()) as Diff
FROM Logins
WHERE DATEDIFF(day,LoginDate, GetDate()) IN (37)
'@
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$format = @{Expression={$_.Provider};Label="Provider Name";width=30},@{Expression={$_.LoginDate};Label="Login Date"; width=25},@{Expression={$_.Diff};Label="Days"; width=8}
$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format
$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format | Out-File C:\Temp\ee\Providers.txt
Send-MailMessage -BodyAsHtml -Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format" -SmtpServer mail.careersourcebroward.com -From "Contract Expiration Database <csbd@careersourcebroward.com>" -To $to -Subject "Provider: $($_.Provider) logged in $($_.Diff) days ago."
$connection.Close()
If there's a cleaner or better with to do this, I'd like to learn it. Thank you!
-
7
2
10 Comments
Perhaps... Maybe take something like this and eliminate the unnecessary complications so it simply puts the table result of a query into the body of the email? I'm looking for something that would be easy for me to modify for multiple uses as a general "mail me that report" script. Maybe to schedule as an automated thing. Thanks.
#Get SQL Database objects and places them into array $ServerBook
$ServerBook = New-Object System.Data.DataSet "myDataSet"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection("Data Source=<Database Server>;Initial Catalog=ServerBook;Integrated Security=True")
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter("SELECT ServerName,Owner,SupportContact,SupportExternal,ServerDescription FROM v_ServerBook_BasicInfo", $sqlConn)
$adapter.Fill($ServerBook)
$ServerBook = $ServerBook.Tables[0] | Sort-Object -Property Owner #reorganizes $serverBook by owner
$owners = ($ServerBook.owner | Get-Unique) #stores list of unique owners and eliminates duplicates
$owners = $owners | where{$_ -ne "" -and $_ -ne $null -and $_ -ne [dbnull]::value} #eliminates null values from the list of owners
foreach($owner in $owners){
#Get the servers for the current owner
$servers = $ServerBook | where{$_.owner -eq $owner}
#Add to server list for current owner to an array
$ServerList = $servers | select ServerName, Owner, SupportContact, SupportExternal, ServerDescription
#copies the server owners name to $ownername variable
$ownerName = $owner
#Changes the format of the name to lastname, firstname is only needed if that is the format of the display name in AD
$Username = "$($Owner.Split(" ")[1]), $($Owner.Split(" ")[0])"
#pulls email address of the $username
$EmailAddress = Get-ADuser -Filter "Name -eq '$Username'" -properties emailaddress -server ads.domain.com | select-object emailaddress
#splits the $emailAddress vaiable to help eliminate extra text
$emailaddress = $emailaddress -split '[=}]' ,0
#removes extra text at the beginning of the variable
$emailaddress = $emailaddress.TrimStart("@{emailaddress")
#created CSS for formatting the table in the HTML email body
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px;}"
$style = $style + "TD{border: 1px solid black; padding: 5px;}"
$style = $style + "</style>"
#creates the HTML email body with static text and adds the dynamic table with the formatting to the middle
$message = "<html>
<p>Hello $ownername,</p>
<p></p>
<p>Static Text</p><hr>
<body>$($ServerList | ConvertTo-Html -Head $style | Out-String)</body>
<p></p>
<hr>
<p></p>
<p>Thank You,</p>
<p>Signature Block</p>
</html>
"
Send-MailMessage -To "$EmailAddress" -Subject "Bi-Annual Server Review" -Body $message -BodyAsHtml -SmtpServer "smtp1.domain.com" -Priority High -DeliveryNotificationOption OnSuccess -From "email@email.com"
}
Active today
two entirely different questions.
#2:
#2:
$serverlist ='server1','server2','server3'
$style = '<style>BODY{font-family: Arial; font-size: 10pt;}'
$style = $style + 'TABLE{border: 1px solid black; border-collapse: collapse;}'
$style = $style + 'TH{border: 1px solid black; background: #dddddd; padding: 5px;}'
$style = $style + 'TD{border: 1px solid black; padding: 5px;}'
$style = $style + '</style>'
$message1 = '<html><p>Hello '+$ownername+',</p><p></p><p>Static Text</p><hr><body>'`
$message2 = $ServerList | Out-HTML
$message3 ='</body><p></p><hr><p></p><p>Thank You,</p><p>Signature Block</p></html>'
$message = $message1 + $message2 + $message3
$message
Send-MailMessage -To $EmailAddress -Subject "Bi-Annual Server Review" -Body $message -BodyAsHtml -SmtpServer "smtp1.domain.com" -Priority High -DeliveryNotificationOption OnSuccess -From "email@email.com"
Active today
It looks like you're at least part way on your way to working this out. From your first
-Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format"
to the second where you're using
$($ServerList | ConvertTo-Html -Head $style | Out-String)
in a here-string.
If you're using ConvertTo-Html to only provide a part of a complete HTML document, then you need to use the -Fragment parameter so it doesn't generate the surrounding HTML that would make a complete doc. And of course since you're only generating the table with the cmdlet, you have to take more care in supplying the correct surrounding HTML that is well-formed.
Example, assuming data's already been collected into the $table variable.
The only thing I can't verify (and haven't actually done) is that $table (which is of type "System.Data.DataTable") can be sent to ConvertTo-HTML and be processed correctly. Seems likely, but I just haven't verified.
-Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format"
to the second where you're using
$($ServerList | ConvertTo-Html -Head $style | Out-String)
in a here-string.
If you're using ConvertTo-Html to only provide a part of a complete HTML document, then you need to use the -Fragment parameter so it doesn't generate the surrounding HTML that would make a complete doc. And of course since you're only generating the table with the cmdlet, you have to take more care in supplying the correct surrounding HTML that is well-formed.
Example, assuming data's already been collected into the $table variable.
$head = @"
<style>
TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}
TH{border-width: 2px;padding: 4px;border-style: solid;border-color: black;background-color:lightblue;text-align:left;font-size:14px}
TD{border-width: 1px;padding: 4px;border-style: solid;border-color: black;font-size:12px}
</style>
"@
$prebody = @"
<H4>SQL query results</H4>
"@
$message = @"
<!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>
$head
</head>
<body>
$($table | ConvertTo-Html -Fragment -PreContent $prebody -PostContent "<p>footer</p>" | Out-String)
<hr>
<p></p>
<p>Thank You,</p>
<p>Signature Block</p>
</body>
</html>
"@
Send-MailMessage -BodyAsHtml -Body $message -SmtpServer mail.domain.com -From "someone <someone@domain.com>" -To "other@domain.com" -Subject "neat stuff"
The only thing I can't verify (and haven't actually done) is that $table (which is of type "System.Data.DataTable") can be sent to ConvertTo-HTML and be processed correctly. Seems likely, but I just haven't verified.
Hi footech,
Unfortunately, I'm not on my way to working anything out. ;-) I found both of those examples and they just look like they are either close to what I'm looking for or at least contain part of what I need. I only started trying to learn Powershell last week when I was asked to find a way to send email alerts when our companies contracts were nearing their expiration dates.
Anyway, I didn't know a "-Fragment" parameter existed. It makes sense to me though.
David, I don't understand your sentence fragment "two entirely different questions." I'm asking for a simple example of a Powershell script to send the result of a query in an email and I provide what I have so far but that doesn't work.
Then I provide an example that I found, while googling around, that looks like it contains mostly what I want, but also has a bunch of stuff that I don't need, but don't know Powershell well enough (at all, really) to remove what doesn't fit with with I need. Thanks for understanding.
Unfortunately, I'm not on my way to working anything out. ;-) I found both of those examples and they just look like they are either close to what I'm looking for or at least contain part of what I need. I only started trying to learn Powershell last week when I was asked to find a way to send email alerts when our companies contracts were nearing their expiration dates.
Anyway, I didn't know a "-Fragment" parameter existed. It makes sense to me though.
David, I don't understand your sentence fragment "two entirely different questions." I'm asking for a simple example of a Powershell script to send the result of a query in an email and I provide what I have so far but that doesn't work.
Then I provide an example that I found, while googling around, that looks like it contains mostly what I want, but also has a bunch of stuff that I don't need, but don't know Powershell well enough (at all, really) to remove what doesn't fit with with I need. Thanks for understanding.
Active today
If your first does everything you want and you're just concerned about the error you get when you try to send an email, then you should post the error. But just looking at it I would say the below would work. And if so, then other changes as shown previously are just formatting choices.
Send-MailMessage -BodyAsHtml -Body ($table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | ConvertTo-HTML | Out-String) -SmtpServer mail.careersourcebroward.com -From "Contract Expiration Database <csbd@careersourcebroward.com>" -To $to -Subject "Provider: $($_.Provider) logged in $($_.Diff) days ago."
footech, that worked. It displays the three column table in the console window and also sends me an email with a similar table, with the correct three columns... plus columns for; "RowError", "RowState", "Table", "ItemArray", and "HasErrors."
I don't really need those extra columns, and I don't know how to suppress them.
It's not a huge thing if the extra columns can't be suppressed, but it would be nice if the email was formatted just like the console output.
Thanks.
I don't really need those extra columns, and I don't know how to suppress them.
It's not a huge thing if the extra columns can't be suppressed, but it would be nice if the email was formatted just like the console output.
Thanks.
David, thank you for taking the time to strip out the unnecessary bits from that example. I think I like the way the HTML seems to be formatted in that example so I'll have to add in the information for my database, etc. to see what it actually looks like. That was an unedited example I found in another forum, so it's not in a "testable" state, yet.
footech, I found this while Googling for how to suppress those extra columns:
But, I'm not using that particular method of creating the table, so I'm not sure how to incorporate that into the script you are helping me with. The table you created looks beautiful. I just would like to exclude those exact columns shown in the example above. Thanks again.
You should be able to simply select the data from your table that you do want when defining $body.
$body = $DataSet.Tables[0] | Select bug_id,Status,Resolution,Summary,Dea dline | convertto-html | out-string
Or, you can select everything, and then specify what you want to exclude:
$body = $DataSet.Tables[0] | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | convertto-html | out-string
But, I'm not using that particular method of creating the table, so I'm not sure how to incorporate that into the script you are helping me with. The table you created looks beautiful. I just would like to exclude those exact columns shown in the example above. Thanks again.
Got it! I added "| Select -property" and the column names I want to keep into the <body> and it's perfect.
Here is the whole working script. Thank you so much for your help. David, thanks for your input as well!
Here is the whole working script. Thank you so much for your help. David, thanks for your input as well!
<# connection string and SQL command to execute #>
[String] $now = Get-Date
[String] $to = 'david@domain.com'
[String] $connectionString = 'Server=SQLServer; Database=OurDatabase; Integrated Security=SSPI;'
[String] $query = @'
SELECT Provider, LoginDate, DATEDIFF(day, LoginDate, GetDate()) as Diff
FROM Logins
WHERE DATEDIFF(day,LoginDate, GetDate()) < (37)
'@
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($result)
$head = @"
<style>
TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}
TH{border-width: 2px;padding: 4px;border-style: solid;border-color: black;background-color:lightblue;text-align:left;font-size:14px}
TD{border-width: 1px;padding: 4px;border-style: solid;border-color: black;font-size:12px}
</style>
"@
$prebody = @"
<H4>SQL query results</H4>
"@
$message = @"
<!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>
$head
</head>
<body>
$($table | Select -property Provider, LoginDate, Diff | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | ConvertTo-Html -Fragment -PreContent $prebody -PostContent "<p>footer</p>" | Out-String)
<hr>
<p></p>
<p>Thank You,</p>
<p>Signature Block</p>
</body>
</html>
"@
Send-MailMessage -BodyAsHtml -Body $message -SmtpServer mail.domain.com -From "Contract Expiration Database <csbd@domain.com>" -To "david@domain.com" -Subject "neat stuff"
$connection.Close()
Featured Post
WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Welcome to 2018! Exciting things lie ahead in the world of tech. To start things off, we compiled great member articles on how to stay safe, ways to learn, and much more! Read on to start your new year right.
- Powershell
- Experts Exchange
- Mobile
- Net Neutrality
- Cloud Computing
- Tech Policy
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
- Document Management
- Document Imaging
- Programming
- Adobe Acrobat
- Scripting Languages
- Printers and Scanners, OCR, *PDF, *text file
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
- Document Management
- Document Imaging
- Programming
- Adobe Acrobat
- Scripting Languages
- Printers and Scanners, Images and Photos, Photos / Graphics Software, *PDF
Suggested Courses
Course of the Month8 days, 17 hours left to enroll
595 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.