Home > bash, PostgreSQL, Slackware > Slackware mailx send html formatted mail with PostgreSQL query result

Slackware mailx send html formatted mail with PostgreSQL query result

I needed monitoring script to mail particular records from a log table.

#!/bin/sh
# Sun Jan 29 22:32:22 EET 2012
# done by dragkh 

recipientMail="meee@host.com me3@host.com"
recipientID="jefe"
where="$(/bin/pwd)"
homeDir="/opt/bin"
locKFile="${homeDir}/${recipientID}.lock.alert"
SMTP="smtp.host.com:25"
FROM_MAIL="me@host.com"
LOGIN="me@host.com"
LOGIN_PASSWORD="paaa4444ssswwwd00d"



sql="
select
service_id as SID
, reason_to_fail  as ERROR 
, mesg as SMS
, shortcode as ORIGINATOR
, msisdn as MSISDN
, client_sms_id
, ext_id as smsID
, otime as OTIME
, retry as RETRY
from bsms_in_failed_requests_log
where true
and otime > now() - interval '30 minute'
order by otime
-- limit 50
;
"

status=$(echo "${sql}" |  /opt/pgsql/bin/psql -x ddlog)


echo "$(date) : result : ${status}"
#exit 0
if [ "${status}" != "" -a "${status}" != "(No rows)" ]
then 
echo "<!DOCTYPE html PUBliC \"-//W3C//DTD html 4.0 Transitional//EN\">
<html>
  <head>
    <META HTTP-EQUIV=\"Content-Type\" CONTENT=\"text/html; charset=utf-8\">
  </head>
  <body>
<h3>Slednite zaiavki sa bili othvyrleni ot HOSTBGAPI:</h3> 
<h3>Следните заявки са били отхвърлени в последните 30 минути от HOSTBGAPI:</h3> 
<pre>
${status}
</pre>
</body>
</html>
" | /usr/bin/mailx \
-vvv \
-s "HOSTBG failed requests : $(date)
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit" \
-S smtp-auth=login \
-S smtp="$SMTP" \
-S from="$FROM_MAIL" \
-S smtp-auth-user="$LOGIN" \
-S smtp-auth-password="$LOGIN_PASSWORD" \
-b me1@host.com \
"${recipientMail}"

fi

crontab -l
# notify for rejected requests : 
*/30 * * * *     /bin/sh /opt/bin/check.rejected.reqs.and.send.mail.sh >> /var/log/check.rejected.reqs.and.send.mail.log 2>&1 

enjoy :)

About these ads
Categories: bash, PostgreSQL, Slackware Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: