cancel
Showing results for 
Search instead for 
Did you mean: 

Daily backup report using symantec netbackup opscenter analytic

Dollypee
Moderator
Moderator
   VIP    Certified

Here's my question:

I would like to be able to generate following reports from opscenter in a tabular form from my opscenter analytic on a daily basis for my management.

1. Total jobs executed in 24hrs

2. Successful jobs in last 24hrs (Status 0)

3. Partially successful jobs in last 24hrs (Status 1)

4. Failed jobs in last 24hrs

5. Data backed up in last 24hrs

6. Total number of tapes used.

Will there be away to have these reports all under one subject/heading?

Will greatly appreciate any help with sql query or some other way to get this done in my opscenter analytic. Thank you

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

DG-2005
Level 5

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

For 2,3,4 below I've selected * (which means all columns). You can modify that to only select the columns you want by replacing the * with column names. Example

--------

Select

domain_jobarchive.PolicyName,

domain_jobarchive.PolicyType,

domain_jobarchive.ScheduleName,

domain_jobarchive.StatusCode,

domain_jobarchive.Throughput,

domain_jobarchive.BytesWrtiten

FROM domain_jobarchive

--------

1.

 

SELECT COUNT(*) as "Number of Backup Jobs"
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

2.

 

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

3.

 

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode in (1)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

4.

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode NOT in (0,1)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

5.

SELECT COALESCE(CAST(SUM(domain_JobArchive.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Backed Up (GB)'
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

View solution in original post

12 REPLIES 12

DG-2005
Level 5

well, here's #3

SELECT domain_MasterServer.friendlyNa4e as "MasterServer", domain_JobArchive.policyName as "Policy", domain_JobArchive.clientName as "Client"

FROM domain_JobArchive, domain_MasterServer

WHERE domain_JobArchive.masterServerId = domain_MasterServer.id

    AND domain_JobArchive.isValid = 1 AND domain_JobArchive.type = 0 AND domain_JobArchive.statusCode not in (0,1)

    AND domain_JobArchive.endTime > NOMTIMETOUTCBIGINT(DATEADD(hour, -24, getdate()))

Dollypee
Moderator
Moderator
   VIP    Certified

Hi DG-2005. Could you re-send this in an attachment? thanks.

DG-2005
Level 5

Uploaded.

Dollypee
Moderator
Moderator
   VIP    Certified

Thank you so much for your response. That attached actually took care of  "Failed jobs in last 24hrs". See attached Would you be able to help with 1,2,3,5 & 6 as well. really appreciate your input on this matter of mine. If there's any available manual, i would be interested too. Thank you so much.

 

 

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Hi

 

You can download the opscenter schema pdf from your opscenter server. From there you'll be able to create your own queries.

Open opscenter > Reports

Click on Create new report

Select SQL Query and next

Then you'll see a link "Refer to the OpsCenter Database Schema Document"

Click on it to download the pdf.

Dollypee
Moderator
Moderator
   VIP    Certified

Thanks Riaan. Am pretty much a rookie for now when it comes to applying sql query to get above report. Any help will be much appreciated. Thank you

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

For 2,3,4 below I've selected * (which means all columns). You can modify that to only select the columns you want by replacing the * with column names. Example

--------

Select

domain_jobarchive.PolicyName,

domain_jobarchive.PolicyType,

domain_jobarchive.ScheduleName,

domain_jobarchive.StatusCode,

domain_jobarchive.Throughput,

domain_jobarchive.BytesWrtiten

FROM domain_jobarchive

--------

1.

 

SELECT COUNT(*) as "Number of Backup Jobs"
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

2.

 

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

3.

 

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode in (1)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

4.

SELECT *
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND domain_JobArchive.StatusCode NOT in (0,1)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

5.

SELECT COALESCE(CAST(SUM(domain_JobArchive.preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Backed Up (GB)'
FROM domain_JobArchive
WHERE domain_JobArchive.Type in (0)
AND DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

Dollypee
Moderator
Moderator
   VIP    Certified

Thank you so much, I will test out the parameter shortly and update you with the status.

Thanks again.

Dollypee
Moderator
Moderator
   VIP    Certified

HI, I have tested out these queries and they work just fine. I was able to add some few more options and also work just great. Thank you so much to you and DG-2005.

 

I however do have a newrequest to list clients deleted say last 24 hours. Here's the link to the new request

https://www-secure.symantec.com/connect/forums/need-help-sql-queries-opscenter-generate-deleted-clie...

Will appreciate if you can also help look into this. Thank you again.

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

Thanks, please mark the solutions, Dg-2005 and mine .

Dollypee
Moderator
Moderator
   VIP    Certified

Yes, I have requested for split solution between you and DG-2005. Thank you

Hi Riaan,

 

This is exactly I want but in one report, like my report should give Number of Jobs, sucessfull jobs, failed jobs and partial jobs and size in one table. Can you please help how can I join all these 5 different sql queries and extract one single report.