cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Report Total Number of Jobs for the Previous Month

bmaro
Level 4

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.

1 ACCEPTED SOLUTION

Accepted Solutions

M_henriksen
Level 4
Partner

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

View solution in original post

4 REPLIES 4

M_henriksen
Level 4
Partner

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
Level 5

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.

Yogesh_Jadhav1
Level 5

Please find the reference picture.

Opscenter Backup Success Rate.png

bmaro
Level 4

Thanks everyone worked great!