Forum Discussion

m_karampasis's avatar
m_karampasis
Level 4
12 years ago

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
  • MilesVScott's avatar
    MilesVScott
    11 years ago

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

14 Replies

Sort By
  • m_karampasis's avatar
    m_karampasis
    Level 4
    11 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.

  • MilesVScott's avatar
    MilesVScott
    Level 6
    11 years ago

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

  • 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
  • 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

    I was actually going to ask you about that... Are you asking for statistics on when an opperator manually restarts a hung backup with checkpoint's enabled?

     

    Otherwise Im not sure of any other retries, out side of regular scheduling. Unless you had your windows pretty open and short frequencies. I guess that would let the job kick off again similar to a retry.

     

    Unfortunately, I don't have a good way to break either of those scnarios out right now. I'll dig a bit deeper into the data and see what I can find.

  • m_karampasis's avatar
    m_karampasis
    Level 4
    12 years ago

    Thank you very much Miles that's great job!!!!

    We are still trying to understand how we will add the "Retries" or "Attempts" to the report if you have any ideas please do not hesitate!!!!

  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    Here is what I have so far....

     

    Still missing the retires, Final Success Ratio, and the pivot to make a single row for each, but this would at least get you some temporary data untill I can finish it up....

     

    SELECT dj.clientName
            , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName AND j.statusCode = 0) AS 'Successful Jobs'
            , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName AND j.statusCode <> 0) AS 'Failed Jobs'
            , (SELECT COUNT(*) FROM domain_Job j WHERE DATEDIFF(day, UtcBigIntToNomTime(j.endTime), GETDATE()) <= 30 AND dj.clientName = j.clientName) AS 'Total Jobs'
            , '' AS 'Retries'
            , '' 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 DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 30
    GROUP BY dj.clientName, rl.label
    ORDER BY dj.clientName, rl.label
  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    12 years ago

    Miles,

    Maybe this can help.......

    Using the nb_retentionlevel for lookup / name, you should be able to join the following:

    I found the retention level under...

    domain_imagecopy.retentionLevel

    joined with 

    domain_imagecopy.imageID = domain_image.id

    then

    domain_image.jobId = domain_job.id

     

    That will be one heck of a join statement... but it should work... 

    --Tom

     

     

     

  • MilesVScott's avatar
    MilesVScott
    Level 6
    12 years ago

    Tom, That looks like a lookup table for the defined retention levels.

     

    My issue with building the query is that I am unable to find how to associate those deffinitions back to the policies.

     

    ie. The policies are lacking the Retention Level ID field... Or at least it's not populated in my system.

     

    It looks like the logic was built into the domain_Schedule table, but again it looks like there was a typo in the column name which might be causing a proc to fail to fill the table with that data. I really can't say as I dont a good ERD for the database. But just my observations....