--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 161
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------


--------- BEGIN SURVEY QUERY ---------

declare @releaseId int
SET @releaseId =(SELECT releaseId from APP_Client  where id =2)

DECLARE @nstring nvarchar(max)

IF object_id('tempdb.dbo.#solrHealthNode') is not null DROP TABLE #solrHealthNode
create table #solrHealthNode(ClientId int, CloudName nvarchar(255), NodeName nvarchar(255), isSearchOnly int, pruneDataCount int)

IF object_id('tempdb.dbo.#solrHealthStats') is not null DROP TABLE #solrHealthStats
create table #solrHealthStats( ClientId int, Segments int, TiiFileSize int, docCount int, IndexSize int, FreeSpace int)

declare @spLevel int
SET @spLevel = ISNULL((SELECT MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) from simInstalledPackages WITH(NOLOCK) where ClientId =2),0)

DECLARE @DocMaxCriticalLimit BIGINT
SET @DocMaxCriticalLimit = ISNULL((SELECT CAST( CONVERT(VARCHAR(MAX),VALUE) AS BIGINT)  FROM GXGlobalParam  WITH(NOLOCK) WHERE name ='CINumDocumentMaxLimit')	,30000000)

IF @DocMaxCriticalLimit >= 10000000
	SET @DocMaxCriticalLimit = @DocMaxCriticalLimit - 5000000 -- to warn them ahead of time (suggested by Arun)

DECLARE @DocMaxWarningLimit BIGINT
SET @DocMaxWarningLimit = ISNULL((SELECT CAST( CONVERT(VARCHAR(MAX),VALUE) AS BIGINT) FROM GXGlobalParam  WITH(NOLOCK) WHERE name ='CINumDocumentReachesLimit')	,20000000)

