etl-alert-framework/alert_generator.ksh at master · datasherlock/etl-alert-framework · GitHub

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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
#!/bin/ksh
#
################################################################################
# alert_generator.ksh
#  Examples:
# sh alert_generator.ksh -e dev
#
# Modification History
#
# Date:      Name:		Desc:
# ---------- ---------- -------------------------------------------------------
# 10/14/2014 Jerome		Created
################################################################################
#
#
Usage()
{
	echo "Usage: $1 -e environment [-h]"
	exit
}

FileCLNUP()
{
	# remove files and logs > 3 days old
	echo `date` "Removing these files:"
	find ${log_dir}  -name "$1*" -mtime +3 -exec ls -lt {} \;
	find ${log_dir}  -name "$1*" -mtime +3 -exec rm {} \;
	echo "Log file for alert process removed"
}

GenerateAlert()
{
touch ${log_dir}/TmpMailFile
NotList=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT LISTAGG(ALRT_KEY,' ') WITHIN GROUP (ORDER BY ALRT_KEY) FROM
(SELECT DISTINCT ALRT_KEY FROM ALRT_LOG_TBL WHERE SWEEP_IND='Y');
ENDSQL`

for i in $NotList
do
RecList=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT ALRT_RCPNT FROM ALRT_REF_TBL WHERE ALRT_KEY=$i;
ENDSQL`

Subject=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT ALRT_SUBJ FROM ALRT_REF_TBL WHERE ALRT_KEY=$i;
ENDSQL`

MailHdr=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT ALRT_MSG FROM ALRT_REF_TBL WHERE ALRT_KEY=$i;
ENDSQL`

FldCnt=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT
regexp_count(COL_META,'[^|]+')
FROM ALRT_REF_TBL WHERE ALRT_KEY=$i;
ENDSQL`

echo "From: DemoUser@Demo.com" > ${log_dir}/TmpMailFile
echo "To: " $RecList >> ${log_dir}/TmpMailFile
echo "MIME-Version: 1.0" >> ${log_dir}/TmpMailFile
echo "Content-Type: text/html" >> ${log_dir}/TmpMailFile
echo "Subject: " $Subject >> ${log_dir}/TmpMailFile
echo "<html><body><p>" $MailHdr"</p><table border=1 cellspacing=0 cellpadding=3>" >> ${log_dir}/TmpMailFile

echo "<tr>" >> ${log_dir}/TmpMailFile
for k in {1..$FldCnt}
do
Fld=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
SELECT regexp_substr(COL_META,'[^|]+',1,$k) FROM ALRT_REF_TBL WHERE ALRT_KEY=$i;
ENDSQL`
echo "<td><b>"$Fld"</b></td>" >> ${log_dir}/TmpMailFile
done
echo "</tr>" >> ${log_dir}/TmpMailFile

MailBodyAggrCursor=`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set serveroutput on
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
DECLARE
vAlrtKeys clob;
  procedure print_clob( p_clob in clob ) is
      v_offset number default 1;
      v_chunk_size number := 32767;
  begin
      loop
          exit when v_offset > dbms_lob.getlength(p_clob);
          dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
          v_offset := v_offset +  v_chunk_size;
      end loop;
  end print_clob;
BEGIN
FOR j IN (SELECT ALRT_LOG_KEY FROM ALRT_LOG_TBL WHERE ALRT_KEY=$i AND SWEEP_IND='Y')
LOOP
SELECT vAlrtKeys||ALRT_LOG_KEY||' ' INTO vAlrtKeys FROM ALRT_LOG_TBL WHERE ALRT_KEY=$i AND SWEEP_IND='Y' AND ALRT_LOG_KEY=j.ALRT_LOG_KEY;
END LOOP;
print_clob(vAlrtKeys);
END;
/
ENDSQL`

#LISTAGG not used because the aggregation of all the data may exceed 4000 characters. LISTAGG returns VARCHAR2(4000). XMLAGG can be used but can be difficult to manage
MailBody=""
for j in $MailBodyAggrCursor
do
SelStmt="SELECT '<tr>'||CHR(10)||"
for m in {1..$FldCnt}
do
SelStmt=$SelStmt"'<td>'||regexp_substr(col_data,'[^|]+',1,$m)||'</td>'||CHR(10)||"
done
SelStmt=$SelStmt"'</tr>'||CHR(10) FROM ALRT_LOG_TBL WHERE ALRT_LOG_KEY=$j;"

echo $SelStmt > TmpAlrtSql.sql

MailBody=$MailBody`sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
@TmpAlrtSql.sql
ENDSQL`
done

echo $MailBody >> ${log_dir}/TmpMailFile
echo "</table>" >> ${log_dir}/TmpMailFile
echo "<p>Thank You</p></body></html>" >> ${log_dir}/TmpMailFile

cat ${log_dir}/TmpMailFile | /usr/sbin/sendmail -t

done

sqlplus -s ${OID}/${OPSWD}<<ENDSQL
set head off
set linesize 400
set pagesize 0 feedback off verify off  heading off echo off trimspool on colsep |
UPDATE ALRT_LOG_TBL
SET SWEEP_IND='N';
COMMIT;
EXIT;
/
ENDSQL
}
################################################################################
#
# Begin Here:
#  - process cmdline flags
#  - set env vars
#  - cleanup old files
#
################################################################################

#  process cmdline flags
while getopts e:h val
do
   case $val in
        e)      eflag=1;
                export env=`echo ${OPTARG} | tr "[a-z]" "[A-Z]"`;;
        h)      hflag=1;;       # help
        *)      Usage $0;;
   esac
done

if [ "$hflag" ]; then
   head -$(($(grep -n "Modification History" $0 | sed 2,\$d | \
   cut -f1 -d:)-1)) $0
   Usage $0
fi

[[ -z "$eflag" ]] && printf "Option -e must be specified\n" && Usage $0

set -o xtrace

#Conceal passwords in an environment file and source the file within the script
export OID=UserName@ServerName
export OPSWD=password
export OSCH=SchemaName
export script='alert_generator'
export dttm=`date '+%Y%m%d%H%M%S'`
export log_dir=${log_dir}/EDI852
export status='Success'


exec > ${log_dir}/${script}_${dttm}.log 2>&1
FileCLNUP ${script}
GenerateAlert