DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10351
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[Replication Groups(Old UI)] int,[Replication Groups(New UI)] int,[Replicated VMs(Old UI)] int,[Replicated VMs (New UI)] int,[VMs under DR License] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @totalvms int

DECLARE @totalReplicatedVMs INT =0 
DECLARE @totalReplicationGroup INT =0
DECLARE @totalReplicatedVMsUnderDRLicense INT =0
DECLARE @totalReplicationGroupNewUI INT  = 0


select @totalReplicationGroup= count(T.taskId)
from TM_SubTask S
inner join TM_Task T on S.taskId = T.taskId
inner join tm_assocentity tae on tae.taskId = T.taskId and tae.apptypeId=106
where operationType = 1007 and T.deleted = 0 and T.invalid = 0 and T.hidden = 0 and T.uninstalled = 0


CREATE TABLE #param (retVal INT)
IF OBJECT_ID(''App_ReplicationGroupAssociation'', ''U'') IS NOT NULL 
BEGIN


    EXEC sp_executesql N''INSERT INTO #param select Count(R.replicationId) from 
App_ReplicationGroupAssociation RGA
INNER JOIN  APP_VSAReplication R
ON R.taskId= RGA.taskId
AND R.lastRestoreTime > 0''

end

select  @totalReplicatedVMs = retVal from #param
DELETE FROM #param

IF OBJECT_ID(''App_ReplicationGroup'', ''U'') IS NOT NULL 
BEGIN


    EXEC sp_executesql N''INSERT INTO #param select  count(RG.id) from App_ReplicationGroup RG
	INNER JOIN  App_ReplicationGroupAssociation RGA
	ON RGA.componentNameId= RG.id 
	INNER JOIN TM_Task T 
	ON T.taskId = RGA.taskId
	AND T.deleted = 0 and T.invalid = 0 and T.hidden = 0 and T.uninstalled = 0
	inner join tm_assocentity tae on tae.taskId = T.taskId and tae.apptypeId=106''
end

	

select  @totalReplicationGroupNewUI = retVal from #param

DROP TABLE #param

select @totalVms = count(replicationId) from app_vsareplication where lastRestoreTime > 0

SELECT @totalReplicatedVMsUnderDRLicense= Count(R.replicationId) FROM APP_VSAReplication R WITH(NOLOCK) 
    INNER JOIN TM_Task T WITH (NOLOCK) ON  T.taskId = R.taskId AND ISNULL(T.disabled,0) <> 1  AND ISNULL(R.status,0) <> 6 AND R.lastRestoreTime > 0
       INNER JOIN APP_ClientProp CP WITH(NOLOCK) ON CP.attrname =''Virtual Machine Instance UUID''  AND CP.attrVal = R.sourceGuid  AND CP.modified = 0
                    INNER JOIN JMBkpStats JMB WITH(NOLOCK) ON JMB.jobId = R.lastSyncedBkpJob
                    INNER JOIN archGroupCopy AG WITH(NOLOCK) ON AG.archGroupId = JMB.dataArchGrpId
                    INNER JOIN archAgingRule AGR WITH(NOLOCK) ON AG.id = AGR.copyId
              GROUP BY CP.componentnameid
			 HAVING max(AGR.retentionDays)<=14


select isnull(@totalReplicationGroup-@totalReplicationGroupNewUI,0) ''Replication Groups(Old UI)'', isnull(@totalReplicationGroupNewUI,0) ''Replication Groups(New UI)'', @totalvms-@totalReplicatedVMs ''Replicated VMs(Old UI)'', isnull(@totalReplicatedVMs,0) ''Replicated VMs (New UI)'', isnull(@totalReplicatedVMsUnderDRLicense,0) ''VMs under DR License'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Replication Groups(Old UI)] AS '@Col_331615804',[Replication Groups(New UI)] AS '@Col_-738745035',[Replicated VMs(Old UI)] AS '@Col_-826679861',[Replicated VMs (New UI)] AS '@Col_1719438038',[VMs under DR License] AS '@Col_-334073893' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10351'), 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'))