cancel
Showing results for 
Search instead for 
Did you mean: 

Custom SQL query for ASA DB (OpsCenter) to generate Monthly Success rate

Satkay_Satish
Level 5
Certified

Objective:

To have a daily backup success rate report for the current month through OpsCenter custom query

Limitations with available report templates:
 

 

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.wink, Just present them with only success & failure.

Requirement:

 
  1. Include partial successful jobs into successful list
  2. 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
Approach:

 

  • 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. cool

 

Steps
 
 

 

  • 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.
devilwink