Forum Discussion

Brits's avatar
Brits
Level 6
10 years ago

scheduled date/time of the backup policy

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...
  • RiaanBadenhorst's avatar
    10 years ago

    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"