BASH DB query and email script - socalal

gistfile1.txt

#!/bin/bash

DATE=$(date)

MYSQL_QUERY1="SELECT COUNT(DISTINCT(nodeId)) AS 'Unreachable Nodes' FROM Alarm WHERE firstOccurrence BETWEEN NOW() - INTERVAL 14 DAY AND NOW() AND additionalInfo LIKE '%Timeout%' AND clearedDate IS NULL;"

MYSQL_QUERY2="SELECT COUNT(DISTINCT(nodeId)) AS 'Unreachable AMI 1.0 Nodes' FROM Alarm WHERE additionalInfo LIKE '%Smart Meter 1.0%' AND id IN (SELECT id AS 'Unreachable Nodes' FROM Alarm WHERE firstOccurrence BETWEEN NOW() - INTERVAL 14 DAY AND NOW() AND additionalInfo LIKE '%Timeout%' AND clearedDate IS NULL);"

MYSQL_QUERY3="SELECT COUNT(DISTINCT(nodeId)) AS 'Unreachable AMI 1.1 Nodes' FROM Alarm WHERE additionalInfo LIKE '%Smart Meter 1.1%' AND id IN (SELECT id AS 'Unreachable Nodes' FROM Alarm WHERE firstOccurrence BETWEEN NOW() - INTERVAL 14 DAY AND NOW() AND additionalInfo LIKE '%Timeout%' AND clearedDate IS NULL);"

MYSQL_QUERY4="SELECT COUNT(DISTINCT(nodeId)) AS 'Unreachable AMI 1.2 Nodes' FROM Alarm WHERE additionalInfo LIKE '%Smart Meter 1.2%' AND id IN (SELECT id AS 'Unreachable Nodes' FROM Alarm WHERE firstOccurrence BETWEEN NOW() - INTERVAL 14 DAY AND NOW() AND additionalInfo LIKE '%Timeout%' AND clearedDate IS NULL);"

MYSQL_QUERY5="SET group_concat_max_len=200000;"

MYSQL_QUERY6="SELECT GROUP_CONCAT(HEX(ulpNodeId))FROM node WHERE id IN (SELECT DISTINCT(nodeId) FROM Alarm WHERE firstOccurrence BETWEEN NOW() - INTERVAL 14 DAY AND NOW() AND additionalInfo LIKE '%Timeout%' AND clearedDate IS NULL);"

echo "$DATE" >> /home/linux_user/OTV_Unreachable_report-temp.txt
echo "CURRENTLY UNREACHABLE AND DROPPED OFF WITHIN THE LAST 14 DAYS $DATA1" >> /home/linux_user/OTV_Unreachable_report-temp.txt
mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY1" >> /home/linux_user/OTV_Unreachable_report-temp.txt

echo "AMI 1.0" >> /home/linux_user/OTV_Unreachable_report-temp.txt
mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY2" >> /home/linux_user/OTV_Unreachable_report-temp.txt

echo "AMI 1.1" >> /home/linux_user/OTV_Unreachable_report-temp.txt
mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY3" >> /home/linux_user/OTV_Unreachable_report-temp.txt

echo "AMI 1.2" >> /home/linux_user/OTV_Unreachable_report-temp.txt
mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY4" >> /home/linux_user/OTV_Unreachable_report-temp.txt

mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY5" >> /home/linux_user/OTV_Unreachable_report-temp.txt

echo "$DATE" >> /home/linux_user/OTV_Unreachable_report-MAC_ADDRESSES.txt
echo "NODE IDs OF CURRENTLY UNREACHABLE AND DROPPED OFF WITHIN THE LAST 14 DAYS" >> /home/linux_user/OTV_Unreachable_report-MAC_ADDRESSES.txt
mysql -h 10.20.30.40 -ufakeuser -ppassword i_am_a_database -N -ss -e "$MYSQL_QUERY6" >> /home/linux_user/OTV_Unreachable_report-MAC_ADDRESSES.txt
echo "" >> /home/linux_user/OTV_Unreachable_report-MAC_ADDRESSES.txt

echo "" >> /home/linux_user/OTV_Unreachable_report-temp.txt
echo "----------------------------------------------------------------" >> /home/linux_user/OTV_Unreachable_report-temp.txt
echo "" >> /home/linux_user/OTV_Unreachable_report-temp.txt

cat /home/linux_user/OTV_Unreachable_report-temp.txt /home/linux_user/OTV_Unreachable_report.txt > /home/linux_user/temp && mv /home/linux_user/temp /home/linux_user/OTV_Unreachable_report.txt
rm /home/linux_user/OTV_Unreachable_report-temp.txt

/bin/mail -s "Nolin - OTV Currently Unreachable & Communicated Within 14 Days" "thisemailaddress@email.com" < /home/linux_user/OTV_Unreachable_report.txt
/bin/mail -s "Nolin - EMS Currently Not Joined & Communicated Within 14 Days" "thisemailaddress@email.com" < /home/linux_user/EMS_not_joined_report.txt
/bin/mail -s "College Park - EMS Currently Not Joined & Communicated Within 14 Days" "thisemailaddress@email.com" < /home/linux_user/CollegePark-EMS_not_joined_report.txt

#/bin/mail -s "Nolin - OTV Currently Unreachable & Communicated Within 14 Days" "alan.orther@ingenu.com<mailto:alan.orther@ingenu.com>" < /home/linux_user/OTV_Unreachable_report.txt
#/bin/mail -s "Nolin - EMS Currently Not Joined & Communicated Within 14 Days" "alan.orther@ingenu.com<mailto:alan.orther@ingenu.com>" < /home/linux_user/EMS_not_joined_report.txt
#/bin/mail -s "College Park - EMS Currently Not Joined & Communicated Within 14 Days" "alan.orther@ingenu.com<mailto:alan.orther@ingenu.com>" < /home/linux_user/CollegePark-EMS_not_joined_report.txt