#!/bin/bash
set -x

# script d'envoi de mail de rapport mensuel

# Initialisation variables de base\\
MYSQLSERVER="p-a389-cenm-001.mgmt.mycorp.corp"\\
MYSQLUSER="centreondashing"\\
MYSQLPASSWD='PASS'\\
MYSQLDB="centreon_storage"\\

   REQUEST="select DISTINCT from_unixtime(ctime) AS DATE, 
   hostgroups.name, hosts.name, logs.service_description, logs.output from logs 
   INNER JOIN hosts_hostgroups ON logs.host_id = hosts_hostgroups.host_id 
   INNER JOIN hostgroups ON hostgroups.hostgroup_id = hosts_hostgroups.hostgroup_id  
   INNER JOIN hosts ON hosts_hostgroups.host_id = hosts.host_id 
   WHERE NOT status = 0 AND logs.output NOT LIKE '%UNKNOWN%' 
   AND hostgroups.name REGEXP '_NW|DataBase|EE_Aerobox|Linux|Storage|UCS|VCT|Windows'  
   AND logs.type = 1 
   AND month(from_unixtime(ctime))=month(now())-1;"

# 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/AA.csv

done

# Envoi du rapport mensuel\\
echo "Bonjour, voici le rapport mensuel de centreon@stelia.aero." | mail -S from=centreon@stelia.aero -s "Rapport mensuel de lasupervision Airbus Atlantic" -a /tmp/AA.csv mziletti@mgi.fr\\
rm -f /tmp/AA.csv