DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10265
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Hypervisor Type] nvarchar(max),[Livemount Policy Count] int
)


IF @releaseId = 15 OR @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @CSVersion INT = (SELECT releaseId FROM App_Client WHERE id = 2)          
IF @CSVersion <= 15  
BEGIN         
select  
       HyperVisor as ''Hypervisor Type'',
	   count(distinct T.id) as ''Livemount Policy Count''
       from 
       ( select  distinct p.id,				
                 CASE CAST(IP.attrVal AS INT) 
                           WHEN 101 THEN ''VMWare'' 
                           WHEN 102 THEN ''Hyper V''
			   WHEN 501 THEN ''RHEV''
                           END AS HyperVisor FROM
       App_VmAllocationPolicy p WITH(NOLOCK)
       inner join SRMVDataCenter VD WITH(NOLOCK)
       on p.vDataCenterId = VD.id and p.alive =1 
       and VD.removalTimeStamp is NULL 
	   and (p.vxml.value(''policy[1]/entity[1]/@policyType'',''int'') in (4,13))
       inner join SRMVDataCenterMap Map WITH(NOLOCK)
       on Map.srmVDataCenterId = VD.id
       inner join APP_InstanceProp IP WITH(NOLOCK)
       on IP.componentNameId = Map.instanceId
       and IP.attrName =''Virtual Server Instance Type''
       and IP.modified =0
       ) T
       group by HyperVisor
 
END
ELSE
BEGIN
select  
       HyperVisor as ''Hypervisor Type'',
       count(distinct T.id) as ''Livemount Policy Count''
       from 
       ( select	 distinct p.id, 
                 CASE CAST(IP.attrVal AS INT) 
                             WHEN 101 THEN ''VMWare'' 
                             WHEN 102 THEN ''Hyper V''
			     WHEN 501 THEN ''RHEV''
                             END AS HyperVisor FROM
       App_VmAllocationPolicy p WITH(NOLOCK)
	   inner join APP_Application A WITH(NOLOCK)
	   on A.clientId = isnull(p.vxml.value(''policy[1]/dataCenter[1]/instanceEntity[1]/@clientId'',''int''), p.vxml.value(''policy[1]/destinationHyperV[1]/@clientId'',''int''))
	   AND A.appTypeID = 106
	   and p.alive =1 
	   and (p.vxml.value(''policy[1]/entity[1]/@policyType'',''int'') in (4,13)
       OR p.vxml.value(''policy[1]/@isLiveMountEnabled'',''int'') = 1)
       inner join APP_InstanceProp IP WITH(NOLOCK)
       on IP.componentNameId = A.instance
       and IP.attrName =''Virtual Server Instance Type''
       and IP.modified =0
       ) T
       group by HyperVisor
END ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Hypervisor Type] AS '@Col_-2145071453',[Livemount Policy Count] AS '@Col_-923238572' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10265'), 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')) 
