Forum Discussion

bmaro's avatar
bmaro
Level 4
11 years ago

OpsCenter Report Total Number of Jobs for the Previous Month

I need to create a basic monthly report showing the total number of jobs that ran for the previous month.  It can be by master or media servers and doesn't need to take into account ignoring reruns, just need to show total jobs that ran for the previous month whether it failed or not.  I used the advanced success rate or another canned report the problem is it put commas in the job count.  For example 102,900 total jobs.  I need it to not include a comma as we import that number into another app and the comma is throwing things off.  My sql query knowledge is very green so was hoping someone can help me with a simple sql query statement to show total number of jobs for a month without a comma in the number.  Thanks.

AIX
Backup and Recovery
OpsCenter
Reporting
Tip-How to
  • M_henriksen's avatar
    M_henriksen
    11 years ago

    Hi bmaro.

    You can try this.

    SELECT count(distinct dj.id) AS "Total Number of Jobs",
    CASE lookup_PolicyType.name WHEN 'Standard' THEN 'Unix' ELSE lookup_PolicyType.name end as "Type"

    FROM domain_job dj
    JOIN lookup_PolicyType
    ON lookup_policyType.id = dj.policyType WHERE DATEDIFF(day,UTCBigIntToNomTime(dj.endTime), GETDATE()) <= 30
    GROUP BY Type
    ORDER BY "Total Number of Jobs"

     

    Br Morten

4 Replies

Sort By
  • M_henriksen's avatar
    M_henriksen
    Level 4
    11 years ago

    Hi bmaro.

    You can try this.

    SELECT count(distinct dj.id) AS "Total Number of Jobs",
    CASE lookup_PolicyType.name WHEN 'Standard' THEN 'Unix' ELSE lookup_PolicyType.name end as "Type"

    FROM domain_job dj
    JOIN lookup_PolicyType
    ON lookup_policyType.id = dj.policyType WHERE DATEDIFF(day,UTCBigIntToNomTime(dj.endTime), GETDATE()) <= 30
    GROUP BY Type
    ORDER BY "Total Number of Jobs"

     

    Br Morten

  • Yogesh_Jadhav1's avatar
    Yogesh_Jadhav1
    Level 5
    11 years ago

    a. Edit Advanced Success Rate template
     
    b. Select the Time Frame (Which can be edited while running this report)
     
    c. Select Job types Backup and Catalog Backup (If you just want Backup else keep it intact)
     
    d. Click Next for the next step where you will select the report to be generated on the Master Server. You can change the Display Unit.
     
    f. Now Run the Report and you get the desired Success Rate for the Master Servers and combine Success rate which can be used as our Success Rate. This will inculde the No of Jobs.

    Hope this helps.