How to send an email from unix script

Question

#! /bin/bash
`sqlplus -s <username>/<passwd>@dbname` << EOF   
set echo on  
set pagesize 0  
set verify off  
set lines 32000  
set trimspool on  
set feedback off  
`SELECT   starts_with, SUM (total_records) total_records
    FROM (SELECT ID,
                 (CASE WHEN ID LIKE '2%'  THEN '2____'
                       WHEN ID LIKE '3%'  THEN '3____'
                       WHEN ID LIKE '99%' THEN '99____'
                  END
                 ) starts_with,
                 total_records
            FROM tr
            where ( id like '2%' or id like '3%' or id like '99%'))
               WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440
               AND tr.TIMESTAMP <= SYSDATE - 15 / 1440
GROUP BY starts_with;
`  
exit;  
EOF

1.Firstly, how can i schedule the script to run after every 1 hr ?

2.Secondly, the requirement is to send an email on condition such as :

if total_records < 1, then an UP ALERT notification email should be send to [email protected].
And as soon as the total_records gets greater than 1, then again DOWN ALERT notification email is send to [email protected].

NOTE : Till total_records > 1, no such above thing (pt.2) to be followed. Only, when it total_records < 1, we need to follow step 2.

Here, total_records represents transactions, so it will change in every hour (as the tr.TIMESTAMP signifies). tr represents transaction table.

Answer 1

For (1) look at the man page for cron(1). This will do what you want.

For (2) you will need to either issue the emails from within Oracle (see the DBMS_MAIL package) or redirect the output from SQLPlus to a file and process the file in a shell script. See the documentation for the mail(1) command for details on how to do this.

Answer 2

Take a look at the manpages of crond and sendmail.

Answer 3

1) To run a command every hour just edit your user crontab and add:
0 * * * * command

2) This could be done in a lot of ways. Is the UNIX mail server configured?

Answer 4

To run the script every hour at the 0 minute mark, add an entry to your crontab like this:

• Type crontab -e to edit your crontab.

• Then add the following line and save the crontab:

  0 * * * * myscript.sh > myscript.log
  

Now, you need to work out if you should send an email or not, based on your conditions. One way of doing this, is to write the output of your SQL command to a file like this:

#! /bin/bash
`sqlplus -s <username>/<passwd>@dbname` << EOF > sql.out  
set echo on  
set pagesize 0  
set verify off  
set lines 32000  
set trimspool on  
set feedback off  
`SELECT   starts_with, SUM (total_records) total_records
    FROM (SELECT ID,
                 (CASE WHEN ID LIKE '2%'  THEN '2____'
                       WHEN ID LIKE '3%'  THEN '3____'
                       WHEN ID LIKE '99%' THEN '99____'
                  END
                 ) starts_with,
                 total_records
            FROM tr
            where ( id like '2%' or id like '3%' or id like '99%'))
               WHERE tr.TIMESTAMP > SYSDATE - 75 / 1440
               AND tr.TIMESTAMP <= SYSDATE - 15 / 1440
GROUP BY starts_with;
`  
exit;  
EOF

Then apply your conditions to sql.out by using a grep or wc command and send an email using mailx (if installed) or sendmail:

if grep -q something sql.out
then
    # send email containing the output of the SQL statement
    cat sql.out | mailx -s "UP ALERT" [email protected]
fi

#delete sql.out
rm sql.out