Script for Running instances Email Notification for Composite deployed in any domain

          Script for Running instances Email Notification for Composite deployed in any domain

Hello to viewer,

This script is for getting the Email Notification in case there is any Running instance of any composite.
This script will monitor the instances of last one hour and if there is any running instance found for any composite deployed in our domain , it simple sends an alert in the form of Email Notification.

Benefit: It is helpful in monitoring the critical transaction especially in production environment so that any transaction failure can be recovered by getting the prior notification.

You need to follow the below steps:

Step 1) Create the below directory structure.

cd /shared/fmw/myscript/monitor

Step 2) Create a file InstanceMonitor.sh under current directory with below content.

#!/bin/sh
export ORACLE_HOME=oracle_home


COMPMON_HOME='/shared/fmw/myscript/monitor'
cd $COMPMON_HOME

MAILTO=xxxxxx@xxxxxx.com
MAILCC=xxxxxx@xxxxxx.com
DBUSER=database_user
PASSWORD=password
HOST=hostname
PORT=port
SERVICE_NAME=service_name
INFO=OFF
ENV=ENV_NAME
MAILFROM=xxxxxx@xxxxx.com
START=ON
OUTPUT=/shared/fmw/myscript/monitor/output.html

if [ -f $COMPMON_HOME/sqloutput.txt ]; then
  rm -f $COMPMON_HOME/sqloutput.txt
fi

if [ -f $COMPMON_HOME/.tmp ]; then
  rm -f $COMPDBMON_HOME/.tmp
fi

########### For MY Domain ########

#if [ ! -f /shared/fmw/myscript/monitor/donotmail ]; then

        echo " " >$OUTPUT
        (
        echo "<br>"
        echo "<H2>LIST OF RUNNING INSTANCES for Composite in Mydomain</H2>"
        #echo "<br>"
        echo "<table border = 1 cellSpacing= 1 cellPadding=1 >"
        echo "<th>"
        echo "<tr bgcolor='#CFCFFF' ><FONT face=Tahoma color='blue'> "
        echo "<td colspan='1' align='center' font-color='blue'><b>INSTANCE ID</b></td>"
        echo "<td colspan='1' align='center' font-color='blue'><b>COMPOSITE NAME</b></td>"
        echo "<td colspan='1' align='center' font-color='blue'><b>SOURCE NAME</b></td>"
        echo "<td colspan='1' align='center' font-color='blue'><b>CONVERSATION ID</b></td>"
        echo "<td colspan='1' align='center' font-color='blue'><b>CREATED TIME</b></td>"
        echo "</font>"
        echo "</tr>"
        echo "</th>"
        echo "<tr>"
        )>>$OUTPUT

        RETURN=`sqlplus -S 'database_user/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=port)))(CONNECT_DATA=(SERV
ER=DEDICATED)(SERVICE_NAME=servicename)))' < query.sql`

        len=`expr length "$RETURN"`
        if [ `echo $len` -gt 0 ]; then
        sed '/^$/d' < $COMPMON_HOME/sqloutput.txt >$COMPMON_HOME/.tmp
        i=1
        while read record; do
                if [ $i -ge 6 ]; then
                                echo "</tr>" >>$OUTPUT
                                echo "<tr>" >>$OUTPUT
                                i=1
                else
                                (echo "<td>"
                                echo $record
                                echo "</td>" )>>$OUTPUT
                                i=`expr $i + 1`

                fi
        done < /shared/fmw/myscript/monitor/.tmp
        echo "</tr>" >>$OUTPUT
        echo "</table>" >>$OUTPUT
        echo "<hr>" >>$OUTPUT


(
                        echo "From: $MAILFROM"; \
                        echo "To: $MAILTO"; \
                        echo "Cc: $MAILCC"; \
                        echo "Content-Type: text/html";\
                        echo "Subject:Running Instances Monitoring"; \
                        echo ""; \
                        cat $OUTPUT; \
                ) | /usr/lib/sendmail -t
        fi


Note: you need to provide the value of  below variable according to your environment:

oracle_home

TNS_ENTRY: 

DBUSER=database_user
PASSWORD=password
HOST=hostname
PORT=port
SERVICE_NAME=service_name


Step 3) Create the file query.sql file that contains the sql script for getting the Running instance details of composites deployed in any domain.

SET ECHO ON
WHENEVER SQLERROR EXIT -1 ROLLBACK
SET TRIMOUT ON
SET HEAD OFF
SET feedback OFF
SET serveroutput OFF

spool sqloutput.txt
select mycom.id, substr(mycom.composite_DN, 0, instr(mycom.composite_DN, '!')-1) Composite_name, mycom.source_name, mycom.conversation_id
, to_char(mycom.created_time, 'MM/DD/YY-HH:MI:SS')
from composite_instance mycom
where
mycom.state = '0'
and mycom.id not in (select cmpst_id from cube_instance mycube)
and mycom.created_time > sysdate - 1/24
and substr(mycom.composite_DN, 0, instr(mycom.composite_DN, '!')-1) IN('composite_name separated by comma for which you want to monitor running instances');
spool off

quit

After this you just need to execute the shell script or you can set this job in cron for every 30 min.

Thanks a lot for your pateince!!!!

Regards
-Ashish