It's an OpsCenter thing, you can do through gui as well. Here's a simple query that I use.
Clients failed more than 2 times within 72 hours.
SELECT clientName, COUNT(*)
FROM domain_Job
WHERE statusCode <> 0
AND id = parentJobId
AND DATEDIFF(hour, UtcBigIntToNomTime(endTime), GETDATE()) <= 72
GROUP BY clientName
HAVING COUNT(*) > 2
ORDER BY COUNT(clientName) DESC
And a more complete view of an environment.
Per Client Success Rate Last 31 Days
select TOP 500 START AT 1 sum ((case when statusCode =0 then 1 else 0 end)) as "successfulJob",sum ((case when statuscode =1 then 1 else 0 end)) as "partialSuccessfulJob",sum ((case when statusCode >1 then 1 else 0 end)) as "failedJob",((successfulJob*1.0/count(domain_JobArchive.id)) *100) as "successRate",domain_JobArchive.clientId as "domain_JobArchive.clientId",domain_JobArchive.masterServerId as "domain_JobArchive.masterServerId",domain_Entity.name as "domain_Entity.name",#entity1.name as "#entity1.name" from domain_MasterServer , domain_Client , domain_JobArchive , domain_Entity , ( select * from domain_entity)#entity1 where domain_MasterServer.id = domain_JobArchive.masterServerId and domain_Client.masterServerId = domain_JobArchive.masterServerId and domain_Client.name = domain_JobArchive.clientName and ( (domain_MasterServer.id = domain_Entity.id) ) AND ( (domain_Client.id = #entity1.id) ) AND ( ( (domain_JobArchive.isValid = '1') AND (domain_Client.isValid = '1') AND (DATEDIFF(day, UtcBigIntToNomTime((domain_JobArchive.startTime)), GETDATE()) <= 31 ) AND (domain_JobArchive.state = 3) ) AND ( ( (domain_JobArchive.masterServerId IN (61 )) ) ) ) GROUP BY "domain_JobArchive.clientId", "domain_JobArchive.masterServerId", "domain_Entity.name", "#entity1.name" ORDER BY UPPER("domain_Entity"."name" ) DESC , UPPER("#entity1.name") DESC , "successRate" DESC
