cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Daily Failure Report

I have the following report but it seems to only display the first 50 lines.  I cant seem to find a reason as to why it would do that.  I have also tryed changing from a select distict to select 500 starting at 1, but that did not make a difference.

 

Here is the code i am using:

 

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST( DISTINCT A.statuscode) as 'Status Code',
            (       select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful' 
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = CKP.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ),
            (       select  UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
                    from   domain_JobArchive BKP
                    where BKP.masterServerId = A.masterServerId AND
                              BKP.Id = BKP.parentJobId AND
                              BKP.clientId = A.clientId AND
                              BKP.policyId = A.policyId AND
                              BKP.policyName = A.policyName AND
                              BKP.policyType = A.policyType AND
                             ( BKP.statusCode = 0 OR   BKP.statusCode = 1  ) )
FROM  "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
          DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
(         select  DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
                    from   domain_JobArchive CKP
                    where CKP.masterServerId = A.masterServerId AND
                              CKP.id = A.parentJobId AND
                              CKP.clientId = A.clientId AND
                              CKP.policyId = A.policyId AND
                              CKP.policyName = A.policyName AND
                              CKP.policyType = A.policyType AND
                              CKP.scheduleType = A.scheduleType AND
                              ( CKP.statusCode = 0 OR   CKP.statusCode = 1  )  ) > 0 AND
A.statusCode not in (0,1,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2)
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid
ORDER BY B.friendlyName, 'Days since Last successful'

1 Solution

Accepted Solutions
Accepted Solution!

Re: OpsCenter Daily Failure Report

The case really did not go anywhere.  But this is what i am using today and it seems to be working forme.  This should do the trick i belive..

 

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST( DISTINCT A.statuscode) as 'Status Code',
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ),
( select UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
from domain_JobArchive BKP
where BKP.masterServerId = A.masterServerId AND
BKP.Id = BKP.parentJobId AND
BKP.clientId = A.clientId AND
BKP.policyId = A.policyId AND
BKP.policyName = A.policyName AND
BKP.policyType = A.policyType AND
( BKP.statusCode = 0 OR BKP.statusCode = 1 ) ),
( select DATEDIFF(hour,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Hours since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
--CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) )
FROM "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = A.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND
A.statusCode not in (0,1,150,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2) AND
A.clientName not in ('server lists') AND -- NBU MASTER Servers
A.clientName not in ('server lists') AND -- NBU site1MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site2 MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site3 MEDIA SERVERS
A.clientName not in ('server lists') -- NBU site4 MEDIA SERVERS
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,statuscode
ORDER BY B.friendlyName, 'Days since Last successful';

output to e:\data\scripts\output\result.txt;
go

 

I then parse this with powershell to send tickets to our ticket system.

View solution in original post

11 Replies

I'm in the same situation

I'm in the same situation (trying to use the same SQL).  I can't see a reason for it to be limited to the first 50 lines, but it is.  What's interesting is that when I run the report the totals (total rows, number of pages) seem to be correct (over 2,000 rows and 40+ pages), but the actual output is just the first 50 rows.

Re: OpsCenter Daily Failure Report

I submitted a case for it but that got shutdown due to its custom reporting.  Have you came up with a solution yet?

 

I have played a lot with sql paging commands that you see in their reports but no luck with that so far.  

Re: OpsCenter Daily Failure Report

Hi.

Have you edit the line report.schedule.max.tabular.rows in C:\Program Files\Symantec\OpsCenter\server\config\report.conf

Remember to restart OpsCenter services after.

Re: OpsCenter Daily Failure Report

Its currently set prety high.  Services had been restarted resently as well.

 

#Last Updated on
#Thu Sep 10 11:22:21 CDT 2015
report.schedule.max.tabular.rows=400000
report.threadPoolMaxSize=20
report.chart.temp.folder=temp
report.charting.engine=com.symantec.nbu.web.common.renderers.chart.jfree.view.ChartRenderer
report.export.header.image.path=images/symantec-phoenix-img.png
report.threadPoolCoreSize=20
report.export.folder=C\:\\PROGRA~1\\Symantec\\OPSCEN~1\\server\\export
report.images.folder=images

 

Re: OpsCenter Daily Failure Report

Remove DISTINCT from the select. Its purpose is to remove duplicates from the result.

Re: OpsCenter Daily Failure Report

What version of OpsCenter are you running?

Re: OpsCenter Daily Failure Report

i am running 7.7.1.

 

If i remove distinct i would have duplicate entries.  

 

 

Re: OpsCenter Daily Failure Report

