05-25-2015 08:51 AM
hi Team,
Please help to know, how to get the report having following information from ops center analytics 7.6.0.3 -
- backup policy name
- client name
- schedule start time of the backup policy
Solved! Go to Solution.
05-26-2015 06:02 AM
There is a small error in the script. It should be as below.
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"
05-25-2015 05:56 PM
refer https://www-secure.symantec.com/connect/forums/generate-custom-report-using-opscenter-analytics
05-26-2015 04:53 AM
thanks Rahul.
The script ran fine but its giving wrong start time.
I copy pasted the following script from the link -
policy_window_SQL_V2.txt
05-26-2015 06:02 AM
There is a small error in the script. It should be as below.
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"
05-26-2015 06:18 AM
great Riaan . Thanks a lot for help!!
its fine now.
05-26-2015 06:51 AM
Pleasure, Pavel did the hard work ;)
05-26-2015 11:59 PM
i forgot to ask , is there any possibility to include client machine name in the same script ?
05-27-2015 01:06 AM
Probably yes but I can imagine it would get really messy as It would then have line for every client in very policies on everyday. 4 policies, 7 Days is 28 Lines, add client to the mix and multiple that by say 50 clients in a big policies and you have over a 1000 lines
05-27-2015 01:37 AM
ok..thank you again .. :)
09-08-2016 03:15 AM
Hi Riaan,
Would it be possible to also show weekly/monthly schedules with calendar run days (based on policy name)
11-29-2016 12:13 AM
Hi,
I have a small query I dnt know abt the sql variable used for ops center how can we get the syntax for it?