Forum Discussion

nbritton's avatar
nbritton
Level 4
9 years ago

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'

Backup and Recovery
Linux
OpsCenter
  • nbritton's avatar
    nbritton
    8 years ago

    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.

11 Replies

Sort By
  • Jason_Hoffman's avatar
    Jason_Hoffman
    Level 1
    9 years ago

    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.

  • nbritton's avatar
    nbritton
    Level 4
    9 years ago

    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.  

  • M_henriksen's avatar
    M_henriksen
    Level 4
    9 years ago

    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.

  • nbritton's avatar
    nbritton
    Level 4
    9 years ago

    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

     

  • nbritton's avatar
    nbritton
    Level 4
    9 years ago

    i am running 7.7.1.

     

    If i remove distinct i would have duplicate entries.  

     

     

  • nbritton's avatar
    nbritton
    Level 4
    9 years ago

    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-rate

     

     

  • tgray21's avatar
    tgray21
    Level 3
    9 years ago

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

    • nbritton's avatar
      nbritton
      Level 4
      8 years ago

      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.