I found that this must be a problem with opscenter.  The case has been pushed back to backline now.  I was able to find how to run the query from cli and could prove it was not the query.

 

Here is what i wrote for powershell to do the task:

 

# Objective - To provide SDR's for failed backups from the privious 24 hours
# Author - Nick Britton

#Vars
$LD_LIBRARY_PATH="C:\Program Files\Symantec\OpsCenter\server\lib"
$machinename='oma00vcntbkup01'
$DBACCESSSERVER='OPSCENTER_OMA00VCNTBKUP01'
$INSTALLATIONDIR="C:\Program Files\Symantec\OpsCenter\server"
$EXECDBACCESS="C:\Program Files\Symantec\OpsCenter\server\db\WIN64\"
$DBACCESSPORT=13786
$DBACCESSUSER='DBA'
$DBACCESSPWD='SQL'
#$QUERYFILENAME='c:\data\query_test.sql'
#$QUERYFILENAME='c:\data\query_table.sql'
$QUERYFILENAME='c:\data\query_child_test.sql'
$DBACCESSNAME='vxpmdb'
$date = (get-date).tostring("yyyyMMdd")


# Execute command to run report

& "$execdbaccess\dbisqlc" -q -c "ENG=$DBACCESSSERVER;DBN=$DBACCESSNAME;LINKS=all;UID=$DBACCESSUSER;PWD=$DBACCESSPWD" $QUERYFILENAME |Out-Null
 

These two forum postings helped me get there:

 

To setup ODBC for opscenter:

 

https://www.veritas.com/support/en_US/article.TECH141827

 

Run sql from cli:

 

 

https://www.veritas.com/community/blogs/custom-sql-query-asa-db-opscenter-generate-monthly-success-r...

 

 

Re: OpsCenter Daily Failure Report

Good work

Re: OpsCenter Daily Failure Report

Did your case with backline ever get anywhere?  I'm having this same issue, with this same report... It's a shame because this would be such a good report if I could get it to display anymore than 1 page...

Accepted Solution!

Re: OpsCenter Daily Failure Report

The case really did not go anywhere.  But this is what i am using today and it seems to be working forme.  This should do the trick i belive..

 

SELECT DISTINCT
B.friendlyName as 'Master Server',
A.clientName as 'Client Name',
A.policyName as 'Policy Name',
LIST( DISTINCT A.statuscode) as 'Status Code',
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ),
( select UtcBigIntToNomTime(MAX(BKP.endTime)) as 'Last successful'
from domain_JobArchive BKP
where BKP.masterServerId = A.masterServerId AND
BKP.Id = BKP.parentJobId AND
BKP.clientId = A.clientId AND
BKP.policyId = A.policyId AND
BKP.policyName = A.policyName AND
BKP.policyType = A.policyType AND
( BKP.statusCode = 0 OR BKP.statusCode = 1 ) ),
( select DATEDIFF(hour,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Hours since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
--CKP.id = CKP.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) )
FROM "domain_JobArchive" A , "domain_masterserver" B
WHERE
A.masterServerId = B.id AND A.id = A.parentjobid AND
DATEDIFF(hour,UtcBigIntToNomTime(A.endTime), GETDATE()) <= 24
AND
( select DATEDIFF(DAY,UtcBigIntToNomTime(MAX(CKP.endTime)),GETDATE()) as 'Days since Last successful'
from domain_JobArchive CKP
where CKP.masterServerId = A.masterServerId AND
CKP.id = A.parentJobId AND
CKP.clientId = A.clientId AND
CKP.policyId = A.policyId AND
CKP.policyName = A.policyName AND
CKP.policyType = A.policyType AND
CKP.scheduleType = A.scheduleType AND
( CKP.statusCode = 0 OR CKP.statusCode = 1 ) ) > 0 AND
A.statusCode not in (0,1,150,190,199,230,239,240,800,1000) AND
A.policyName NOT LIKE 'SLP_%' AND A.type IN (0,22,28) AND
A.scheduletype not in (-1,2) AND
A.clientName not in ('server lists') AND -- NBU MASTER Servers
A.clientName not in ('server lists') AND -- NBU site1MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site2 MEDIA SERVERS
A.clientName not in ('server lists') AND -- NBU site3 MEDIA SERVERS
A.clientName not in ('server lists') -- NBU site4 MEDIA SERVERS
Group by A.clientName,A.masterServerId,A.policyName,B.friendlyName,policyType,scheduleType,clientid,policyid,statuscode
ORDER BY B.friendlyName, 'Days since Last successful';

output to e:\data\scripts\output\result.txt;
go

 

I then parse this with powershell to send tickets to our ticket system.

View solution in original post