DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10227
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[Feature Name] nvarchar(max)
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; IF OBJECT_ID(''tempdb.dbo.#UsedFeatures'', ''U'') IS NOT Null DROP TABLE #UsedFeatures
CREATE TABLE #UsedFeatures ( 
	FeatureName nvarchar(max)
)

DECLARE @CSVersion int
SELECT @CSVersion = releaseId from APP_Client WITH(NOLOCK) WHERE Id = 2

DECLARE @displayName nvarchar(30) = ''displayName''
IF(@CSVersion <= 14)
	SET @displayName = ''Name''

DECLARE @query nvarchar(max)

SET @query = ''INSERT INTO #UsedFeatures '' +
				'' SELECT P.'' + @displayName +
				'' FROM simPackage P WITH(NOLOCK) INNER JOIN simInstalledPackages I WITH(NOLOCK) ON P.id = I.simPackageId AND P.id IN (255, 251, 257, 713, 1136, 23)''

		--(N_WIN_PACKAGE_DM2_WEB_CLIENT, N_WIN_PACKAGE_CONTENTINDEXING_ENGINE, N_WIN_PACKAGE_SEARCH_SERVER 
		-- N_WIN_PACKAGE_VIRTUAL_SERVER_AGENT, N_UNIX_PACKAGE_VIRTUAL_SERVER_AGENT, N_WIN_PACKAGE_WORKFLOW_ENGINE)
EXEC sp_executesql @query

--Updating FeatureNames to match across versions
UPDATE #UsedFeatures
SET FeatureName = CASE	WHEN FeatureName = ''VirtualServer Agent'' OR FeatureName = ''Virtual Server Agent''	
							THEN ''Virtual Server''
						WHEN FeatureName = ''Web Search Client''			
							THEN ''Compliance Search''
						WHEN FeatureName = ''Content Indexing Engine''
							THEN ''Search Engine''
						WHEN FeatureName = ''Work Flow''
							THEN ''Workflow''
						ELSE FeatureName	
					END



IF EXISTS(SELECT TOP 1 1 FROM APP_Client WITH(NOLOCK) WHERE (status & 0x10000000) <> 0)
	INSERT INTO #UsedFeatures VALUES (''Edge Solution'')

IF EXISTS (SELECT TOP 1 1 FROM APP_VMProp vmprop
			INNER JOIN JMBkpStats bkp
			ON vmprop.jobid = bkp.jobid
			AND attrName=''vmcbtstatus'' AND attrVal IN (''Used'', ''Enabled'')
			INNER JOIN APP_Application ap
			ON bkp.appId = ap.id
			INNER JOIN APP_InstanceProp ip
			ON ap.instance = ip.componentNameId
			AND ip.attrName =''Virtual Server Instance Type''
			AND ip.attrVal = 102)
	INSERT INTO #UsedFeatures VALUES (''Hyper-V CBT'')

IF EXISTS (SELECT TOP 1 1 FROM APP_SubclientProp P WITH (NOLOCK)  INNER JOIN APP_Application S WITH(NOLOCK) ON S.id = P.componentNameId 
			WHERE attrName = ''Is Turbo Subclient'' AND attrVal = ''1''  AND P.modified = 0 AND S.appTypeId IN (22, 80)) --(appType Oracle and Oracle RAC)
	INSERT INTO #UsedFeatures VALUES (''Database Archiving'')

IF (@CSVersion >= 15) 
	IF EXISTS (SELECT TOP 1 1 FROM APP_MonitoringPolicyProp PP WITH(NOLOCK) INNER JOIN APP_MonitoringPolicy P WITH(NOLOCK) ON PP.monitorPolicyId = P.monitorPolicyId
			WHERE ((attrName = ''Continuous monitoring'' and attrVal > 0) OR (attrName = ''lm task id'' and attrVal > 0)) AND P.status = 0)
		INSERT INTO #UsedFeatures VALUES (''Log Monitoring'')

IF EXISTS(SELECT 1 FROM MMConfigs WITH(READUNCOMMITTED) WHERE name = ''MMS2_CONFIG_ENABLE_INFINI_STORE'' and value = 1)
      INSERT INTO #UsedFeatures VALUES (''Horizontal scaling of DDBs'')

SELECT	DISTINCT FeatureName AS ''Feature Name'' FROM	#UsedFeatures 

DROP TABLE #UsedFeatures ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Feature Name] AS '@FeatureName' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10227'), 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'))