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:
- Include partial successful jobs into successful list
- Ability to add any other status code which occurs out of scope of backup team's job profile [ eg: Status 71,73 ] as successful jobs
- SQL Query: Modifying the existing query would have been an excellent solution but due to lack of subject expertise, I couldn’t achieve it.
- My Way: Snoop through the canned reports to build the query, run the query to extract data. process the data.
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. 
- Follow this technote to snoop the query from the canned report for daily success rate. Include whichever job status that you want to count as successful in the following line of the query.
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 |
- I now prepare for Database connection and run the query. Output is captured in temporary file which will be used later for processing
|
#!/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" |
- The HTML report generator script
|
#!/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.
