I have created a custom query to report on the daily job status codes.
And I would like to have OpsCenter combine both Queries into (1) report (i.e. Email)
Is that even possible with OpsCenter SQL? I tried a UNION but no luck ...
The first report, shows all the jobs with "Partial success" status codes 1 to 6 ...
// --------- Query #1 --------- //
SELECT
clientName as 'Client',
policyName,
mediaServerName as 'Media Server',
statusCode as 'Exit Status',
UtcBigIntToNomTime(startTime) as 'Start Time',
UtcBigIntToNomTime(endTime) as 'End Time',
((endTime - startTime)/600000000) AS 'Run Time (minutes)',
parentJobId AS 'Job ID'
FROM domain_JobArchive
WHERE
DATEDIFF(hour, UtcBigIntToNomTime(startTime), GETDATE()) <= 24
and
statusCode > 0
and
statusCode <= 6
and
masterServerId = 61
ORDER BY 1,5
// ----------------------- //
While, the second query lists the count for each status code ...
// ------------- Query #2 ---------------- //
SELECT statusCode AS STATUS, COUNT(*) AS COUNT
FROM domain_JobArchive
WHERE
DATEDIFF(hour, UtcBigIntToNomTime(endTime), GETDATE()) <= 24
and
masterServerId = 61
GROUP BY statusCode
// ------------------------------------------- //
