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.
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.
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.
WHEN ds.day = 0
WHEN ds.day = 1
WHEN ds.day = 2
WHEN ds.day = 3
WHEN ds.day = 4
WHEN ds.day = 5
WHEN ds.day = 6
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
domain_Policy dp ON (ds.policyname = dp.name
AND ds.policyVersionNo = dp.versionNo
AND dp.status=0 )
domain_PolicyClient dpc ON (dpc.policyname = dp.name
AND dpc.policyVersionNo = dp.versionNo )
WHERE duration > 0
ORDER BY 1,2,ds.day