Note for MySelf:
This post contains bash script sample , which upon executed, can query various system components & send the report via email. Useful to monitor remote server. Further functions can be added or existing can be customized according to the requirements. I opted for LOOP Formula to show mysql DB sizes in MB/GB using IF ELSE statements & some other fun stuff for myself as well.
The script is bit messy & scrambled in terms of proper organized display, but it works fine. You may customized or trim as per your taste
Feel free to use as you like …
Regard’s
Syed Jahanzaib
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 | #!/bin/shPATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/etc#set -x# Version 1.1 / 10th January, 2014# Last Modified / 5th-MARCH-2021# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacabl AT hotmail DOT com# This script generalized & customized DISK reports and email to admin# Adjust below DATA fields accordingly. remove / add desired tasks.# Settings various VARIABLES for the scriptclear# Colors Config ... [[ JZ ... ]]COMPANY="ZAIB_LTD"CREDITS="Powered by Syed Jahanzaib / 0333.3021.909 / aacable at hotmail dot com / https:// aacable . wordpress .com"#MYSQL DETAILSSRV="mysql"SQLUSER="root"SQLPASS="XXXXXX"export MYSQL_PWD=$SQLPASSCMD="mysql -uroot --skip-column-names -e"ALL_DB_TEMP_LIST="/tmp/mysq_all_dbs.txt"DB1="radius"DB2="conntrack"DB3="syslog"SQL_ACCOUNTING_TABLE="radacct"CMD="mysql -u$SQLUSER --skip-column-names -s -e"EMAILMSG="/tmp/report1.log"DB_HOLDER="/temp/temp_db_size_holder.log"> $EMAILMSG> $DB_HOLDERHOSTNAME=`hostname`INT_IP1=`hostname -I`INT_IP2=`ip route get 1 | awk '{print $NF;exit}'`EXT_IP=`dig +short myip.opendns.com @resolver1.opendns.com`URL="google.com"DNS=$(cat /etc/resolv.conf | sed '1 d' | awk '{print $2}')# Check OS Typeos=$(uname -o)#################################### Check OS Release Version and Name###################################OS=`uname -s`REV=`uname -r`MACH=`uname -m`GetVersionFromFile(){VERSION=`cat $1 | tr "\n" ' ' | sed s/.*VERSION.*=\ // `}if [ "${OS}" = "SunOS" ] ; thenOS=SolarisARCH=`uname -p`OSSTR="${OS} ${REV}(${ARCH} `uname -v`)"elif [ "${OS}" = "AIX" ] ; thenOSSTR="${OS} `oslevel` (`oslevel -r`)"elif [ "${OS}" = "Linux" ] ; thenKERNEL=`uname -r`if [ -f /etc/redhat-release ] ; thenDIST='RedHat'PSUEDONAME=`cat /etc/redhat-release | sed s/.*\(// | sed s/\)//`REV=`cat /etc/redhat-release | sed s/.*release\ // | sed s/\ .*//`elif [ -f /etc/SuSE-release ] ; thenDIST=`cat /etc/SuSE-release | tr "\n" ' '| sed s/VERSION.*//`REV=`cat /etc/SuSE-release | tr "\n" ' ' | sed s/.*=\ //`elif [ -f /etc/mandrake-release ] ; thenDIST='Mandrake'PSUEDONAME=`cat /etc/mandrake-release | sed s/.*\(// | sed s/\)//`REV=`cat /etc/mandrake-release | sed s/.*release\ // | sed s/\ .*//`elif [ -f /etc/os-release ]; thenDIST=`awk -F "PRETTY_NAME=" '{print $2}' /etc/os-release | tr -d '\n"'`elif [ -f /etc/debian_version ] ; thenDIST="Debian `cat /etc/debian_version`"REV=""fiif ${OSSTR} [ -f /etc/UnitedLinux-release ] ; thenDIST="${DIST}[`cat /etc/UnitedLinux-release | tr "\n" ' ' | sed s/VERSION.*//`]"fiOSSTR="${OS} ${DIST} ${REV}(${PSUEDONAME} ${KERNEL} ${MACH})"fi# Check Architecturearchitecture=$(uname -m)# Check Kernel Releasekernelrelease=$(uname -r)#SET DATE TIMEset $(date)time=`date |awk '{print $4}'`DT=`date +%d.%b.%Y_time_%H.%M`DATE=$(date +%Y-%m-%d)DT_HMS=$(date +'%H:%M:%S')FULL_DATE=`date`TODAY=$(date +"%Y-%m-%d")TODAYYMD=`date +"%d-%b-%Y"`#Get ip which have default routelogger General report has been started @ $DATE / $DT_HMS# Check FREERADIUS online sessions#SESSIONS=`$CMD "use radius; SELECT username FROM $SQL_ACCOUNTING_TABLE WHERE acctstoptime IS NULL;" |wc -l`# Adding OS level Details in email message# modify below disk name we want to monitor, make sure to change thisDISK="/dev/sda2"DISKTOT=`df -h $DISK |awk '{print $2}'| sed -n 2p`DISKUSED=`df -h $DISK |awk '{print $3}'| sed -n 2p`DISKAVA=`df -h $DISK |awk '{print $4}'| sed -n 2p`DISKUSEPER=`df -h $DISK |awk '{print $5}'| sed -n 2p`MEMTOT=`free -m |awk '{print $2}'| sed -n 2p`MEMUSED=`free -m |awk '{print $3}'| sed -n 2p`MEMAVA=`free -m |awk '{print $4}'| sed -n 2p`MEMUSEDPER=`free -m | grep Mem | awk '{print $3/$2 * 100.0}'`MEMAVAPER=`free -m | grep Mem | awk '{print $4/$2 * 100.0}'`#GMAIL DetailsGMAILID="XXXX@gmail.com"GMAILPASS="XXXXX"TO1="aacableXhotmail.com"SMTP="smtp.gmail.com:587"#Collect all data in fileecho "General Report for $HOSTNAME - $INT_IP - $EXT_IP===============NETWORK DETAILS:===============HOST: $HOSTNAMEOperating System Type $os$OSSTRArchitecture : $architectureKernel Release : $kernelreleaseINT_IP1: $INT_IP1INT_IP2: $INT_IP2EXT_IP: $EXT_IPDNS: $DNS================MYSQL DB REPORT:================" >> $EMAILMSG# Fetch ALL DB's & calculate there sizes and convert sizes in MB/GBMYSQLALLDB=`$CMD "show databases;" > $ALL_DB_TEMP_LIST`num=0cat $ALL_DB_TEMP_LIST | while read databasedonum=$[$num+1]DB=`echo $database | awk '{print $1}'`MYSQLDBSIZE=`$CMD "SELECT table_schema '$DB', sum(data_length + index_length)/1024/1024 FROM information_schema.TABLES WHERE table_schema='$DB' GROUP BY table_schema;" | cut -f1 -d"." | sed 's/[^0-9]*//g'`if [ "$MYSQLDBSIZE" -ge 1024 ]; thenMYSQLDBSIZE_FINAL=`echo "scale=2; $MYSQLDBSIZE/1024" |bc -l`echo "$DB / $MYSQLDBSIZE_FINAL GB" | column -t >> $DB_HOLDERfiif [ "$MYSQLDBSIZE" -le 1024 ]; thenMYSQLDBSIZE_FINAL=`echo "scale=2; $MYSQLDBSIZE" |bc -l`echo "$DB / $MYSQLDBSIZE_FINAL MB" | column -t >> $DB_HOLDERfidonecat $DB_HOLDER | column -t >> $EMAILMSGecho "=============Disk Details:=============" >> $EMAILMSGdf -h |grep sda2 | column -t >> $EMAILMSGecho "==============MEMORY_REPORT:==============Total_RAM = $MEMTOT MBTotal_RAM_Used = $MEMUSED MBTotal_RAM_Available = $MEMAVA MBTotal_RAM_Used_Percent = $MEMUSEDPER %Total_RAM_Available_Percent = $MEMAVAPER %" > /tmp/temp_memory_report.logcat /tmp/temp_memory_report.log | column -t >> $EMAILMSGecho "$CREDITS" >> $EMAILMSG# PRINT INFO SECTION ########## Print Fetched Information on Screen , for info to seecat $EMAILMSG# EMAIL SECTION ############### Make sure you install sendEMAIL tool and test it properly before using email section.#SEND EMAIL Alert As well using sendEMAIL tool using GMAIL ADDRESS.# If you want to send email , use below ...echo " - Sending SMS/EMAIL info ..."#curl "http://$KHOST/cgi-bin/sendsms?username=$KID&password=$KPASS&to=$CELL2+$CELL3+$CELL4" -G --data-urlencode text@$SMSMSGsendemail -u "$HOSTNAME - $EXT_IP - General Report- $DATE " -o tls=yes -s $SMTP -t $TO1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$EMAILMSG -o message-content-type=text# log entry in /var/log/sysloglogger General Report have been end @ $DATE / $DT_HMS |
Make sure to install BC to calculate size
1 | apt-get -y install bc |
Sample snapshot for Email Reporting !
