cancel
Showing results for 
Search instead for 
Did you mean: 

Opscenter/netbackup - SQL query to have a weekly report: Policy with start Time and End Time

jayjay00
Level 3

Hello,

The goal is to have a gantt diagramm (made with excel) for the last week that show for each policy the start time and the end time.

I found some report on opscenter to have this kind of information but only by client. I would like to have a SQL query that give me these informations on a policy basis.

Exemple:

Policy name Start Time End Time (or duration)
Policy 1 27.07.2015 22:00 27.07.2015 23:18
Policy 2 27.07.2015 22:30 27.07.2015 23:01
Policy 1 28.07.2015 22:00 28.07.2015 23:19
....    

Do you have any idea on the best/easiest way to do that?

Regards

Jay

1 ACCEPTED SOLUTION

Accepted Solutions

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Try this

 

SELECT
domain_JobArchive.policyname as "Policy",
domain_JobArchive.statuscode as "Status",
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM
domain_jobarchive
WHERE
domain_JobArchive.type in (0) AND (domain_JobArchive.parentjobid = domain_JobArchive.id)
ORDER BY
'Start Time'

View solution in original post

12 REPLIES 12

GulzarShaikhAUS
Level 6
Partner Accredited Certified

I would normally grab that query and replace the report on from clients to policy. This can be done through SQL query or using custom reports.

Give it a try.

cruisen
Level 6
Partner Accredited

Hello jayjay00,

you can go for the all backup tabular report and configure it for the setting you need:

Check this screenshot:

Screenshot.png

best regards,

Cruisen

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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"

 

Credit https://www-secure.symantec.com/connect/user/voropaevpavel

GulzarShaikhAUS
Level 6
Partner Accredited Certified

So Pavel has already put it there :) Thanks Riaan for pointing out.

jayjay00
Level 3

Thank you for your reply, I allready try this kind of request and the problem is it doesnt really give me what I want.

This request give me the theoretical start time, I need the effective one and I this the same for the duration. 

Any Idea?

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

Ok so you want the job start and end times. I thought you were referring to the policy configuration.

 

Let me work on that for you.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Try this

 

SELECT
domain_JobArchive.policyname as "Policy",
domain_JobArchive.statuscode as "Status",
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM
domain_jobarchive
WHERE
domain_JobArchive.type in (0) AND (domain_JobArchive.parentjobid = domain_JobArchive.id)
ORDER BY
'Start Time'

GulzarShaikhAUS
Level 6
Partner Accredited Certified

I tested and works perfectly

cruisen
Level 6
Partner Accredited

Hello,

I understand better your request now. I dont think you will get this, or I do not know how to get this by opscenter. But you can try this I tested this for you in my lab. You can adapt this like you need.

To get the start time === begin writing 

endtime parameters ==== exiting with status

for that you need all_colums

/usr/openv/netbackup/bin/admincmd/bpdbjobs -all_columns -jobid 98365 | awk -F, '{print $1" "$5" "$6" "$111" " $115}'
 

'{print $1" "$5" "$6" "$111" " $115}' = [jobid, policy,schedule,begin writing, exiting with status]

[98365] [EXCHANGE_2010]  [INCR] [07/28/15 19:46:57 - begin writing] [07/28/15 19:51:24 - Info bptm(pid=3052) EXITING with status 0 <----------]

best regards,

Cruisen

 

 

 

jayjay00
Level 3

Hello,

 

Thanks for your help, it is almost what I need but I should have enough to tune the query.

 

Regards

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi,

 

Ok please mark the solution or let me know how else I can help.

Hi Riaan .

I tried to make a change but I couldn't ..

I would need to enter a check on the last week only (Last 7 Day)

Can you help me ?