Code:
xpgm=$( basename $0 )
xexecdate=$1
xstarttime=$2
xendtime=$3
xserver=$4
xuserid=`../src/get_signon.s userid $xserver`
xpasswd=`../src/get_signon.s password $xserver`
xemailist=`cat $xpgm.email`
rm -f ../data1/$xpgm.txt
rm -f ../data/perf_stats_xserver.txt
#echo "Service Id Start Time End Time lt_300ms 300-500ms 500_1000ms 1000_2000ms gt_2000 Total Min Exec Time Max Exec Time %lt 300ms %300-500ms %500-1000ms %1000-2000ms %gt 2
000ms">../data/perf_stats_$xserver.xls
> ../data/$xpgm.$xserver.csv
while read serviceid logtable dbname
do
rm -f ../data1/$xpgm.txt1
./sql_data.pl -S $xserver -U $xuserid -P $xpasswd -e -t "," -o ../data1/$xpgm.txt1 <<-EOF
use soc
go
select
service_id,
'$xexecdate',
'$xstarttime',
'$xendtime',
--a_lt_300,
--b_300_500,
--c_500_1000,
--d_1000_2000,
--e_gt_2000,
total,
convert(decimal(5,2),(convert(decimal,a_lt_300)/convert(decimal,total))*100) pc_a_lt_300,
convert(decimal(5,2),(convert(decimal,b_300_500)/convert(decimal,total))*100) pc_b_300_500,
convert(decimal(5,2),(convert(decimal,c_500_1000)/convert(decimal,total))*100) pc_c_500_1000,
convert(decimal(5,2),(convert(decimal,d_1000_2000)/convert(decimal,total))*100) pc_d_1000_2000,
convert(decimal(5,2),(convert(decimal,e_gt_2000)/convert(decimal,total))*100) pc_e_gt_2000,
min_exec,
max_exec,
avg_exec
from (
select service_id, sum(a_lt_300) a_lt_300,sum(b_300_500) b_300_500,sum(c_500_1000) c_500_1000, sum(d_1000_2000) d_1000_2000, sum(e_gt_2000) e_gt_2000,count(*) total, min(exec_time) min_exec,
max(exec_time) max_exec,avg(exec_time) avg_exec from (
select service_id,
case when exec_time <= 300 then 1 else 0 end a_lt_300,
case when exec_time <= 500 and exec_time > 300 then 1 else 0 end b_300_500,
case when exec_time <= 1000 and exec_time > 500 then 1 else 0 end c_500_1000,
case when exec_time <= 2000 and exec_time > 1000 then 1 else 0 end d_1000_2000,
case when exec_time > 2000 then 1 else 0 end e_gt_2000,
exec_time
from
(
select service_id, datediff(ms, startdate, enddate) exec_time
from $dbname..$logtable where startdate >='$xexecdate $xstarttime' and enddate <='$xexecdate $xendtime'
and service_id in ('$serviceid')
) a
) b
group by service_id
) c
go
EOF
cat ../data1/$xpgm.txt1 >> ../data/$xpgm.$xserver.csv
done < $xpgm.cfg
rm -f ../data1/$xpgm.txt1
nawk 'BEGIN{
FS=","
print "MIME-Version: 1.0"
print "Content-Type: text/html"
print "Content-Disposition: inline"
print "<HTML>""<TABLE border="2"><TH>Service ID</TH><TH>Run Date</TH><TH>Start Time</TH><TH>End Time</TH><TH>Total Executions</TH><TH>% \<=300 ms</TH> <TH>% 300-500 ms</TH><TH>% 500-1000 ms</TH><TH>% 1000-2000ms</TH><TH>% \> 2 Sec </TH>
<TH>Min Exec Time</TH> <TH>Max Exec Time</TH><TH>Average Exec Time</TH>"
}
{
printf "<TR>"
for(i=1;i<=NF;i++)
printf "<TD>%s</TD>", $i
print "</TR>"
}
END{
print "</TABLE></BODY></HTML>"
}
' ../data/$xpgm.$xserver.csv > ../data/$xpgm.$xserver.html
../src/send_mail.s ../data/$xpgm.$xserver.html "SP Stats on $xserver for $xexecdate from $xstarttime to $xendtime " $xemailist
