How to set up RED Scheduler PowerShell Emailer

The purpose of this article is to explain how to use a custom PowerShell script to send Success/Failure emails from the WhereScape scheduler. 

Note that this version of the email solution requires you to modify the attached PowerShell script and hard code in some values for your environment. Future versions of this method will include the ability to parameterise these values within the RED interface. 

Pre-Requisites 

• Powershell is installed on the machine where the WhereScape scheduler service is running.
• The user account that the scheduler service is running under has the ability to run PowerShell scripts.
• You have an SMTP email server that is capable of being invoked using the Send-MailMessage PowerShell cmdlet.

Method 

1. Copy the file sendMail.ps1 into a folder on the scheduler machine. For this example, we will use C:\temp.

2. The following variables can be edited for the ODBC DSN where the RED Repository resides, in order to query the repository for failure results.

• Line 72 - Replace ODBC DSN Name: 
  • $odbc.ConnectionString = "DSN=RED_META_DSN"
• Line 73 and 74- Replace Username if using SQL Server Authentication
  • if( ! [string]::IsNullOrEmpty({RED_META_USER})) {
  • $odbc.ConnectionString += ";UID=RED_META_USER "
• Line 76 and 77 - Replace Password if using SQL Server Authentication
  • if( ! [string]::IsNullOrEmpty({RED_META_PWD})) {
  • $odbc.ConnectionString += ";PWD=RED_META_PWD"

3. If your email server requires a username and password then edit the following variables, if a user name and password are not required then comment out these lines using # and move on to step 3 

• $mailUser = spiderman@wherescape.com 
• $mailPwd = "abcdefghijklmnop" | ConvertTo-SecureString -AsPlainText -Force 

4. Now edit the following variables as per your requirements. 

• $From = spiderman@wherescape.com 
• $To = iron.man@wherescape.com 
• $Cc = nick.fury@wherescape.com 
• $SMTPServer = "your.smtpserver.com" 
• $SMTPPort = "000" 

5. Send-MailMessage variations, you will need to edit the line that sends the message depending on your requirements, depending on your mail server configuration you may also need to remove the -UseSsl from the string.

• With credentials and CC: Send-MailMessage -BodyAsHtml -From $From -To $To -Cc $cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $credential 
• With credentials no CC: Send-MailMessage -BodyAsHtml -From $From -To $To -Subject $Subject -Body $Body -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl -Credential $credential 
• Without credentials with CC: Send-MailMessage -BodyAsHtml -From $From -To $To -Cc $cc -Subject $Subject -Body $Body -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl 
• Without credentials no CC: Send-MailMessage -BodyAsHtml -From $From -To $To -Subject $Subject -Body $Body -SmtpServer $SMTPServer -Port $SMTPPort -UseSsl 

6. Amend the line for the log file if you do not want your log to be written to the C:\temp location.

• $log = "Your preferred log location\sendmail.log" 

7. Create your scheduled job in RED and set the Success (if required) and Failure commands as per the below screen shot where you replace C:\temp with the location that you stored the sendmail.ps1 file.

• Success Command: powershell -f [Your file location]\sendmail.ps1 -key $JOB_KEY$ -seq $JOB_SEQ$ -name "$JOB_NAME$" -Success 
• Failure Command: powershell -f [Your file location]\sendmail.ps1 -key $JOB_KEY$ -seq $JOB_SEQ$ -name "$JOB_NAME$" 

Appendix 

• If you choose to receive a Success email this message will not contain any content, just an empty table, if you require information in your Success email you will need to modify the metadata query contained in the sendMail.ps1 file.
• The failure email will contain an html formatted message showing the error messages that were recorded in the scheduler log and again if you require different detail then the metadata query will need to be modified for this purpose.
• If you are uncertain how to modify the query, then please log a support ticket or email your requirements to support@wherescape.com