DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10368
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Agent] nvarchar(max),[DBArchiverInstanceCount] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT ''SQL Server'' Agent, COUNT(DISTINCT AP.instance) DBArchiverInstanceCount
FROM APP_Application AP WITH (NOLOCK) JOIN APP_SubClientProp SP WITH (NOLOCK)
ON AP.appTypeId = 81 AND AP.id = SP.componentNameId AND SP.modified = 0 AND SP.attrName = N''mssql subclient type'' AND SP.cs_attrName = CHECKSUM(N''mssql subclient type'') AND SP.attrVal = N''3'' 
AND AP.subclientStatus & (2|4) = 0 
UNION
SELECT ''Oracle'' Agent, COUNT(DISTINCT AP.instance) DBArchiverInstanceCount
FROM APP_Application AP WITH (NOLOCK) JOIN APP_SubClientProp SP WITH (NOLOCK)
ON AP.appTypeId IN (22, 80) AND AP.id = SP.componentNameId AND SP.modified = 0 AND SP.attrName = N''Oracle Subclient Type'' 
	AND SP.cs_attrName = CHECKSUM(N''Oracle Subclient Type'') 
	AND SP.attrVal = N''1'' -- get only archiver subclient for SQL.
	AND AP.subclientStatus & (2|4) = 0 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Agent] AS '@Agent',[DBArchiverInstanceCount] AS '@DBArchiverInstanceCount' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10368'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))