Unix Script 1- to find the Zombee process and send email

####################################################################################################
# Script  - CATCHZ.ksh
# Author  - Amol P Ingle
# Date    - 5/24/2018
# Version - 1.0
#
# Purpose - when stack trace with infected 11 hits ASE dataserver it will check
#           1. Check Zombie process
#              -if Zombie process came in then will send alert message to DBA Team
#               with DBA Page to On-call DBA
#              - even if zombie process not in server and infected message comes in errorlog then-
#           2. Will check health of the ASE dataserver and send an email to DBA team
#           3. Will check message with shutdown as well and if comes then will
#              Alert email message to DBA Team and On-call DBA
#
#####################################################################################################

cd /dir

if test -f lerr1.txt
then
rm lerr1.txt
fi
rm tail.log
touch tail.log
touch /dir/SERVER_NAME.log_old
cp /SERVER_NAME/ASE_LOG/ASE-*/install/ /ASE-*/install/SERVER_NAME.log /dir/SERVER_NAME.log
diff /dir/SERVER_NAME.log /dir/SERVER_NAME.log_old | grep "<" > tail.log

#####################################################################################
# PART 1 - Check the zombie process if there or not
#
#####################################################################################

EMAILTEAM="abc@abc.com"

EMAILONCALL="xyz@abc.com"



for i in tail.log
do
grep infected $i >> lerr1.txt
done
file="lerr1.txt"

if [ -s "$file" ]
then
isql -UUser_Namedb_name.table_list -PXXXXXXXXX -Dmaster -w2000 << EOF!>catchzmb.log

IF EXISTS  (select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses))
PRINT 'Zombie Caught'
ELSE
PRINT 'NONE'
go
IF EXISTS (select spid from master..syslocks where spid not in (select spid from master..sysprocesses))
PRINT 'Zombie Caught'
ELSE
PRINT 'NONE'
go
exit
EOF!
fi

echo $(wc -l < lerr1.txt)> inftcount.txt
inft=`cat inftcount.txt`
grep Zombie catchzmb.log > rowsout.txt

grep Zombie catchzmb.log > rowsout.txt

if [ -s rowsout.txt ]
then

cat catchzmb.log > ZOMBIE.txt
  echo " Zombie Process Found, Please check SERVER_NAME" > ZOMBIE.txt
  echo " Infected Message count : $inft " > ZOMBIE.txt

 /usr/bin/mail -s "Zombie ALERT : Zombie Process Found on SERVER_NAME Server, Please check immediately" $EMAILONCALL < ZOMBIE.txt
else
echo "No Zombie Process"

fi
cp /dir/SERVER_NAME.log /dir/SERVER_NAME.log_old
rm rowsout.txt catchzmb.log ZOMBIE.txt




##########################################################################
## PART 2 - check infected 11 appears the check server health and email ##
##########################################################################

logfile="tail.log"


isql -UUser_Namedb_name.table_list -PXXXXXXXXX -Dmaster -w2000 <> health.log

print "<< StackTrace Message Appears on the Server Errolog, Checking server Health >>"
go
select @@servername,getdate()
go
print "<>"
go
select * from syslogshold where name not like '%replication_truncation_point%'
go
print "<< Blokcing on server>>"
go
select spid,cmd,blocked from sysprocesses where blocked >0
go
print "<<  SPID which is in syslogshold but not in sysprocesses >>"
go
select * from syslogshold where spid not in (select spid from master..sysprocesses ) and name not like '%replication_truncation_point%'
go
select spid from master..syslogshold slh where slh.spid <> 0 and slh.spid not in(select spid from master..sysprocesses)
go
print "<< SPID which is in syslocks but not in sysprocesses >>"
go
select spid from syslocks where spid not in (select spid from sysprocesses)
go
print "<< Below Database marked as Suspect / Offline Status >> "
go
select name from sysdatabases where status in (-32768,64,256,32)
go

exit
EOF1!
fi
/usr/bin/mail -s "StackTrace Message Appears on SERVER_NAME Server, See below details: Infected Message Count:$inft" $EMAILTEAM < health.log

rm health.log
#exit

#####################################################################################
# PART 3 - Check for the shutdown message
#
#####################################################################################

critical="tail.log"


cat $critical | grep "shutdown"
        if [ $? = 0 ]
        then

echo " shutdown message apprears in SERVER_NAME errorlog, Please check  server Health !!" > srvdown.txt

/usr/bin/mail -s "StackTrace made SERVER_NAME server down " $EMAILONCALL < srvdown.txt
/usr/bin/mail -s "StackTrace made SERVER_NAME server down " $EMAILTEAM < tail.log
fi
rm srvdown.txt inftcount.txt
exit