--10.0 SP6 - 11.0 SP9
IF(@releaseId = 15 AND @spLevel >= 7) OR (@releaseId = 16 AND @spLevel < 9)
BEGIN

	set @nstring =N'INSERT INTO #solrHealthStats (ClientId, Segments, TiiFileSize, IndexSize, FreeSpace)
                    select clientId, [segments_count] as Segments, [main_core_tii_size_MB] AS TiiFileSize , [index_folder_size_MB] as IndexSize, [index_drive_free_size_MB] as FreeSpace
                    from (
                    SELECT A.clientId, A.attrName, A.attrVal 
                    FROM DM2SearchServerHealthInfo A WITH(NOLOCK)
                          INNER JOIN (SELECT clientId, MAX(created) AS created FROM DM2SearchServerHealthInfo WITH(NOLOCK) GROUP BY clientId) AS B 
                                ON A.clientId = B.clientId AND A.created = B.created AND A.modified =0 AND A.attrVal <> ''''
                    ) as z
                    pivot ( MAX (attrval) for attrname IN ( [segments_count], [main_core_tii_size_MB],  [index_folder_size_MB] ,[index_drive_free_size_MB])
                    ) as p


					UPDATE #solrHealthStats
					SET docCount = (S.CollectionSize + S.failedItems)
					FROM #solrHealthStats h 
					JOIN DM2SearchServerCoreInfo  S WITH (NOLOCK) ON S.clientId = h.ClientId 
					JOIN APP_ComponentProp cp WITH (NOLOCK) ON S.cloudId = cp.id 
					WHERE cp.componentType = 1 and propertyTypeid = 1
--PER SEARCH NODE 
	INSERT INTO #solrHealthNode (ClientId, CloudName, NodeName, isSearchOnly, pruneDataCount)
    SELECT S.ClientId,ISNULL((SELECT stringVal FROM APP_ComponentProp WHERE ID= S.CloudId AND propertyTypeId =1),''''), max(C.name), 
    (CASE WHEN MAX( S.enabled) = 0 THEN 1 ELSE 0 END),COUNT (A.cloudId)
    FROM DM2SearchServerCoreInfo S WITH(NOLOCK)
        INNER JOIN APP_ComponentProp CP WITH(NOLOCK) ON S.CloudId = CP.componentId  
        INNER JOIN APP_Client C WITH(NOLOCK) ON S.ClientId =C.id
        INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON C.id = Prop.componentnameId AND attrname =''CLOUD_ID'' AND CAST(attrval as INT) = S.cloudId
        INNER JOIN simInstalledPackages P WITH(NOLOCK) ON S.ClientId = P.ClientId AND P.simPackageID IN (257, 260)
		LEFT JOIN archContentIndexingAged A WITH (NOLOCK) ON A.cloudId = S.CloudId
    WHERE CP.propertyTypeId =1014 AND CP.stringVal LIKE ''%SOLR%''
	GROUP BY S.CloudId, S.ClientId'

exec sp_executesql @nstring
END	
--11.0 SP9 and above
ELSE IF(@releaseId = 16 AND @spLevel >= 9) OR (@releaseId > 16)
BEGIN

	set @nstring =N'INSERT INTO #solrHealthStats (ClientId, Segments, TiiFileSize, IndexSize, FreeSpace)
                    select clientId, [segments_count] as Segments, [main_core_tii_size_MB] AS TiiFileSize , [index_folder_size_MB] as IndexSize, [index_drive_free_size_MB] as FreeSpace
                    from (
                    SELECT A.clientId, A.attrName, A.attrVal 
                    FROM DM2SearchServerHealthInfo A WITH(NOLOCK)
                          INNER JOIN (SELECT clientId, MAX(created) AS created FROM DM2SearchServerHealthInfo WITH(NOLOCK) GROUP BY clientId) AS B 
                                ON A.clientId = B.clientId AND A.created = B.created AND A.modified =0 AND A.attrVal <> ''''
                    ) as z
                    pivot ( MAX (attrval) for attrname IN ( [segments_count], [main_core_tii_size_MB],  [index_folder_size_MB] ,[index_drive_free_size_MB])
                    ) as p


					UPDATE #solrHealthStats
					SET docCount = (S.CollectionSize + S.failedItems)
					FROM #solrHealthStats h 
					JOIN DM2SearchServerCoreInfo  S WITH (NOLOCK) ON S.clientId = h.ClientId and S.CloudType = 3

--PER SEARCH NODE 
	INSERT INTO #solrHealthNode (ClientId, CloudName, NodeName, isSearchOnly, pruneDataCount)
	SELECT S.ClientId, ISNULL(MAX(CP.stringVal),''''), max(C.name), (CASE WHEN MAX( S.enabled) = 0 THEN 1 ELSE 0 END), COUNT (A.cloudId)
	FROM DM2SearchServerCoreInfo S WITH(NOLOCK)
		INNER JOIN APP_ComponentProp CP WITH(NOLOCK) ON S.CloudId = CP.Id	
		INNER JOIN APP_Client C WITH(NOLOCK) ON S.ClientId =C.id		
		LEFT JOIN archContentIndexingAged A WITH (NOLOCK) ON A.cloudId = S.CloudId
	WHERE S.cloudType = 3
	GROUP BY S.CloudId, S.ClientId'
exec sp_executesql @nstring
END

DECLARE  @Usage XML

SET @Usage = (SELECT N.CloudName AS '@CloudName', N.NodeName AS '@NodeName', ISNULL(H.Segments,0) AS '@SegmentCount', ISNULL(H.TiiFileSize,0) AS '@TermIndexFileSizeMB', ISNULL(H.docCount,0) AS '@DocumentCount', ISNULL(H.FreeSpace,0) AS '@FreeSpaceMB',ISNULL(H.IndexSize,0) AS '@IndexSizeMB',
	@DocMaxCriticalLimit AS '@DocMaxCriticalLimit' ,@DocMaxWarningLimit AS '@DocMaxWarningLimit', N.isSearchOnly AS '@isSearchOnly',N.pruneDataCount AS '@pruneDataCount'
	FROM #solrHealthNode N 
		LEFT JOIN #solrHealthStats H ON N.ClientId =H.ClientId
FOR XML PATH('CloudNodeInfo'))

SET @surveyXML = (SELECT @Usage
FOR XML PATH('CloudInfoXML'))

DROP TABLE #solrHealthStats
DROP TABLE #solrHealthNode

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

--SELECT cast(@surveyXML as xml)
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF