cancel
Showing results for 
Search instead for 
Did you mean: 

Need to run opscenter query from command line

Dollypee
Moderator
Moderator
   VIP    Certified

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.

1 ACCEPTED SOLUTION

Accepted Solutions

Krutons
Moderator
Moderator
   VIP   

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.

View solution in original post

7 REPLIES 7

Krutons
Moderator
Moderator
   VIP   

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
Moderator
Moderator
   VIP    Certified

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
Moderator
Moderator
   VIP   

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?

Dollypee
Moderator
Moderator
   VIP    Certified

@Krutons yes. That's correct

Dollypee
Moderator
Moderator
   VIP    Certified

hi @Krutons , I supplied all credential above and also specify to connect to vxpmdb database.  But got error " database could not be found. 

Dollypee
Moderator
Moderator
   VIP    Certified

I figured it out now. Was a mistake from my end. Am all good now. Thank you @Krutons 

Krutons
Moderator
Moderator
   VIP   

Odd that it runs normally through that method but not in the Web GUI. I would suggest opening a ticket with Veritas to diagnose the underlying issue.

Glad I was able to help with an alternate method though.