DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10363
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[DBEngineType] nvarchar(max),[SnapShotSharing] int,[SnapShotFull] int,[SnapShotReplica] int,[InstanceCount] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @rdsInstanceDetails TABLE(DBEngineType NVARCHAR(256), SnapShotSharing INT default 0, SnapShotFull INT default 0, SnapShotReplica INT default 0, InstanceCount INT default 0)

INSERT INTO @rdsInstanceDetails(DBEngineType, InstanceCount)
SELECT dbType, COUNT(DISTINCT A.instance) FROM APP_RDSSubclientContentMap R WITH (NOLOCK)
INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = R.subclientId AND R.modified = 0
INNER JOIN App_InstanceProp IP WITH (NOLOCK) ON A.instance = IP.componentNameId 
AND IP.attrName = ''Cloud Apps Instance Type'' AND IP.modified = 0 AND IP.attrVal = 4
WHERE A.appTypeId = 134 GROUP BY dbType

UPDATE rds
SET rds.SnapShotSharing = T.insCount
FROM @rdsInstanceDetails rds
INNER JOIN (
	SELECT R.dbType, COUNT( DISTINCT A.instance) AS insCount 
	FROM APP_Application A WITH (NOLOCK)
	INNER JOIN APP_SubClientProp S WITH (NOLOCK) ON S.componentNameId=A.id
	INNER JOIN APP_RDSSubclientContentMap R WITH (NOLOCK) ON R.subclientId=A.id ANd R.modified = 0
	INNER JOIN App_InstanceProp IP WITH (NOLOCK) ON A.instance = IP.componentNameId 
	AND IP.attrName = ''Cloud Apps Instance Type'' AND IP.modified = 0 AND IP.attrVal = 4
	WHERE S.attrName=''Enable cross account copy'' AND S.attrVal=1 AND S.modified = 0 AND A.appTypeId = 134
	GROUP BY R.dbType
) T ON T.dbType = rds.DBEngineType

UPDATE rds
SET rds.SnapShotFull = T.insCount
FROM @rdsInstanceDetails rds
INNER JOIN (
	SELECT R.dbType, COUNT( DISTINCT A.instance) AS insCount 
	FROM APP_Application A WITH (NOLOCK)
	INNER JOIN APP_SubClientProp S WITH (NOLOCK) ON S.componentNameId=A.id
	INNER JOIN APP_RDSSubclientContentMap R WITH (NOLOCK) ON R.subclientId=A.id ANd R.modified = 0
	INNER JOIN App_InstanceProp IP WITH (NOLOCK) ON A.instance = IP.componentNameId 
	AND IP.attrName = ''Cloud Apps Instance Type'' AND IP.modified = 0 AND IP.attrVal = 4
	WHERE S.attrName=''Enable cross account full copy'' AND S.attrVal=1 AND S.modified = 0 AND A.appTypeId = 134
	GROUP BY R.dbType
) T ON T.dbType = rds.DBEngineType

UPDATE rds
SET rds.SnapShotReplica = T.insCount
FROM @rdsInstanceDetails rds
INNER JOIN (
	SELECT R.dbType, COUNT(DISTINCT A.instance) AS insCount
	FROM APP_Application A WITH (NOLOCK)
	INNER JOIN App_InstanceProp IP WITH (NOLOCK) ON A.instance = IP.componentNameId 
	AND IP.attrName = ''Cloud Apps Instance Type'' AND IP.modified = 0 AND IP.attrVal = 4
	INNER JOIN APP_RDSSubclientContentMap R WITH (NOLOCK) ON R.subclientId = A.id AND R.modified = 0
	INNER JOIN archGroupCopy Ar WITH (NOLOCK) ON Ar.archGroupId=A.dataArchGrpID
	WHERE Ar.type=2 AND A.appTypeId = 134
	GROUP BY R.dbType
) T ON T.dbType = rds.DBEngineType

SELECT * FROM @rdsInstanceDetails ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [DBEngineType] AS '@DBEngineType',[SnapShotSharing] AS '@SnapShotSharing',[SnapShotFull] AS '@SnapShotFull',[SnapShotReplica] AS '@SnapShotReplica',[InstanceCount] AS '@InstanceCount' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10363'), 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'))