Forum Discussion

m_karampasis's avatar
m_karampasis
Level 4
12 years ago
Solved

OPS ANALYTICS CUSTOM REPORT (SQL QUERY)

We need help in order to create a custom report at OPS , attached you will find a template of the report as our Management team asked to be created.

We have bought Analytics License and all our efforts for the creation of the custom reports have failed.

Is there a way to add the retention period at a report ?

 

 

Backup and Recovery
Linux
OpsCenter
Reporting
Script
  • m_karampasis's avatar
    m_karampasis
    Level 4
    12 years ago

    Hello Miles we need the " Job Attempt Count " there are some default reports at the ops for that, although we cannot export the report per client or policy (as you can see attached).

    What we need is that if there is a backup that wasn't successful at the 1st attempt then a way to know that at the one of the next attempts we had successfull backed up the data.

  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    This one is a bit better in that It breakes the success/failures down for each of the retention levels:

     

    SELECT dj.clientName
            , (SELECT COUNT(*) 
                FROM domain_Job domj
                INNER JOIN domain_image domi
                ON domj.id = domi.jobId
                INNER JOIN domain_ImageCopy domic
                ON domi.id = domic.imageId
                INNER JOIN nb_RetentionLevel nbrl
                ON domic.retentionLevel = nbrl.iD
                WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND domj.statusCode = 0 AND nbrl.id = rl.id) AS 'Successful Jobs'
            , (SELECT COUNT(*) 
                FROM domain_Job domj
                INNER JOIN domain_image domi
                ON domj.id = domi.jobId
                INNER JOIN domain_ImageCopy domic
                ON domi.id = domic.imageId
                INNER JOIN nb_RetentionLevel nbrl
                ON domic.retentionLevel = nbrl.iD 
                WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND domj.statusCode <> 0 AND nbrl.id = rl.id) AS 'Failed Jobs'
            , (SELECT COUNT(*) 
                FROM domain_Job domj
                INNER JOIN domain_image domi
                ON domj.id = domi.jobId
                INNER JOIN domain_ImageCopy domic
                ON domi.id = domic.imageId
                INNER JOIN nb_RetentionLevel nbrl
                ON domic.retentionLevel = nbrl.iD 
                WHERE UtcBigIntToNomTime(domj.endTime) > GETDATE() - 30 AND dj.clientName = domj.clientName AND nbrl.id = rl.id) AS 'Total Jobs'
            , REPLACE(LEFT(100*ROUND(((CONVERT(NUMERIC(10,2),[Successful Jobs])/CONVERT(NUMERIC(10,2),[Total Jobs]))), 2), 3), '.', '') AS 'Final Success Rate'
            , rl.label AS 'RetentionLevel' 
        FROM domain_Job dj
    INNER JOIN domain_image di
    ON dj.id = di.jobId
    INNER JOIN domain_ImageCopy ic
    ON di.id = ic.imageId
    INNER JOIN nb_RetentionLevel rl
    ON ic.retentionLevel = rl.iD
    WHERE UtcBigIntToNomTime(dj.endTime) > GETDATE() - 30
    GROUP BY dj.clientName, rl.label, rl.id
    ORDER BY dj.clientName, rl.label
     
     
     
     
    DISCLAIMER: When Testing this for the last day's time frame, I found a few clients that showed more jobs on here than in the activity monitor. I have no idea why that is.... and I am still researching it.
     
    EDIT: Added success rate
  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    Was that query helpful? Or are you still looking for other items?

  • m_karampasis's avatar
    m_karampasis
    Level 4
    12 years ago

    Thank you very much MIles the only think that we cannot export from the report is the "Job Attempt Count" . I will mark your response as the solution.