09-13-2015 02:43 PM
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'
Solved! Go to Solution.
12-12-2016 08:43 AM
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.
09-15-2015 01:51 PM
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.
12-04-2015 03:18 PM
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.
12-07-2015 12:34 AM
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.
12-07-2015 06:49 AM
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
12-09-2015 08:47 PM
Remove DISTINCT from the select. Its purpose is to remove duplicates from the result.
12-10-2015 09:15 AM
What version of OpsCenter are you running?
02-05-2016 05:40 AM
i am running 7.7.1.
If i remove distinct i would have duplicate entries.
02-09-2016 07:07 AM
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:
02-09-2016 07:57 AM
Good work
06-07-2016 04:02 PM
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...
12-12-2016 08:43 AM
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.