cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Daily backup report using symantec netbackup opscenter analytic

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 Solutions

Accepted Solutions
Highlighted
Accepted Solution!

Uploaded.

Highlighted
Accepted Solution!

For 2,3,4 below I've selected

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
Highlighted

well, here's #3 SELECT

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()))

Highlighted

Hi DG-2005. Could you re-send

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

Highlighted
Accepted Solution!

Uploaded.

Highlighted

Thank you so much for your

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.

 

 

Highlighted

Hi You can download the

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.

Highlighted

Thanks Riaan. Am pretty much

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

Highlighted
Accepted Solution!

For 2,3,4 below I've selected

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

Highlighted

Thank you so much, I will

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

Thanks again.

Highlighted

HI, I have tested out these

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.

Highlighted

Thanks, please mark the

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

Highlighted

Yes, I have requested for

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

Highlighted

Re: For 2,3,4 below I've selected

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.