cancel
Showing results for 
Search instead for 
Did you mean: 

scheduled date/time of the backup policy

Brits
Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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"

View solution in original post

10 REPLIES 10

RahulG
Level 6
Employee

 refer https://www-secure.symantec.com/connect/forums/generate-custom-report-using-opscenter-analytics

Brits
Level 6

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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"

Brits
Level 6

great Riaan . Thanks a lot for help!! 

its fine now.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Pleasure, Pavel did the hard work ;)

Brits
Level 6

i forgot to ask , is there any possibility to include client machine name in the same script ?

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

Brits
Level 6

ok..thank you again .. :)

Hi Riaan,

Would it be possible to also show weekly/monthly schedules with calendar run days (based on policy name)

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?