Scheduling SQL Server Tasks on Linux Part 3: Sending E-Mail Alerts with Cron

By: Daniel Farina |  |   Related Tips:More >SQL Server on Linux

Problem

You work as a SQL Server Database Administrator at a company that is starting to use Linux as its platform to run SQL Server . You need to automatize some tasks which we covered in theprevious tip. You know that you can set the MAILTO variable to send the execution results of Cron to your email, but now you need to send the output of specific jobs to people in other departments of your company like the development team. Since you take security seriously, you don’t want them to receive information for specific jobs. In this tip I will provide the ways you can achieve this.

Solution

The principle of least privilege, states that users should only have access to what they need and nothing more. When we apply this concept to Cron security, we realize that we can’t send job execution logs to anyone other than those who need to know. Think about this, if some malicious user knows the jobs that run on your system and at which time the jobs execute, this could be a serious threat. Of course, we as Database Administrators can’t afford that. We have to take care of security, but also we may have to comply with specific regulations like SOX (Sarbanes-Oxley Act), HIPAA (Health Insurance Portability and Accountability Act) or if your company deals with payments made with credit cards. Almost any company that receives payments will accept credit cards. So, as a matter of fact, processing credit card data in your environment for sure is something that happens very frequently.  In other words, it is necessary for your company to be complaint with PCI DSS (Payment Card Industry Data Security Standard) standards.

So, in order to be compliant with these regulations and security standards what can we do if we need to send the output of a job to a particular user or group? Fortunately, Linux gives us the tools to deal with this matter, so we can protect our servers.

Sending E-Mail Alerts with Cron

Since you have read myprevious tip on this series you know that you can set the MAILTO variable with the emails that you want to receive the execution logs like in the next script.

<a href="/cdn-cgi/l/email-protection" data-cfemail="622f232b2e362d5f06030c0b070e22060d0f030b0c4c010d0f">[email protected]</a>,<a href="/cdn-cgi/l/email-protection" data-cfemail="630706151706020e23070c0e020a0d4d000c0e">[email protected]</a>

In the previous example, we are instructing Cron to send an email to Daniel and the development team.

Additionally, you know that if you declare the MAILTO variable as empty, Cron won’t send an email at all.

MAILTO=””

But as I told you in the introduction, we need more than an “All or Nothing”. Fortunately for us, we can take advantage of command redirection.

Sending an E-Mail on a Job by Job Basis

There is a trick we can do to send an email for a specific job. To do so, you first need to delete that email from the MAILTO variable. Suppose you want the execution result of the job which script is webupdate.sh to be delivered to the Dev Team whose email address is[email protected], and you don´t want the execution result of any other job to be sent to them. The first thing you have to do is remove the[email protected] address from the MAILTO variable. Then you have to add a pipe at the end of that specific job invoking the mail command as follows.

0 0 * * *  webupdate.sh | mail -s "Mail Subject" <a href="/cdn-cgi/l/email-protection" data-cfemail="553130232130343815313a38343c3b7b363a38">[email protected]</a>

That item in the crontab file will run the webupdate.sh script every day at midnight and send an email to the development team. But be aware that the email will be sent to the addresses in the MAILTO variable. A word of advice is that you must have installed the mailutils package. In case you don’t have it you can install it by running the next command with root permissions.

apt-get install mailutils

Blocking the E-Mail of a Specific Job

There is also a way that allows you to send the results of the execution of all jobs and avoid sending the execution result of a specific job. In Linux we can redirect the output of a program or script. In other words, we can use this as needed and redirect the output of a specific job.

Let’s consider the previous case, but with a difference. Suppose you want the execution results of the webupdate.sh script to not be delivered to anyone. In such case, you just simply have to add the following command at the end of the job definition:  ‘>/dev/null 2>&1’ like in the next example.

0 0 * * *  webupdate.sh >/dev/null 2>&1

Now I will explain each step of the “>/dev/null 2>&1” extra command for redirection.

The ">" character is used in Linux for redirection of the output of a given command to the input of another command. So, at first sight we have two redirections: “webupdate.sh >/dev/null” and “2>&1”.

The /dev/null is known as the null device. What it does is to discard all data written to it, but reports that the write operation succeeded. In layman's terms, it is just like a black hole that gently accepts everything you throw at it.

The number 2 refers to the second file descriptor of the process. It is the stderr device which is where the error messages are being sent.

In the next table you can see each file descriptors:

File Descriptor Number File Descriptor Name 0 Standard Input 1 Standard Output 2 Standard Error

The &1 value instructs Cron that the target of the redirection should be the same location as the first file descriptor which is the stdout device.

In other words, > /dev/null 2>&1 first redirects stdout to /dev/null and then redirects stderr also to the /dev/null device. This effectively silences all output (regular or error) from the given job and since there is no output, nothing will be sent by email.

Sending an E-Mail Only when a Job Fails

If you read myprevious tip in this series, you know that Cron sends an email regardless if the job succeeded or there were errors on its execution. In case you want to receive an email only when a job fails you can redirect the standard output to /dev/null as follows:

0 0 * * *  webupdate.sh >/dev/null

Next Steps

• Read my previous tips on this series,tip1 andtip2.
• New on Linux? This is where you must begin: Getting Started with SQL Server on Linux .
• After reading the previous tip I suggest that you read 7 Things Every SQL Server DBA Should Know About Linux . 
• On the following tip you will find the most used commands for SQL Server DBA’s: Top 10 Linux Commands for SQL Server DBAs .
• Also you want to learn other Linux administration basics you can read my previous tips on which I cover how to check forCPU Usage,Disk I/O andDisk Space.
• For more information regarding SQL Server and Linux check SQL Server on Linux Tips Category .
• Do you want to send email alerts trough SQL Server Agent? If so, you can start here: Setting Up Alerts for All SQL Server Agent Jobs .

Last Update: 2018-11-01

About the author