generate a custom report using OpsCenter Analytics

NBU 7.6.0.3

 

it seems Symantec has left out an obvious report that is needed by many admins. i'm referring to this link which is also looking for the same custom report i am looking:

https://www-secure.symantec.com/connect/forums/how-generate-report-all-backup-jobspolicies-schedules...

 

i too have tried the custom reports but i cannot get it to report to what i need which is:

Backup policy name Schedule (daily/weekly/monthly) Start Time
Exchange Backup Daily 23:00
Oracle Backup Weekly 23:30

 

if one has a report like that, it would be easy to see if my backup schedules are overlapping or there are some gaps.

9 Replies
Highlighted

There you go, with bonus

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.

Highlighted

Hi this post seems to refer

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

Highlighted

SQL in attachment, its too

SQL in attachment, its too big for the post.

Highlighted

Much cleaner version of SQL

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

 

Highlighted

Good post and great

Good post and great deliverable from VoropaevPavel.

Highlighted

Hi again, incase you do not

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.

 

 

Highlighted

Hi Pavel, Small typo in your

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"

Highlighted

This is very close to what i

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.

Highlighted

Here is what i came up

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"