Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Operations Center issue running custom reports

Created: 03 Sep 2013 | 5 comments

Does anyone have any information on running custom reports and getting either unknown exception error or invalid query responses for a custom report that worked once?

I randomly get these messages in 2 of my environments even though I have been able to run this report on multiple Operations Center databases ( I have 5 different ones). I have also run this report once successfully on the database where I randomly get these errors. My second database where i get these errors, the report has never run successfully and I have over 5 million tables here.

This is the report I am trying to run:

SELECT  B.friendlyName as 'Master Server Name',
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Number of days since Last successful'
                    from   DBA.domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.clientId = A.clientId AND 
                              CKP.policyId = A.policyId AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
          A.clientName as 'Client Name',
          A.policyName as 'Policy Name',
          A.scheduleName as 'Schedule Name',
          UtcBigIntToNomTime(A.endTime) as 'Date of failure',
          A.statusCode as 'Status Code',
          (         select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   DBA.domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              BKP.clientId = A.clientId AND 
                              BKP.policyId = A.policyId AND
                              BKP.policyType = A.policyType AND
                              BKP.scheduleType = A.scheduleType AND
                             ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
          A.masterServerId = B.id AND
          DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 120
          AND A.statusCode > 1 AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Number of days since Last successful'
                    from   DBA.domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.clientId = A.clientId AND 
                              CKP.policyId = A.policyId AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0
ORDER BY B.friendlyName , 'Number of days since Last successful' ;
 

Operating Systems:

Comments 5 CommentsJump to latest comment

Dave High's picture

Hi Lisa,

What version of OpsCenter and NetBackup? I copy/pasted your query into a 7.5.0.6 instance and it ran fine. Are you saying that even on the same instance sometimes it randomly fails?

I am not a SQL expert but again - this does work on a 7.5.0.6 instance. One of the older versions of OpsCenter did have an issue sometimes with random SQL failures when the DB was busy. I dont recall the OC version but it was quite old.

Dave

 

lisareinhart's picture

7.5.0.6 using W2008 and yes, even in the same instance it fails.  I have not been able to get it to run since it first ran in the same environment. The enivronment where it first ran has over 1 million tables.

sgt_why's picture

Let me help ... REMOVE THE SEMI COLON ... yes, I realize its the correct syntax ... but leaving the trailing SEMI-COLON will cause all your reports to not paginate properly.

 

i.e. If the output has multiple pages, each page simply shows the first pages data ... sad, but true.

 

remove the semi-colon and re-run and the issue goes away ...

 

would have been nice if they added logic for this into their WebGUI

sgt_why's picture

yes, just to confirm -- I was able to cut-n-paste your report (minus the ;) and run it with no issues.

(Windows 2008 R2 server, Ops Center 7.5.0.6 with 7.5.0.3 master servers)

If its still failing, maybe considering the size of your databases ... could it be running out of available memory ?

lisareinhart's picture

I have 256GB of memory. One of the reports that behaves like this is the Stored Image report, so there is no semi colon to remove.  My database has now grown to 125GB with 70Gb allowed for Sybase.