DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10260
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Number of Hyperscale MountPaths] int,[Number of Hyperscale Libraries] int,[Number of Hyperscale SPs] int,[Number of Hyperscale Copies] int,[Number of Hyperscale SPs with more than one Copy] int,[Number of Hyperscale copies where Full/Incremental DV2 was run in the last 14 days.] int,[Highest basic retention days of the Hyperscale Copies] nvarchar(max),[Highest extended retention days of the Hyperscale Copies] nvarchar(max)
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @MountPathsTable TABLE(MountPathId INT, LibraryId INT, archGroupId INT, CopyId INT, SIDBStoreId INT PRIMARY KEY(MountPathId, LibraryId, archGroupId, CopyId, SIDBStoreId))

	--Pick Gluster FS MountPath and associated copies
	INSERT INTO @MountPathsTable
	SELECT	DISTINCT MP.MountPathId, MP.LibraryId, AGC.archGroupId, DPath.CopyId, AGC.SIDBStoreId
	FROM	MMMountPath MP WITH (READUNCOMMITTED)
			INNER JOIN MMDrivePool DP WITH (READUNCOMMITTED) ON MP.MasterPoolId = DP.MasterPoolId
			INNER JOIN MMDataPath DPath WITH (READUNCOMMITTED) ON DP.DrivePoolId = DPath.DrivePoolId
			INNER JOIN archGroupCopy AGC WITH (READUNCOMMITTED) ON DPath.copyId = AGC.id
	WHERE	MP.Attribute & 64 /*MNTPTH_ATTRIB_GFS_VOL_CONFIGURED*/ > 0
			AND (AGC.dedupeFlags & 268435456 /*CVA_HOST_GLOBAL_DEDUP_STORE_FLAG*/) = 0

	SELECT	COUNT(DISTINCT MountPathId) ''Number of Hyperscale MountPaths'',
			COUNT(DISTINCT LibraryId) ''Number of Hyperscale Libraries'',
			COUNT(DISTINCT archGroupId) ''Number of Hyperscale SPs'',
			COUNT(DISTINCT copyId) ''Number of Hyperscale Copies'',
			(SELECT COUNT(*)
			FROM	
					(SELECT	DISTINCT AGC.archGroupId
					FROM	archGroupCopy AGC WITH (READUNCOMMITTED), @MountPathsTable M
					WHERE	AGC.archGroupId = M.archGroupId
							AND AGC.type != 5 /*CVA_AGCOPYTYPE_TRANSITIVE*/
							AND AGC.flags & (524288 /*$$(CVA_SILO_COPY_FLAG)*/ | 1073741824 /*$$(CVA_DELETE_COPY_AFTER_JOBS_ARE_DELETED)*/) = 0
							AND AGC.isSnapCopy = 0
					GROUP BY AGC.archGroupId
					HAVING COUNT(AGC.id) > 1) T) ''Number of Hyperscale SPs with more than one Copy'',
			(SELECT COUNT(*)
			FROM	
					(SELECT	DISTINCT M.copyId
					FROM	JMAdminJobStatsTable J WITH (READUNCOMMITTED), @MountPathsTable M
					WHERE	J.OpType = 31
							AND J.cloudId = M.SIDBStoreId
							--AND J.archGrpId = M.archGroupId
							AND J.Status IN (1, 3)
							AND J.servStart >= dbo.GetUnixTime(DATEADD(d, -14, GETUTCDate()))) T) ''Number of Hyperscale copies where Full/Incremental DV2 was run in the last 14 days.'',
			(SELECT  (CASE WHEN ((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) = 2147483647) THEN ''INFINITE''
						  WHEN ((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) = 0) THEN ''NA''
						  ELSE CAST((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) AS VARCHAR(10))
					END)
			FROM @MountPathsTable M, archGroupCopy AGC WITH (READUNCOMMITTED), archAgingRule AAR WITH (READUNCOMMITTED)
			WHERE AGC.id = M.copyId
			AND     AGC.type != 5 /*CVA_AGCOPYTYPE_TRANSITIVE*/
			AND     AGC.flags & (524288 /*$$(CVA_SILO_COPY_FLAG)*/ | 1073741824 /*$$(CVA_DELETE_COPY_AFTER_JOBS_ARE_DELETED)*/) = 0
			AND     AGC.isSnapCopy = 0
			AND     AGC.id = AAR.copyId) ''Highest basic retention days of the Hyperscale Copies'',
			(SELECT  (CASE WHEN ((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) = 2147483647) THEN ''INFINITE''
						  WHEN ((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) = 0) THEN ''NA''
						  ELSE CAST((ISNULL(MAX(CASE WHEN AAR.retentionDays < 0 THEN 2147483647 ELSE AAR.retentionDays END), 0)) AS VARCHAR(10))
					END)
			FROM @MountPathsTable M, archGroupCopy AGC WITH (READUNCOMMITTED), archAgingRuleExtended AAR WITH (READUNCOMMITTED)
			WHERE AGC.id = M.copyId
			AND     AGC.type != 5 /*CVA_AGCOPYTYPE_TRANSITIVE*/
			AND     AGC.flags & (524288 /*$$(CVA_SILO_COPY_FLAG)*/ | 1073741824 /*$$(CVA_DELETE_COPY_AFTER_JOBS_ARE_DELETED)*/) = 0
			AND     AGC.isSnapCopy = 0
			AND     AGC.id = AAR.copyId) ''Highest extended retention days of the Hyperscale Copies''
	FROM	@MountPathsTable ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Number of Hyperscale MountPaths] AS '@Col_999576229',[Number of Hyperscale Libraries] AS '@Col_-443223479',[Number of Hyperscale SPs] AS '@Col_565869958',[Number of Hyperscale Copies] AS '@Col_-344140509',[Number of Hyperscale SPs with more than one Copy] AS '@Col_-382844893',[Number of Hyperscale copies where Full/Incremental DV2 was run in the last 14 days.] AS '@Col_1888121622',[Highest basic retention days of the Hyperscale Copies] AS '@Col_-588336906',[Highest extended retention days of the Hyperscale Copies] AS '@Col_1968901561' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10260'), 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'))