Q: I have a duty engineer roster like the one below. I want to send system alert messages to the engineer on duty for each day based on the date. For example, send alert emails to Liam on Monday, to Noah on Tuesday, and so on. How should I configure this?

A: You can use the "Variable Recipient" of SQLMessenger to accomplish this task.

Here we use SQL Server database to demonstrate. If the roster is stored in a different type of database, then the query statement for the variable needs to be modified.

Step 1: Create a variable named "SendTo" for the task.

Create variable for the task

The value source the variable is "Read from database". The SQL statement for the variable as follows:

select email from roster where week_day = DATENAME(weekday, getdate());

Step 2: Select the variable "SendTo" created in the previous step as the recipient for the task.

Select the variable "SendTo" as the recipient

When the system executes this task, it will use the SQL query of the variable to look up the email address of the engineer on duty for the day in your roster table and send the email to that address.

The email generated by the task

You can also use this method to send information to members of your Slack workspace.

Tip: This method requires configuring the Slack App in SQLMessenger first.

First, add a column named "slack_member_id" to the roster table to store each engineer's Slack Member ID.

Add "slack_member_id" to the roster table

Then, create a variable named "MemberID" in the task, and use it to query the Slack Member ID of the on-duty engineer for the current day from the roster table.

Create a variable named "MemberID" in the task

In the "Select Recipients" dialog, navigate to the "Send Via Slack" tab and select the variable "MemberID" as the Slack recipient for the task.

Select the variable "MemberID" as the Slack recipient for the task

Alert list sent to the on-duty engineer via Slack by the task

Note: To ensure the email body retains its formatting, we use SQLMessenger's format conversion feature to convert it to PDF before sending it to the Slack recipient.

Convert the email body to PDF format