generate a custom report using OpsCenter Analytics

There you go, with bonus duration and policy type.

SELECT
domain_ScheduleWindow.policyName as "Policy",
lookup_PolicyType.name as "Type",
domain_ScheduleWindow.scheduleName as "Schedule",
domain_ScheduleWindow.day  as "Day",
(CASE
	WHEN domain_ScheduleWindow.duration != 0 THEN 
		DATEFORMAT(UTCBigIntToNomTime(NomTimeToUTCBigInt(
		DATE(convert(CHAR(20),GETDATE(), 101))
		)+domain_ScheduleWindow.startOffset),'hh:nn:ss')
	ELSE '-'
END) as "Start Time",

(CASE
	WHEN domain_ScheduleWindow.duration > 864000000000  THEN 
		CAST(
		DATEDIFF(hour,
		DATEADD(day, 1, DATE(convert(CHAR(20),GETDATE(), 101))),
		UTCBigIntToNomTime(NomTimeToUTCBigInt(
			DATE(convert(CHAR(20),GETDATE(), 101))
			)+domain_ScheduleWindow.duration ))
		AS CHAR(20))
WHEN domain_ScheduleWindow.duration = 864000000000  THEN '24'
WHEN domain_ScheduleWindow.duration < 864000000000  THEN 
	CAST(
		DATEDIFF(hour, DATE(convert(CHAR(20),GETDATE(), 101)),
			UTCBigIntToNomTime(NomTimeToUTCBigInt(
				DATE(convert(CHAR(20),GETDATE(), 101)))+domain_ScheduleWindow.duration )
		) 
		AS CHAR(20))
ELSE  '0'
END) as "Duration, hrs"

FROM
domain_ScheduleWindow, domain_Policy, domain_Schedule, lookup_PolicyType, nb_Policy

WHERE domain_Policy.masterServerId = domain_Schedule.masterServerId 
AND domain_Policy.name =  domain_Schedule.policyName
AND domain_Policy.policyDomainName = domain_Schedule.policyDomainName
AND domain_Policy.versionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.masterServerId = domain_Schedule.masterServerId 
AND domain_ScheduleWindow.policyName =  domain_Schedule.policyName
AND domain_ScheduleWindow.policyVersionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.scheduleName = domain_Schedule.name
AND domain_Policy.name = nb_Policy.name 
AND domain_Policy.masterServerId = nb_Policy.masterServerId  
AND domain_Policy.versionNo = nb_Policy.versionNo  
AND domain_Policy.policyDomainName = nb_Policy.policyDomainName  
AND lookup_PolicyType.id = domain_Policy.type 

AND domain_Policy.status = 0
AND domain_Policy.active = 1

ORDER BY "Policy", "Schedule", "Day"

**Assuming have you have one master, otherwise you must either filter by it or include master into the output.

Hi this post seems to refer to the Tabular Backup Report, which would contain policy name, schedule and start time (along with other fields too)

https://www-secure.symantec.com/connect/blogs/opscenter-reports-quick-howto

SQL in attachment, its too big for the post.

Much cleaner version of SQL in attachment. Also now it accounts for duration over 24hrs.

Good post and great deliverable from VoropaevPavel.

Hi again, incase you do not have opscenter analytic to execute V's query, here's a similar commands that can be ran on master server.  bpplsched <poliyc_name> -v -L. Please see attached for a similar outputs.

Hi Pavel,

Small typo in your script in the convert line. Its says 1000 its should be 10000000.

Cheers

SELECT
domain_ScheduleWindow.policyName as "Policy",
lookup_PolicyType.name as "Type",
domain_ScheduleWindow.scheduleName as "Schedule",
(CASE domain_ScheduleWindow.day
    WHEN 0 THEN 'Sunday'
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
END) AS "Weekday",

convert(varchar(8), dateadd(SECOND, domain_ScheduleWindow.startOffset/10000000, '1970-01-01'), 108) as "Start Time",

