--- Please follow the below comments to insert SQL statements.
-- cloud connector query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

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



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

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


-- Get the details of Cloud connector type and the data stored




DECLARE @cloudConnector XML


If exists (select * from mmmountpath where mountpathtypeid = 7 and mountpathid > 0)
Begin

DECLARE @tbl TABLE (DeviceId INT, DeviceTypeId INT, dataStoredMB BIGINT)

INSERT INTO @tbl
SELECT dev.DeviceId, dev.DeviceTypeId, SUM(CAST(mv.PhysicalBytesMB AS BIGINT))
FROM mmdevice dev WITH (READUNCOMMITTED), mmmountpath mp WITH (READUNCOMMITTED), mmmountpathtostoragedevice mpdev WITH (READUNCOMMITTED), MMVolume mv WITH (READUNCOMMITTED)
WHERE mp.mountpathtypeid = 7 and mpdev.mountpathid = mp.mountpathid and dev.deviceid = mpdev.deviceid and mv.mediasideid = mp.MediaSideId and (mv.Attributes & 512) = 0 AND mv.SiloStatus <> 3
GROUP BY dev.DeviceId, dev.DeviceTypeId


UPDATE T
SET DeviceTypeId = (CASE WHEN T.DeviceTypeId = 2 AND T2.UserName not like '%amazonaws.com%' THEN 40
						WHEN T.DeviceTypeId = 3 AND T2.UserName not like '%blob.core.%' THEN 39
						ELSE T.DeviceTypeId
					END)
FROM @tbl T INNER JOIN
	(select d.DeviceId, dc.userName, ROW_NUMBER() OVER (PARTITION BY d.DeviceId ORDER BY dc.DeviceControllerId asc) as Row from MMDevice d with (readuncommitted), 
		MMDeviceController dc with (readuncommitted) where dc.DeviceId  = d.DeviceId and d.DeviceTypeId IN (2, 3)) as T2
	ON T.DeviceId = T2.DeviceId AND T2.Row = 1


SET @cloudConnector = (SELECT
		T.DeviceTypeId AS '@CloudConnectorId', 
		SUM(T.dataStoredMB) as '@DataStored'
FROM @tbl T
GROUP BY T.DeviceTypeId
FOR XML PATH('cloudConnector'))
end



SET @surveyXML = (
	SELECT
	@cloudConnector
	FOR XML PATH ('CloudSP') 
	-- 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 ---------