DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10257
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[Cloud Vendor] nvarchar(max),[Server Host] nvarchar(max)
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; IF OBJECT_ID(''tempdb.dbo.#tmpCloudHostNames'') IS NOT NULL 
DROP TABLE #tmpCloudHostNames

CREATE TABLE #tmpCloudHostNames
( deviceTypeId INT,
serverHost varchar(1024)) 

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''MMDevice'' AND COLUMN_NAME = ''DisplayDeviceTypeId'') 
BEGIN
	EXEC
	(
		''INSERT INTO #tmpCloudHostNames (serverHost, deviceTypeId)
		SELECT	DISTINCT REVERSE(UserName), DEV.DisplayDeviceTypeId
		FROM	MMDeviceController MDC
				INNER JOIN MMDevice DEV on MDC.DeviceId = DEV.DeviceId
		WHERE	DEV.DeviceTypeId >= 2 AND DEV.DeviceTypeId <= 1000 AND DEV.DeviceTypeId <> 59 /*TYPE_SAN_MAGNETIC_REMOTE_HOST_HP_STOREONCE*/''
	)
END
ELSE
BEGIN
	EXEC
	(
		''INSERT INTO #tmpCloudHostNames (serverHost, deviceTypeId)
		SELECT	DISTINCT REVERSE(UserName), DEV.DeviceTypeId
		FROM	MMDeviceController MDC
				INNER JOIN MMDevice DEV on MDC.DeviceId = DEV.DeviceId
		WHERE	DEV.DeviceTypeId >= 2 AND DEV.DeviceTypeId <= 1000 AND DEV.DeviceTypeId <> 59 /*TYPE_SAN_MAGNETIC_REMOTE_HOST_HP_STOREONCE*/''
	)
END

UPDATE #tmpCloudHostNames SET serverHost =
CASE 
	WHEN CHARINDEX(''//'', serverHost) <> 0 THEN
		REVERSE(SUBSTRING(serverHost, CHARINDEX(''//'', serverHost, 1)+2, LEN(serverHost)))
	ELSE
		REVERSE(serverHost)
END

UPDATE #tmpCloudHostNames SET serverHost =
CASE 
	WHEN CHARINDEX(''@'', serverHost) <> 0 THEN
		SUBSTRING(serverHost, 1, CHARINDEX(''@'', serverHost, 1)-1)
	ELSE
		serverHost
END

-- Amazon S3 and Amazon Glacier
UPDATE #tmpCloudHostNames SET serverHost = 
CASE 
	WHEN CHARINDEX(''['', serverHost) <> 0 THEN
		SUBSTRING(serverHost, 1, CHARINDEX(''['', serverHost)-1) + SUBSTRING(serverHost,CHARINDEX('']'', serverHost, CHARINDEX(''['', serverHost))+2, LEN(serverHost))
	ELSE
		serverHost
end
FROM #tmpCloudHostNames WHERE deviceTypeId = 2 OR deviceTypeId = 53

-- 22 Oracle Cloud Infrastructure Archive Storage Classic [account_name].storage.oraclecloud.com/auth/v1.0//
-- 25 Oracle Cloud Infrastructure Object Storage [Namespace].compat.objectstorage.us-phoenix-1.oraclecloud.com//
-- 28 Oracle Cloud Infrastructure Object Storage Classic [account_name].storage.oraclecloud.com/auth/v1.0//
UPDATE #tmpCloudHostNames SET serverHost = 
CASE 
	WHEN CHARINDEX(''['', serverHost) <> 0 THEN
		SUBSTRING(serverHost, 1, CHARINDEX(''['', serverHost)-1) + SUBSTRING(serverHost,CHARINDEX('']'', serverHost, CHARINDEX(''['', serverHost))+2, LEN(serverHost))
	ELSE
		serverHost
END
FROM #tmpCloudHostNames WHERE deviceTypeId = 22 OR deviceTypeId = 25 OR deviceTypeId = 28

UPDATE #tmpCloudHostNames SET serverHost = 
CASE 
	WHEN CHARINDEX(''/'', serverHost) <> 0 THEN
		SUBSTRING(serverHost, 1, CHARINDEX(''/'', serverHost, 1)-1)
	ELSE
		serverHost
END
FROM #tmpCloudHostNames where deviceTypeId = 22 OR deviceTypeId = 28



select case deviceTypeId
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 ''Hitachi Content Platform''
WHEN 13 THEN ''Mezeo Cloud Storag''
WHEN 14 THEN ''OpenStack Object Storage''
WHEN 15 THEN ''Quantum ActiveScale''
WHEN 16 THEN ''China Mobile oNest''
WHEN 17 THEN ''Hadoop Distributed File System''
WHEN 18 THEN ''Verizon Cloud Storage''
WHEN 19 THEN ''Google Cloud Storage''
WHEN 20 THEN ''VMware vCloud Air Object Storage''
WHEN 21 THEN ''Oracle Cloud Infrastructure Object Storage Classic''
WHEN 22 THEN ''Oracle Cloud Infrastructure Archive Storage Classic''
WHEN 23 THEN ''Alibaba Cloud Object Storage Service''
WHEN 24 THEN ''Huawei Object Storage''
WHEN 25 THEN ''Oracle Cloud Infrastructure Object Storage (S3 Compatibility)''
WHEN 26 THEN ''Oracle Cloud Infrastructure Object Storage''
WHEN 27 THEN ''Telefonica Open Cloud Object Storage''
WHEN 28 THEN ''Oracle Cloud Infrastructure Archive Storage''
WHEN 29 THEN ''Inspur Cloud Object Storage''
WHEN 30 THEN ''IBM Cloud Object Storage''
WHEN 31 THEN ''Microsoft Data Lake Store''
WHEN 32 THEN ''Kingsoft Standard Storage Service''
WHEN 33 THEN ''Microsoft OneDrive''
WHEN 34 THEN ''Iron Mountain Iron Cloud''
WHEN 35 THEN ''Microsoft File Share''
WHEN 36 THEN ''Microsoft Data Lake Store Gen 2''
WHEN 39 THEN ''Microsoft Azure Storage Compatible''
WHEN 40 THEN ''S3 Compatible Storage''
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 Storage''
WHEN 200 THEN ''Ceph Object Gateway (S3-compatible)''
WHEN 201 THEN ''Cloudian HyperStore''
WHEN 202 THEN ''Dell EMC ECS (S3-compatible)''
WHEN 203 THEN ''Fujitsu Storage ETERNUS CD10000''
WHEN 204 THEN ''Hitachi Vantara Hitachi Content Platform HS3''
WHEN 205 THEN ''IBM Cloud Object Storage (S3-compatible)''
WHEN 206 THEN ''NetAppStorageGRID''
WHEN 207 THEN ''Revera Vault''
WHEN 208 THEN ''Scality RING''
WHEN 209 THEN ''Wasabi Hot Cloud Storage''
WHEN 210 THEN ''Nutanix Buckets''
WHEN 400 THEN ''Metallic Cloud Storage''
END AS [Cloud Vendor]
, serverHost AS [Server Host] from #tmpCloudHostNames
GROUP BY deviceTypeId, serverHost 
ORDER BY deviceTypeId

IF OBJECT_ID(''tempdb.dbo.#tmpCloudHostNames'') IS NOT NULL 
DROP TABLE #tmpCloudHostNames ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Cloud Vendor] AS '@Col_1280899539',[Server Host] AS '@Col_849798245' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10257'), 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'))