--- Please follow the below comments to insert SQL statements.
-- Copies with low disk space
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

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



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

--------- Insert your SQL statements here 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @copies XML

set @copies = (
	SELECT	AG.id AS '@agId', 
			AG.name AS '@agName', 
			AGC.id AS '@copyId', 
			AGC.name AS '@copyName',			
			SUM(CAST(MS.freebytesmb AS BIGINT)) AS '@freeSpaceMB', 
			SUM(CAST(MS.TotalSpaceMB AS BIGINT)) AS '@capacityMB',
			ML.LibraryId AS '@LibraryId',
			ML.AliasName AS '@LibraryName'			
	FROM	archGroupCopy AGC
			INNER JOIN ArchGroup AG ON AGC.archGroupId = AG.id
			INNER JOIN (
				SELECT	DISTINCT CopyId, MP.MediaSideId, MP.LibraryId
				FROM	MMDataPath DP
					INNER JOIN MMDrivePool DRP ON DP.DrivePoolId = DRP.DrivePoolId
					INNER JOIN MMMountPath MP ON DRP.MasterPoolId = MP.MasterPoolId
				) CM ON AGC.id = CM.CopyId
			INNER JOIN MMMediaSide MS ON CM.MediaSideId = MS.MediaSideId
			INNER JOIN MMLibrary ML ON CM.LibraryId = ML.LibraryId AND ML.LibraryEnabled = 1
	WHERE	((AGC.dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) > 0) AND (AGC.dedupeFlags & 268435456) = 0 
		AND AGC.isActive = 1 AND (AG.flags & 64) = 0
	GROUP BY AG.id, AG.name, AGC.id, AGC.name, ML.LibraryId, ML.AliasName
	ORDER BY AG.id, AGC.id
	for xml path('Copy')
)


SET @surveyXML = (
	SELECT

	@copies
	FOR XML PATH ('CopySpaceDetails') 
	-- Replace <<RESULTS_NAME>> with specific name
)

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

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


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')
)

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

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