03-15-2015 03:58 AM
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:
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.
03-15-2015 08:43 AM
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.
03-15-2015 11:35 AM
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
03-15-2015 10:52 PM
SQL in attachment, its too big for the post.
03-16-2015 01:28 AM
Much cleaner version of SQL in attachment. Also now it accounts for duration over 24hrs.
05-23-2015 07:18 AM
Good post and great deliverable from VoropaevPavel.
05-23-2015 07:43 AM
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.
05-26-2015 06:04 AM
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"
07-24-2015 01:03 PM
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.
07-24-2015 02:31 PM
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"