Forum Discussion

Dollypee's avatar
Dollypee
Moderator
6 years ago

Need to run opscenter query from command line

Hello All,

My opscenter analytic version is 8.1.2

Can any one point me to how I can run an opscenter query from the opscenter server db itself? Not from the web console. 

The steps involve and any other pre-task. Thank you in advance.

  • What are you trying to do?

    You could run an SQL query from the server by using dbisqlc located at the following location
    C:\Program Files\Symantec\OpsCenter\server\db\WIN64

    You would need to know how to connect to the DB.
    USERID- DBA
    PASSWORD- SQL (unless you've changed it)
    Server Name- opscenter_<server name>
    Database Name- vxpmdb

    I would stress that you use extreme caution when doing this if you aren't 100% certain on what you are doing.

  • What are you trying to do?

    You could run an SQL query from the server by using dbisqlc located at the following location
    C:\Program Files\Symantec\OpsCenter\server\db\WIN64

    You would need to know how to connect to the DB.
    USERID- DBA
    PASSWORD- SQL (unless you've changed it)
    Server Name- opscenter_<server name>
    Database Name- vxpmdb

    I would stress that you use extreme caution when doing this if you aren't 100% certain on what you are doing.

    • Dollypee's avatar
      Dollypee
      Moderator

      Hi Krutons Thanks for your quick reply. So I have SQL query which aren't displaying all information when I run it from opscenter web link. For some reason, the report are only limiting to 50 lines. I've done all sort of troubleshooting steps link increasing the value in "report.schedule.max.tabular.rows" to a higher value . But no luck. So am attempting to run it from db end I perse the report to a txt file.   Here's the sample of the query which you happen to actually provide on another discussion link : https://vox.veritas.com/t5/OpsCenter/opscenter-customized-sccript-issue/td-p/864366

      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.Id 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.Id 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.Id 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.id 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.Id 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,200,230,239,240,800,1000) AND
      A.policyName NOT LIKE 'SLP_%' AND A.policyType = 40 AND
      A.scheduletype not in (-1,2) AND
      A.clientName NOT LIKE '<media server>' AND A.clientName NOT LIKE '<media server>' AND A.clientName NOT LIKE '<media server>' AND
      A.clientName not in ('server lists') AND -- NBU MASTER Servers
      A.clientName not in ('server lists') AND -- NBU site1 MEDIA 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';

       

      • Krutons's avatar
        Krutons
        Moderator

        Dollypee, I assume other reports give more than 50 lines and that the only report that is currently not working as expected this this one right?