04-30-2019 12:53 PM
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.
Solved! Go to Solution.
04-30-2019 01:04 PM
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.
04-30-2019 01:04 PM
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.
04-30-2019 01:22 PM
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';
04-30-2019 01:32 PM
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?
04-30-2019 01:34 PM
@Krutons yes. That's correct
05-01-2019 07:39 AM - edited 05-01-2019 07:40 AM
hi @Krutons , I supplied all credential above and also specify to connect to vxpmdb database. But got error " database could not be found.
05-01-2019 09:42 AM
I figured it out now. Was a mistake from my end. Am all good now. Thank you @Krutons
05-02-2019 10:07 AM
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.