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.

1 Solution

Accepted Solutions
Accepted Solution!

Re: Need to run opscenter query from command line

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
Accepted Solution!

Re: Need to run opscenter query from command line

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

Re: Need to run opscenter query from command line

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';

 

Re: Need to run opscenter query from command line

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?

Re: Need to run opscenter query from command line

@Krutons yes. That's correct

Re: Need to run opscenter query from command line

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

Re: Need to run opscenter query from command line

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

Re: Need to run opscenter query from command line

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.