Email Alert in UNIX

UNIX for Beginners Q & A
1

Hi There

I have to wrote a script where I am able to echo a result of an SQL script, however I want to be able to send an email to myself when it is more than 0 (so whenever a value is returned) is this possible?

I tried one way from looking on the web but this didn't work, I have added my script below

duplicate_user_list=`psql cemdb admin -t -f /opt/ca/SAP_Tooling/sql/self_monitoring/duplicate_users.sql|sed '/^$/d' | awk '{print $1}'`
 duplicate_user_count=`psql cemdb admin -t -f /opt/ca/SAP_Tooling/sql/self_monitoring/duplicate_users.sql|sed '/^$/d' | wc -l`

 if [ "$duplicate_user_count" -eq 0 ]
 then
 duplicate_user_status="No Duplicate IP's Found"
 else
 duplicate_user_status="Duplicate IP's Found"

 fi

 if [ "$duplicate_user_count" -gt 0 ]
 then
 echo "mail sent to SAP Tooling Team" | mail -s "$duplicate_user_list" dummyuser@dummydomain.co.uk

The count works fine and returns what is expected

Any help will be much appreciated

Many Thanks

2

Hi,

I've had to hard-code the variables in this example of course, but something like this should work.

$ cat script.sh 
#!/bin/bash
duplicate_user_list="rod jane freddy"
duplicate_user_count=3

if [ "$duplicate_user_count" -gt "0" ]
then
        echo "Duplicate users found, mailing you the details"
        echo "$duplicate_user_list" | mail -s "Details of duplicate users" unixforum@localhost
        exit 1
else
        echo "No duplicate users found"
        exit 0
fi

$ ./script.sh 
Duplicate users found, mailing you the details
$ mail
"/var/mail/unixforum": 1 message 1 new
>N   1 unixforum@localhost Thu Mar  9 13:30  15/667   Details of duplicate users
? 1
<Some headers redacted for privacy reasons>
Subject: Details of duplicate users
To: <unixforum@localhost>
X-Mailer: mail (GNU Mailutils 2.99.99)

rod jane freddy
? d 1
? q
Held 0 messages in /var/mail/unixforum
$

Hope this helps.

1 Like
3

Hi @drysdalk

Thank you for your reply:

I have copied what you have done but now do not get any outage when executing the code.

Is there of emailing too an outlook email?

Many Thanks

4

Hi,

You will of course have to edit the code I provided so that it has your own variable defitions, and you'll need to change the line that sends the mail to have your own e-mail address in it.

But as you can see from my own run of the script which I included as sample output, it did run, and sent me an e-mail which I was able to read.

What output did you get from the script ? What type of system are you running it on, and what shell are you using ? For reference, my own system is running Linux, specifically Ubuntu 16.04 LTS x86_64, and this script is set up to use Bash as the shell.

5

On top of what drysdalk said, you might want to reduce resource consumption by running the psql query just once. One option would be to use "here strings" provided by recent shells ( ksh , bash ):

read CNT USR <<< $(psql ... | awk 'NF {T = T " " $1; CNT++} END {print  CNT, T}')
6

Hi

I am running this on Putty using a bash shell script. My code is:

duplicate_user_list=`psql cemdb admin -t -f /opt/ca/SAP_Tooling/sql/self_monitoring/duplicate_users.sql|sed '/^$/d' | awk '{print $1}'`
duplicate_user_count=`psql cemdb admin -t -f /opt/ca/SAP_Tooling/sql/self_monitoring/duplicate_users.sql|sed '/^$/d' | wc -l`

if [ "$duplicate_user_count" -gt 0 ]
then

duplicate_user_status="Duplicate Users Found Emailing The Details"

echo "$duplicate_user_list" | mail -s "Details of Duplicate Users" dummyuser@dummydomain.co.uk

else

duplicate_user_status="No Duplicate Users Found"

fi

echo "<metric type=\"IntCounter\" name=\"SQL|CEMDB|Users:Duplicate User IP Count\" value=\"$duplicate_user_count\"/>"
echo "<metric type=\"StringEvent\" name=\"SQL|CEMDB|Users:Duplicate User IP Status\" value=\"$duplicate_user_status\"/>"

My return is:

[rp1cem@wycvlapph036 self_monitoring]$ ./epagent_cem_database_metrics_TEST.sh
<metric type="IntCounter" name="SQL|CEMDB|Users:Duplicate User IP Count" value="3"/>
<metric type="StringEvent" name="SQL|CEMDB|Users:Duplicate User IP Status" value="Duplicate Users Found Emailing The Details"/>
[rp1cem@wycvlapph036 self_monitoring]$

Cheers

7

Hi,

I was meaning what is the nature of the system that's running the script ? What you're running on your own PC isn't really relevant. Is it Linux/Solaris/AIX/etc, and what shell are you using ? If you copy-and-paste in its entirety the sample code I gave you into a fresh script (with the only change being the e-mail address), does that at least run and send you an e-mail ?

8

Hi
Sorry yes Linux and bash shell.

I copied your script into a new .sh file but same issue no email being sent.

Cheers

9

Hi,

OK, thanks. All of that then points with an issue sending mail from the system using the mail command. Could you try a different e-mail address (something external to yourself, say your own GMail or Outlook.com account or something like that) to see if that gets through ? If it does, then it would be your own corporate mail system that would be filtering it out. If you get nothing there either, then that more indicates that mail is not getting out of the server the script is running on.

If you have full root access on that server, you probably want to look at /var/log/maillog and see what it reveals. It should show you what happened to the mail that was generated, and if it made it successfully off of the server or not.