Custom Query in Ops Center

[Alex_Vasquez]

Hello there,

I'm attempting to create a query in Ops Center to accomplish the following report: Client name, policy name, backup window, retention period

The problem is that I know jack about SQL queries and I don't see any way of creating this report outside of a custom query. Any advice or pointers here are appreciated. Thank you.

[Bryan_Proctor]

Found the schema.

There is a schema on the windows server in x:\program files\symantec\OpsCenter\db in a zip file called db_sqls.zip.  In that file under the 70 directory you will find a file call schema.xml. 

[Symboy]

You can also try "SP_Tables" script as a SQL Query . that will list all OC views and tables . You can then create your SQL Queries . 

[NLeeds]

This is a little tricky to get all in one shot.  On my version of OpsCenter (7.0.1), I have a rentension level field in the domain_schedule table, but it's completely null.  I know OpsCenter knows about retension levels because I can see them when I go to Monitor, Policies, click on a policy name then click the schedule tab down below.

Here's a query to get the rest of what you want.  OpsCenter stores everything in number of milliseconds since the Gregorian epoch (?!?) instead of the standard Unix epoch, so I have to do a lot of conversion trickery to get human readable numbers.  For interested parties, use the UTCBigIntToUTCTime() SQL function to convert the dates to normal.

SELECT ds.policyName,
dpc.clientName,
CASE
    WHEN ds.day = 0
        THEN 'Sun'
    WHEN ds.day = 1
        THEN 'Mon'
    WHEN ds.day = 2
        THEN 'Tue'
    WHEN ds.day = 3
        THEN 'Wed'
    WHEN ds.day = 4
        THEN 'Thu'
    WHEN ds.day = 5
        THEN 'Fri'
    WHEN ds.day = 6
        THEN 'Sat'
END AS BackupWindowDay,
RIGHT(CONVERT(varchar(19),(UTCBigIntToUTCTime(startOffset+135131328000000000))),8) AS StartTime,
CONVERT(decimal(10,2),(duration-startoffset)/36000000000) as DurationInHours
FROM domain_schedulewindow ds
    INNER JOIN
    domain_Policy dp ON (ds.policyname = dp.name
                     AND ds.policyVersionNo = dp.versionNo
                     AND dp.status=0 )
    INNER JOIN
    domain_PolicyClient dpc ON (dpc.policyname = dp.name
                            AND dpc.policyVersionNo = dp.versionNo )
WHERE duration > 0
ORDER BY 1,2,ds.day