Forum Discussion

Dollypee's avatar
Dollypee
Moderator
10 years ago

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

 

 

 

Backup and Recovery
Best Practice
Evaluating
Features
Monitoring
OpsCenter
Windows Server (2003-2008)
  • RiaanBadenhorst's avatar
    RiaanBadenhorst
    10 years ago

    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

12 Replies

Sort By
  • DG-2005's avatar
    DG-2005
    Level 5
    10 years ago

    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's avatar
    Dollypee
    Moderator
    10 years ago

    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's avatar
    RiaanBadenhorst
    Level 6
    10 years ago

    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's avatar
    Dollypee
    Moderator
    10 years ago

    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's avatar
    RiaanBadenhorst
    Level 6
    10 years ago

    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

    • Pinku's avatar
      Pinku
      Level 5
      8 years ago

      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.

  • Dollypee's avatar
    Dollypee
    Moderator
    10 years ago

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

    Thanks again.