Objective:
To have a daily backup success rate report for the current month through OpsCenter custom query
The available report templates either have partial successful job accounted as failures or are listed separately, they can however be excluded but then its brings down the success rate. Reports to the management should be precise and must not include much technical detailed information., Just present them with only success & failure.
Requirement:
I came up with this article to share how we can connect to ASA DB , run custom SQL query and generate reports of our choice.
Everything that’s readymade is as per mass need which may not suit an individuals requirement, such tricks help to achieve our objectives filtering out the unwanted stuff. So MY way better than HIGHWAY.
when domain_Job.state =3 and domain_Job.statusCode =73 then 0
http://www.symantec.com/business/support/index?page=content&id=TECH156223
My query looks something like this. I however struggled with dates [ because logs show time in some wiered 18digits]. Thx to the forum and i was able to make this working
select COUNT(domain_Job.id) as "domain_Job.id", (case when domain_Job.state =106 then 3 when domain_Job.state =3 and domain_Job.statusCode =0 then 0 when domain_Job.state =3 and domain_Job.statusCode =1 then 0 when domain_Job.state =3 and domain_Job.statusCode =73 then 0 else 2 end) as "jobMonitotExitStatus", lookup_JobStatus.name as "lookup_JobStatus.name"
from domain_Job , lookup_JobStatus where ( (domain_Job.state = 3) AND (domain_Job.isValid = 1) ) AND ( ( ( (UtcBigIntToNomTime(domain_Job.startTime) BETWEEN getdate() -1 and getdate() ) OR (UtcBigIntToNomTime(domain_Job.endTime) BETWEEN getdate() - 1 and getdate() ) ) OR ( (UtcBigIntToNomTime(domain_Job.startTime) < getdate()) AND (domain_Job.endTime = getGregorianConstant()) ) ) AND ( (domain_Job.masterServerId IN (56,1780,175176,173720,93083 )) ) ) AND ( (lookup_JobStatus.id = jobMonitotExitStatus) ) GROUP BY"jobMonitotExitStatus", "lookup_JobStatus.name"
ORDER BY "lookup_JobStatus"."name" ASC , "jobMonitotExitStatus" ASC; |
Once the query is ready, add the following commands to the query to output the result to a file
output to /opscenter/scripts/output/success.txt; go |
#!/bin/bash export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:\opt/SYMCOpsCenterServer/db/lib machinename=`hostname` DBACCESSSERVER="OPSCENTER_$machinename"
INSTALLATIONDIR=/opt/SYMCOpsCenterServer EXECDBACCESS=$INSTALLATIONDIR/db/bin DBACCESSPORT=13786 DBACCESSUSER=DBA DBACCESSPWD=SQL DBACCESSNAME=vxpmdb QUERYFILENAME=/opscenter/scripts/query/success_rate.sql $EXECDBACCESS/dbisqlc -q -c "ENG=$DBACCESSSERVER;DBN=$DBACCESSNAME;LINKS=all;UID=$DBACCESSUSER;PWD=$DBACCESSPWD" $QUERYFILENAME |
Process the captured data to calculate percentage. Append “Date”, “Total Job”, “Succesful” & “Percentage” to the monthly file and then call another script to generate html file. At last mail out the html report.
TOTAL_JOB=`cat /opscenter/scripts/output/success.txt | cut -d"," -f1 | xargs | tr ' ' + | bc` success_line=`cat /opscenter/scripts/output/success.txt | grep -i success` c=`echo $success_line | cut -d"," -f1` d=`expr $c \* 100` percent=`expr $d / $TOTAL_JOB` echo "`date +"%d-%b-%Y"`,$TOTAL_JOB,$c,$percent %" >> /opscenter/scripts/output/$(date +%b-%Y).txt /opscenter/scripts/generate_report.sh uuencode /opscenter/scripts/output/$(date +%b-%Y).html "$(date +%b-%Y).html" |mailx -s "Daily Backup Success Rate" |
#!/bin/bash saveIFS=$IFS # Put the Templates cp /opscenter/scripts/html_template /opscenter/scripts/output/$(date +%b-%Y).html saveIFS=$IFS IFS="," while read f1 f2 f3 f4 do echo "<td class="ltalignedtd" >" $f1 "</td>" "<td class="ltalignedtd" >" $f2 "</td>" "<td class="ltalignedtd" >" $f3 "</td>" "<td class="ltalignedtd" >" $f4 "</td> </tr>" >> /opscenter/scripts/output/$(date +%b-%Y).html done < /opscenter/scripts/output/$(date +%b-%Y).txt IFS=$saveIFS |
All the bits and pieces have been collected from various forums and tech articles. They all have be knit together to produce my desired results.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.