DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10277
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Standalone Index Server] int,[Standalone Index Server Node] int,[Cluster Index Server] int,[Cluster Index Server Node] int,[HAC Cluster] int,[HAC Cluster Node] int,[Index Server Pool] int,[Index Server Pool Node] int,[Search Engine] int,[Search Engine Node] int,[Cloud Collection] int,[Cluster Cloud With Replication >1] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @CS_SPVERSION  INT = 0
SELECT Top 1 @CS_SPVERSION = ISNULL(SIM.HighestSP,0) FROM simInstalledPackages SIM
WHERE sim.ClientId = 2 AND Sim.simPackageID = 20 /*commserve package*/ 

IF(@CS_SPVERSION < 9) RETURN -- This report is for V11 SP9 and above

declare @sqlquery NVARCHAR(MAX) = 
''	IF OBJECT_ID(''''tempdb.dbo.#distributedServerInfo'''') IS NOT NULL DROP TABLE #distributedServerInfo

CREATE TABLE #distributedServerInfo	
	(	
		attrName VARCHAR(100),	
		attrVal INT		
	);

IF OBJECT_ID(''''tempdb.dbo.#cloudProps'''') IS NOT NULL DROP TABLE #cloudProps

CREATE TABLE #cloudProps (cloudid INT, cloudPropXml XML)

INSERT INTO #cloudProps
	SELECT cloudId, cloudProps FROM DM2Cloud  WITH (NOLOCK) WHERE CloudType = 5
               
	INSERT INTO #distributedServerInfo
	SELECT  CASE WHEN CloudType = 1 THEN ''''Standalone Index Server''''
									WHEN CloudType = 2 THEN ''''Content Analyzer Server''''
									WHEN CloudType = 5 THEN ''''Cluster Index Server''''
									WHEN CloudType = 6 THEN ''''HAC Cluster''''
									WHEN CloudType = 9 THEN ''''Index Server Pool''''
									END, COUNT (*)
	FROM DM2Cloud 
	WHERE CloudType IN (1,2,5,6,9)
	GROUP BY CloudType
	UNION ALL
	SELECT  ''''Search Engine'''', COUNT (*)
	FROM DM2SearchServerCoreInfo 
	WHERE CloudType =3
	GROUP BY CloudType
	UNION ALL	
	SELECT CASE WHEN CloudType = 1 THEN ''''Standalone Index Server Node''''
									WHEN CloudType = 2 THEN ''''Content Analyzer Server Node''''
									WHEN CloudType = 3 THEN ''''Search Engine Node''''
									WHEN CloudType = 5 THEN ''''Cluster Index Server Node''''
									WHEN CloudType = 6 THEN ''''HAC Cluster Node''''
									WHEN CloudType = 9 THEN ''''Index Server Pool Node''''
									END, COUNT (*)

	FROM DM2SearchServerCoreInfo
	WHERE CloudType IN (1,2,3,5,6,9)
	GROUP BY CloudType
	UNION ALL	
	SELECT ''''Cloud Collection'''', COUNT (*)
	FROM SECollectionInfo CI JOIN DM2Cloud C  ON CI.CloudID = C.cloudId AND C.CloudType = 5
	UNION ALL		
	SELECT ''''Cluster Cloud With Replication >1'''', COUNT(DISTINCT cloudId)
	FROM #cloudProps
	CROSS APPLY cloudPropXml.nodes(''''CloudMetaProp/cloudMetaInfos'''') as R(ref)
	WHERE ref.value(''''@name'''', ''''NVARCHAR(1024)'''') =''''REPLICATION'''' AND CAST( ref.value(''''@value'''', ''''NVARCHAR(1024)'''') AS INT) >1

		
	SELECT ISNULL([Standalone Index Server],0) [Standalone Index Server],
	   ISNULL([Standalone Index Server Node],0) [Standalone Index Server Node], 
	   ISNULL([Cluster Index Server],0) [Cluster Index Server], 
	   ISNULL([Cluster Index Server Node],0) [Cluster Index Server Node],
	   ISNULL([HAC Cluster],0) [HAC Cluster],
	   ISNULL([HAC Cluster Node],0) [HAC Cluster Node],
	   ISNULL([Index Server Pool],0) [Index Server Pool],
	   ISNULL([Index Server Pool Node],0) [Index Server Pool Node],
	   ISNULL([Search Engine],0) [Search Engine], 
	   ISNULL([Search Engine Node],0) [Search Engine Node], 
	   ISNULL([Cloud Collection],0) [Cloud Collection],
	   ISNULL([Cluster Cloud With Replication >1],0) [Cluster Cloud With Replication >1]
FROM 
(
  SELECT  attrVal, attrName FROM #distributedServerInfo
) d
PIVOT
(
  Max(attrVal)
  FOR attrName in (	[Standalone Index Server],[Standalone Index Server Node], [Cluster Index Server],[Cluster Index Server Node], 
					[HAC Cluster],[HAC Cluster Node],[Index Server Pool], [Index Server Pool Node],[Search Engine], [Search Engine Node], [Cloud Collection],[Cluster Cloud With Replication >1])
) piv;



IF OBJECT_ID(''''tempdb.dbo.#cloudProps'''') IS NOT NULL DROP TABLE #cloudProps
IF OBJECT_ID(''''tempdb.dbo.#distributedServerInfo'''') IS NOT NULL DROP TABLE #distributedServerInfo''

exec (@sqlquery) ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Standalone Index Server] AS '@Col_2053873260',[Standalone Index Server Node] AS '@Col_-1470458026',[Cluster Index Server] AS '@Col_2120087319',[Cluster Index Server Node] AS '@Col_-1712590453',[HAC Cluster] AS '@Col_1211781668',[HAC Cluster Node] AS '@Col_-121669986',[Index Server Pool] AS '@Col_429781099',[Index Server Pool Node] AS '@Col_-1542017865',[Search Engine] AS '@Col_866315194',[Search Engine Node] AS '@Col_63269576',[Cloud Collection] AS '@Col_-1882405463',[Cluster Cloud With Replication >1] AS '@Col_1403488720' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10277'), 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'))