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
Accepted Solution!

Uploaded.

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

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

Hi DG-2005. Could you re-send

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

Accepted Solution!

Uploaded.

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.

 

 

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.

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

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

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.

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.

Thanks, please mark the

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

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.