creating reports to list top 7 failed jobs

[Jeff_Tank]

In the main Monitor overview page for OpsCenter there is a report showing the top 7 policiesby failed jobs, I would like to be able to create a similar report but can not find a canned report I can modify to do that. We have the full version with Analytics enabled but I am no SQL programmer. Does anyone have a report they created I can us?

Thank

Jeff

[M_henriksen]

Hi Jeff, you can try this one.

SELECT TOP 7
dj.policyname as 'Policy Name',
dj.statusCode as 'Status Code',
count(dj.statuscode) as 'Failed Jobs'

FROM domain_jobarchive dj
WHERE DATEDIFF(hour,UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 24
and dj.statuscode not in (0,1)
group by dj.statuscode,dj.policyname
order by 3 DESC

Regards Morten