DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10287
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[CloudTypeId] int,[Cloud Type] nvarchar(max),[NoOfPrimaryCopies] int,[Application Size(TB)] decimal(21,3),[Media Size(TB)] decimal(21,3)
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT  T.CloudType AS CloudTypeId, 
	CASE T.CloudType
		When 2 then ''Amazon S3''
		When 3 then ''Microsoft Azure Storage''
		When 4 then ''Nirvanix MFS''
		When 5 then ''Rackspace Cloud Files''
		When 6 then ''Iron Mountain ASP''
		When 7 then ''Sun Cloud Storage''
		When 8 then ''i365 Cloud Storage''
		When 9 then ''EMC Atmos''
		When 10 then ''AT&T Synaptic Storage''
		When 11 then ''CV Object Server''
		When 12 then ''HDS Hitachi Content Platform''
		When 13 then ''Mezeo Cloud Storage''
		When 14 then ''OpenStack Object Storage''
		When 15 then ''HGST Storage''   
		When 16 then ''China Mobile oNest''   
		When 17 then ''HDS Hitachi Content Platform''
		When 18 then ''Verizon Cloud Storage''
		When 19 then ''Google Cloud Storage''
		When 20 then ''VMware vCloud Air Object Storage''
		When 21 then ''Oracle Storage Cloud Service''	  
		When 22 then ''Oracle Storage Cloud Archive Service''	 
		When 23 then ''AliCloud Object Storage Service''	 
		When 24 then ''Huawei Object Storage''
		When 25 then ''Oracle Cloud Infrastructure Object Storage Service - S3 Compatibility''
		When 26 then ''Oracle Cloud Infrastructure Object Storage Service''
		When 27 then ''Telefonica Open Cloud Object Storage''
		When 39 then ''Azure Compatible''
		When 40 then ''S3 Compatible''
		When 41 then ''OBJECT BASE START''
		When 51 then ''Caringo CAStor'' 
		When 52 then ''Dell DX Object Storage Platform''      
		When 53 then ''Amazon Glacier''
		When 54 then ''DDN WOS''
		When 59 then ''HPE Catalyst''
		When 60 then ''OBJECT BASE END''
		Else ''Others''
	    END AS ''Cloud Type'', 
		count(distinct(archGroupId)) AS NoOfPrimaryCopies,
		cast(sum(JM.totalUncompBytes)*1.0/1024/1024/1024/1024 AS NUMERIC(20,2)) as ''Application Size(TB)'',
        cast(sum(JM.nwTransBytes)*1.0/1024/1024/1024/1024 AS NUMERIC(20,2)) as ''Media Size(TB)''
from
(SELECT  DISTINCT  device.deviceTypeid AS CloudType, AGC.archGroupId as archGroupId
         FROM   archGroup AG WITH (NOLOCK)
		 INNER JOIN archGroupCopy AGC WITH (NOLOCK)
		 ON AG.id = AGC .archGroupId 
		 AND AGC.ID = AG.defaultCopy
		 AND AG.id > 1 
         INNER JOIN MMDataPath DPT WITH (NOLOCK) ON AGC.id = DPT.CopyId 
         INNER JOIN MMDrivePool DPL WITH (NOLOCK) ON DPL.DrivePoolId = DPT.DrivePoolId
         INNER JOIN MMMasterPool MPL WITH (NOLOCK) ON MPL.MasterPoolId = DPL.MasterPoolId
		 INNER JOIN mmmountpath PATH WITH (NOLOCK) ON PATH.LibraryId =MPL.LibraryId
		 AND PATH.mountpathtypeid =7    -- CLOUD LIBRARY
		 INNER JOIN  MMMountPathToStorageDevice  devicemap
		 ON PATH.MountPathId = devicemap.MountPathId
		 INNER JOIN MMDevice device WITH (NOLOCK)
		 ON devicemap.deviceid = device.DeviceId) T
		 INNER JOIN JMBkpStats JM WITH (NOLOCK)
		 ON JM.dataArchGrpId = T.archGroupId
		 AND JM.status in (1,3,14,16) --- for completed or partial completed jobs
group by T.CloudType ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [CloudTypeId] AS '@CloudTypeId',[Cloud Type] AS '@Col_1619167877',[NoOfPrimaryCopies] AS '@NoOfPrimaryCopies',[Application Size(TB)] AS '@Col_748279524',[Media Size(TB)] AS '@Col_1799138000' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10287'), 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'))