(SELECT RIGHT('00' + CAST(CAST(floor((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000/ 3600) as NUMERIC(3)) as CHAR(3)), 2)) + ':' +
(SELECT RIGHT('00' + CAST(CAST(floor((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000/ 60) % 60 as NUMERIC(2)) as CHAR(2)), 2)) +':' +
(SELECT RIGHT('00' + CAST(CAST((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000% 60 as NUMERIC(2)) as CHAR(2)), 2)) as "Duration"

FROM
domain_ScheduleWindow, domain_Policy, domain_Schedule, lookup_PolicyType, nb_Policy

WHERE domain_Policy.masterServerId = domain_Schedule.masterServerId
AND domain_Policy.name =  domain_Schedule.policyName
AND domain_Policy.policyDomainName = domain_Schedule.policyDomainName
AND domain_Policy.versionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.masterServerId = domain_Schedule.masterServerId
AND domain_ScheduleWindow.policyName =  domain_Schedule.policyName
AND domain_ScheduleWindow.policyVersionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.scheduleName = domain_Schedule.name
AND domain_Policy.name = nb_Policy.name
AND domain_Policy.masterServerId = nb_Policy.masterServerId  
AND domain_Policy.versionNo = nb_Policy.versionNo  
AND domain_Policy.policyDomainName = nb_Policy.policyDomainName  
AND lookup_PolicyType.id = domain_Policy.type

AND domain_Policy.status = 0
AND domain_Policy.active = 1

ORDER BY "Policy", "Schedule", "Day"

This is very close to what i am needing, however i need to add the clientname, how would you go about adding that?  I am also using it as select distinct.

Here is what i came up with:

SELECT DISTINCT
domain_Job.clientname as "Client Name",
domain_ScheduleWindow.policyName as "Policy",
lookup_PolicyType.name as "Type",
domain_ScheduleWindow.scheduleName as "Schedule",
(domain_schedule.frequency/86400) as "Frequency_Days",
nb_RetentionLevel .label as "Retention Period",

convert(varchar(8), dateadd(SECOND, domain_ScheduleWindow.startOffset/10000000, '1970-01-01'), 108) as "Window Open",

(SELECT RIGHT('00' + CAST(CAST(floor((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000/ 3600) as NUMERIC(3)) as CHAR(3)), 2)) + ':' + 
(SELECT RIGHT('00' + CAST(CAST(floor((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000/ 60) % 60 as NUMERIC(2)) as CHAR(2)), 2)) +':' + 
(SELECT RIGHT('00' + CAST(CAST((domain_ScheduleWindow.duration-domain_ScheduleWindow.startOffset )/10000000% 60 as NUMERIC(2)) as CHAR(2)), 2)) as "Duration"

FROM
domain_ScheduleWindow, domain_Policy, domain_Schedule, lookup_PolicyType, nb_Policy, domain_Job, nb_job, nb_RetentionLevel 

WHERE 

domain_Policy.masterServerId = domain_Schedule.masterServerId 
AND "Window Open" != "Duration"
AND domain_Policy.name =  domain_Schedule.policyName
AND domain_Policy.policyDomainName = domain_Schedule.policyDomainName
AND domain_Policy.versionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.masterServerId = domain_Schedule.masterServerId 
AND domain_ScheduleWindow.policyName =  domain_Schedule.policyName
AND domain_ScheduleWindow.policyVersionNo =  domain_Schedule.policyVersionNo
AND domain_ScheduleWindow.scheduleName = domain_Schedule.name
AND domain_Policy.name = nb_Policy.name 
AND domain_Policy.masterServerId = nb_Policy.masterServerId  
AND domain_Policy.versionNo = nb_Policy.versionNo  
AND domain_Policy.policyDomainName = nb_Policy.policyDomainName  
AND lookup_PolicyType.id = domain_Policy.type 
AND DATEDIFF(hour,UTCBigIntToNomTime(domain_Job.endTime), GETDATE()) <= 96
AND domain_job.policyname = domain_Schedule.policyName
AND domain_job.clientname = nb_job.clientname
AND nb_RetentionLevel.id = nb_job.RetentionLevel
AND nb_RetentionLevel.masterserverid = nb_job.masterserverid
AND domain_Policy.status = 0
AND domain_Policy.active = 1

AND domain_Job.clientname ='wcw30506'

ORDER BY "Client Name", "Policy", "Schedule"