MyWiki

#!/bin/bash
set -x

# script zabbix d'envoi de mail de rapport mensuel

# Connexion a la BDD
PGPASSWORD='Kn8ks$Lmpx31'  psql -U zabbix -h 10.1.1.72 << 'EOF' | tee "/total.csv" &> /dev/null
\COPY ( SELECT DISTINCT TIMESTAMP WITH TIME ZONE 'epoch' + events.clock * INTERVAL '1 second' AS Date, hstgrp.name AS Groupes,hosts.name AS Hosts,events.name AS Erreurs,events.severity AS Severity FROM events  JOIN triggers ON events.objectid = triggers.triggerid JOIN functions ON functions.triggerid = triggers.triggerid JOIN items ON items.itemid = functions.itemid JOIN hosts ON items.hostid = hosts.hostid JOIN hosts_groups ON hosts.hostid = hosts_groups.hostid JOIN hstgrp ON hosts_groups.groupid = hstgrp.groupid WHERE events.clock >= extract(epoch from date_trunc('month', current_timestamp) - interval '1' month) AND events.clock < extract(epoch from date_trunc('month', current_timestamp)) AND events.source = 0 AND NOT events.severity < 2 AND hstgrp.name IN ('G_APWIFI','G_BAIE_DISQUES','G_CERTIFICATS','G_GTC','G_LIGHTPOINTES','G_PROD-LINUX','G_PDA','G_PROD-WINDOWS','G_RESEAU','G_SWITCHS','G_VLANS','G_VSPHERE') AND hosts.maintenance_status = 0) to '/tmp/total.csv' with csv header;
EOF

# Envoi du rapport mensuel
#echo "Bonjour, voici le rapport mensuel de zabbix@orsay." | mail -S from=zabbix@orsay.fr -s "Rapport mensuel de la supervision ORSAY" -a /tmp/total.csv mziletti@mgi.fr #,slabadie@mgi.fr,emanueco@mgi.fr,sgenevieve@mgi.fr,jfournie@mgi.fr

#!/bin/bash
set -x

# script zabbix d'envoi de mail de rapport mensuel (Mysql)

# Initialisation variables de base

MYSQLSERVER="127.0.0.1"
MYSQLUSER="zabbix"
MYSQLPASSWD='Tssi1602'
MYSQLDB="zabbix"

REQUEST="SELECT DISTINCT 
from_unixtime(events.clock) AS Date,
hstgrp.name AS Groupes,
hosts.name AS Hosts,
events.name AS Erreurs,
events.severity AS Severity
FROM events JOIN triggers ON events.objectid = triggers.triggerid 
JOIN functions ON functions.triggerid = triggers.triggerid 
JOIN items ON items.itemid = functions.itemid 
JOIN hosts ON items.hostid = hosts.hostid 
JOIN hosts_groups ON hosts.hostid = hosts_groups.hostid 
JOIN hstgrp ON hosts_groups.groupid = hstgrp.groupid 
WHERE events.source = 0 AND NOT events.severity < 2 AND hosts.maintenance_status = 0
AND month(from_unixtime(events.clock))=month(now())-1 
ORDER BY events.clock DESC;"

# Execution de la requete puis on traite ligne par ligne

mysql -u $MYSQLUSER --password=$MYSQLPASSWD -h $MYSQLSERVER $MYSQLDB -e "$REQUEST" -NB | sed 's/\t/;/g' | while read -r line
do
        DATE=$(echo $line | awk -F ";" {'print $1'})
        GROUP=$(echo $line | awk -F ";" {'print $2'})
        HOST=$(echo $line | awk -F ";" {'print $3'})
        SERVICE=$(echo $line | awk -F ";" {'print $4'})
        STATUS=$(echo $line | awk -F ";" {'print $5'})


        printf "%b" "$DATE;$GROUP;$HOST;$SERVICE;$STATUS\n" >> /tmp/total.csv

done

# Envoi du rapport mensuel
echo "Bonjour, voici le rapport mensuel de zabbix@orsay." | mail -S from=zabbix@orsay.fr -s "Rapport mensuel de la supervision ORSAY" -a /tmp/total.csv mziletti@mgi.fr #,slabadie@mgi.fr,emanueco@mgi.fr,sgenevieve@mgi.fr,jfournie@mgi.fr
rm -f /tmp/total.csv