PSOFT : Unix Script to monitor PS IB messages

#!/bin/ksh
#This simple script sends out alerts on encountering IB Pub contracts , Sub Contracts & Pub handler mesg in Error/Retry & timeout status

# Platform : AIX 6.1, Oracle 11g, Peopletools 8.51.06
#Usage : FS_Monitor_IB_MSG.ksh
#Author : Niraj Patil

###Check for Alive Subscription contracts messages in Error/New/Timeout###
SRCDIR1=/home/NjServer/src ; export SRCDIR1
LOGFILE1=/home/NjServer/logs/FS_Monitor_IB.log ; export LOGFILE1
MAILLIST1="nirajdpatil1986@gmail.com groupemail@abc.com"
echo "\n --------------------`date`---------------------------- \n" >> $LOGFILE1
SUBCON=`sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/subcon.log
set feedback off
set heading off
SET SERVEROUTPUT ON SIZE 100000
Declare
SubCon_MSG VARCHAR2(200);
Begin
select count(*) into SubCon_MSG from psapmsgsubcon
where statusstring in ('ERROR','RETRY','TIMEOUT') or
SUBCONSTATUS in (0,5,6);
dbms_output.put_line(Chr(10)||'PSADMIN ' || SubCon_MSG);
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

###Check for Alive Subscription contracts messages in Error/New/Timeout###
PUBCON=`sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/pubcon.log
set feedback off
set heading off
SET SERVEROUTPUT ON SIZE 100000
Declare
PubCon_MSG VARCHAR2(200);
Begin
select count(*) into PubCon_MSG from PSAPMSGPUBCON
where STATUSSTRING in ('ERROR','RETRY','TIMEOUT') or
PUBCONSTATUS in (0,5,6);
dbms_output.put_line(Chr(10)||'PSADMIN ' || PubCon_MSG);
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

###Check for Alive Publication Handler messages in Error/New/Timeout###
PUBHDR=`sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/pubhdr.log
set feedback off
set heading off
SET SERVEROUTPUT ON SIZE 100000
Declare
PubHdr_MSG VARCHAR2(200);
Begin
select count(*) into PubHdr_MSG from PSAPMSGPUBHDR
where STATUSSTRING in ('ERROR','RETRY','TIMEOUT') or
PUBSTATUS in (0,5,6);
dbms_output.put_line(Chr(10)||'PSADMIN ' || PubHdr_MSG);
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

SC_Count=`grep "PSADMIN" /tmp/subcon.log | awk -F" " '{ print $2 }'`
PC_Count=`grep "PSADMIN" /tmp/pubcon.log | awk -F" " '{ print $2 }'`
PH_Count=`grep "PSADMIN" /tmp/pubhdr.log | awk -F" " '{ print $2 }'`

# Mail detailed Subscription contracts Message record if there are any in Error/Retry/Timeout
if [ $SC_Count -ne 0 ]
then
sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/subcondata.log
set pagesize 1000
set linesize 130
set feedback off
select * from psapmsgsubcon
where statusstring in ('ERROR','RETRY','TIMEOUT') or
SUBCONSTATUS in (0,5,6);
EXIT;
EOF
cat /tmp/subcondata.log | grep -vi SQL | grep -vi Oracle | grep -vi "Partitioning" | grep -vi connected | grep -vi ^$ > /tmp/SUBCONDATA
mailx -s "Subscription contracts messages in Error/Retry/Timeout in $DB_NAME at `date`" $MAILLIST1 < /tmp/SUBCONDATA
echo "Subscription contracts messages in Error/Retry/Timeout in $DB_NAME at `date`" >> $LOGFILE1
cat /tmp/SUBCONDATA >> $LOGFILE1
else
echo "No Subscription Contract message in Error for $DB_NAME at `date`" >> $LOGFILE1
fi

#Mail detailed Publication contracts Message record if there are any in Error/Retry/Timeout
if [ $PC_Count -ne 0 ]
then
sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/pubcondata.log
set pagesize 1000
set linesize 130
set feedback off
select * from PSAPMSGPUBCON
where STATUSSTRING in ('ERROR','RETRY','TIMEOUT') or
PUBCONSTATUS in (0,5,6);
EXIT;
EOF
cat /tmp/pubcondata.log | grep -vi SQL | grep -vi Oracle | grep -vi "Partitioning" | grep -vi connected | grep -vi ^$ > /tmp/PUBCONDATA
mailx -s "Publication contracts messages in Error/Retry/Timeout on $DB_NAME at `date`" $MAILLIST1 < /tmp/PUBCONDATA
echo "Publication contracts messages in Error/Retry/Timeout on $DB_NAME at `date`" >> $LOGFILE1
cat /tmp/PUBCONDATA >> $LOGFILE1
else
echo "No Publication Contract message in Error $DB_NAME at `date`" >> $LOGFILE1
fi

#Mail detailed Publication Handler Message record if there are any in Error/Retry/Timeout
if [ $PH_Count -ne 0 ]
then
sqlplus ${DB_LOGIN}/${DB_PASSWD}@${DB_NAME} <<EOF > /tmp/pubhdrdata.log
set pagesize 1000
set linesize 130
set feedback off
select * from PSAPMSGPUBHDR
where STATUSSTRING in ('ERROR','RETRY','TIMEOUT') or
PUBSTATUS in (0,5,6);
EXIT;
EOF
cat /tmp/pubhdrdata.log | grep -vi SQL | grep -vi Oracle | grep -vi "Partitioning" | grep -vi connected | grep -vi ^$ > /tmp/PUBHDRDATA
mailx -s "Publication contracts messages in Error/Retry/Timeout on $DB_NAME at `date`" $MAILLIST1 < /tmp/PUBHDRDATA
echo "Publication contracts messages in Error/Retry/Timeout on $DB_NAME at `date`" >> $LOGFILE1
cat /tmp/PUBHDRDATA >> $LOGFILE1
else
echo "No Publication Handler message in Error/Retry/Timeout in $DB_NAME at `date`" >> $LOGFILE1
fi