Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

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


By:   |   Last Updated: 2018-11-01   |   Comments   |   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 the previous 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 my previous 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.

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" [email protected]

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 my previous 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


Last Updated: 2018-11-01


next webcast button


next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools