#!/bin/bash

SENDWITH0=0
URL=$(echo "http://$(hostname --fqdn)/zabbix/")
MAILTO="root@$(hostname -d)"
MAILFROM=$(echo "zabbix@$(hostname -d)")

SQLHOST=$(sed -e '/^DBHost/!d; s/.*=\(.*\)/\1/' /etc/zabbix/zabbix_server.conf)
SQLUSER=$(sed -e '/^DBUser/!d; s/.*=\(.*\)/\1/' /etc/zabbix/zabbix_server.conf)
SQLPASS=$(sed -e '/^DBPassword/!d; s/.*=\(.*\)/\1/' /etc/zabbix/zabbix_server.conf)

SQL='SELECT host, t.description, FROM_UNIXTIME(e.clock) as time, t.value, i.lastvalue, IF(e.acknowledged=1,"Yes","No") as acknowledged, IF(e.acknowledged=1,a.message,"") as message
FROM triggers t
INNER JOIN functions f ON ( f.triggerid = t.triggerid )
INNER JOIN items i ON ( i.itemid = f.itemid )
INNER JOIN hosts h ON ( i.hostid = h.hostid )
INNER JOIN events e ON ( e.objectid = t.triggerid )
LEFT JOIN acknowledges a ON ( a.eventid = e.eventid )
WHERE (e.eventid DIV 100000000000000)
IN (0)
AND e.object = 0
AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60))
AND h.status = 0
AND i.status = 0
AND t.status = 0
AND e.eventid = (SELECT max(eventid)
FROM events e
WHERE (e.eventid DIV 100000000000000)
IN (0)
AND e.object = 0
AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60))
AND h.status = 0
AND i.status = 0
AND t.status = 0
AND e.objectid = t.triggerid
)
GROUP BY host, f.triggerid
ORDER BY t.lastchange DESC;'

echo ${SQL}|mysql -h ${SQLHOST} -u ${SQLUSER} -p${SQLPASS} zabbix -t > /dev/shm/$(basename $0)

body="This is an automatically generated mail message sent by a script running on $(hostname --fqdn)."
subject="Zabbix / Trigger Status"
num=0

if [ -s /dev/shm/$(basename $0) ]; then

num=$(( $(cat /dev/shm/$(basename $0)|wc -l) -4))
body="${body}

The following triggers are currently active:

$(cat /dev/shm/$(basename $0))"

else

body="${body}

All quiet on the western front!"

fi

body="${body}

Log in to Zabbix at ${URL}

Regards,
$(hostname -s) (Zabbix Server)"

subject="${subject} / $num"

if [ ${num} -eq 0 ] && [ ${SENDWITH0} -eq 0 ]; then
        exit 0;
else
        echo -e "From: ${MAILFROM}\nTo: ${MAILTO}\nSubject: ${subject}\n\n${body}"|mail -t
fi
