cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Custom Queries - is it possible to combine output from 2 queries?

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

// ------------------------------------------- //