DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10007
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[Hypervisor Type] varchar(7), [Policy Count] int, [Data Center Count] int, [Created VM Count] int
)
BEGIN 
 insert into @temptbl 
EXEC('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 ''Policy Count'',
       COUNT(distinct T.DataCenterId) as ''Data Center Count'',
       COUNT(ISNULL(VM.id,0)) as ''Created VM Count''
       from 
       ( select distinct p.id, VD.id as DataCenterId,
                 CASE CAST(IP.attrVal AS INT) 
                           WHEN 101 THEN ''VMWare'' 
                           WHEN 102 THEN ''Hyper V''
                           WHEN 301 THEN ''Amazon''
                           WHEN 401 THEN ''Azure''
						   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 (p.flags&1) =0
       and VD.removalTimeStamp is NULL
       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
       left outer join SRMVM VM WITH(NOLOCK)
       on VM.vmAllocationPolicyId = T.id
       group by HyperVisor
 
END
ELSE
BEGIN
select  
       HyperVisor as ''Hypervisor Type'',
       count(distinct T.id) as ''Policy Count'',
       COUNT(distinct T.DataCenterId) as ''Data Center Count'',
       COUNT(ISNULL(VM.id,0)) as ''Created VM Count''
       from 
       ( select distinct p.id, VD.id as DataCenterId,
                 CASE CAST(IP.attrVal AS INT) 
                             WHEN 101 THEN ''VMWare'' 
                             WHEN 102 THEN ''Hyper V''
                             WHEN 301 THEN ''Amazon''
                             WHEN 401 THEN ''Azure''
							 WHEN 501 THEN ''RHEV''
                             END AS HyperVisor FROM
       App_VmAllocationPolicy p WITH(NOLOCK)
       inner join APP_VMDatacenter VD WITH(NOLOCK)
       on p.vDataCenterId = VD.id and p.alive =1 and (p.flags&1) =0
       and VD.removalTimeStamp is NULL
       inner join APP_VMDatacenterMap Map WITH(NOLOCK)
       on Map.VDataCenterId = 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
       left outer join APP_VM VM WITH(NOLOCK)
       on VM.vmAllocationPolicyId = T.id
       group by HyperVisor
END
 ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
Select [Hypervisor Type] AS '@HypervisorType',[Policy Count] AS '@PolicyCount',[Data Center Count] AS '@DataCenterCount',[Created VM Count] AS '@CreatedVMCount' from @temptbl
 FOR XML PATH ('cf_MetricsQuery10007'), 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') 
)