source: branches/trigger_burst_research/Monitoring/CheckStatus.sh@ 19536

#!/bin/bash
#
# This script checks the status tables in the DB for failed and crashed jobs
#

source `dirname $0`/../Sourcefile.sh
printprocesslog "INFO starting $0"

tables=( RawFileAvailISDC RawFileAvailWue RawFileRsyncedISDC SequenceFileAvailISDC AuxFilesAvailISDC DriveFileAvailISDC Callisto Star )

for step in ${tables[@]}
do
   getstepinfo
   counter=0
   for prim in ${prims[@]}
   do
      if [ $counter -eq 0 ]
      then
         selstart=" CONCAT("$prim
      fi
      if [ $counter -gt 0 ]
      then
         selstart=$selstart" '_', LPAD("$prim", 3, 0)"
      fi
      counter=`echo $counter + 1 | bc -l`
      #if [ $counter -eq ${#prims[@]} ]
      #then
      #   sel=$sel")"
      #fi
   done
   
   # check table for failed jobs
   printprocesslog "INFO checking "$step" for failed jobs."
   where=" WHERE NOT ISNULL(fReturnCode) "
   where=$where" AND fStopTime > ADDDATE(NOW(), INTERVAL -25 HOUR)"              
   query="SELECT Count(*) FROM "$step"Status "$where
   num=`sendquery`
   if [ "$num" == "" ]
   then 
      printprocesslog "WARN could not get number of failed jobs from the DB."
   else
      if [ $num -gt 0 ]
      then
         printprocesslog "WARN found in "$step" "$num" rows with errors."
         sel=$selstart", '(', fReturnCode, ')')"
         query="SELECT "$sel" FROM "$step"Status "$where
         sendquery | mail -s 'found failed jobs in '$step $erradrs
         printprocesslog "INFO sent mail about failed jobs in "$step" to "$erradrs
      fi
   fi

   # check table for crashed jobs
   #   i.e. jobs which are running > 2 hours
   # check the last 27 hours
   printprocesslog "INFO checking "$step" for crashed jobs."
   where=" WHERE NOT ISNULL(fStartTime) AND ISNULL(fStopTime) "
   where=$where" AND fStartTime < ADDDATE(NOW(), INTERVAL -2 HOUR)"
   where=$where" AND fStartTime > ADDDATE(NOW(), INTERVAL -27 HOUR)"
   query="SELECT Count(*) FROM "$step"Status "$where
   num=`sendquery`
   if [ "$num" == "" ]
   then 
      printprocesslog "WARN could not get number of crashed jobs from the DB."
   else
      if [ $num -gt 0 ]
      then
         printprocesslog "WARN found in "$step" "$num" crashed jobs."
         sel=$selstart", '(', fStartTime, ')')"
         query="SELECT "$sel" FROM "$step"Status "$where
         sendquery | mail -s 'found crashed jobs in '$step $erradrs
         printprocesslog "INFO sent mail about crashed jobs in "$step" to "$erradrs
      fi
   fi
done

printprocesslog "INFO finished $0"