DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10231
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[KeyName] nvarchar(max),[RelativePath] nvarchar(max),[Value] nvarchar(max),[MachineCount] int,[DefaultValue] nvarchar(max),[IsInLookUpDB] int,[SettingCount] int,[SettingType] nvarchar(max)
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @relId int = 0
SET @relId = (SELECT TOP 1 id FROM SimAllGalaxyRel ORDER BY id DESC)
DECLARE @sqlCommand NVARCHAR(MAX) = ''''
IF (@relId < 15 OR OBJECT_ID(N''dbo.APP_AdvanceSettingsEx'', N''U'') IS NULL) --V9 or < V10 SP6
BEGIN
SET @sqlCommand =	
	''SELECT 
		A.KEYNAME AS KeyName, 
		A.RELATIVEPATH AS RelativePath, 
        A.VALUE AS Value, 
        COUNT(CLIENTID) AS MachineCount ,
        ISNULL(A.DEFAULTVALUE,'''''''') AS DefaultValue, 
        A.ISINDB AS IsInLookUpDB,
		-1 AS SettingCount,
		''''N/A'''' AS SettingType 
    FROM
    (SELECT 
		RK.KEYNAME,
        RK.RELATIVEPATH,
        CAST(VALUE AS NVARCHAR(MAX)) AS VALUE, 
        CLIENTID, 
        '''''''' AS DEFAULTVALUE, 
        (CASE WHEN RI.SubKey IS NULL THEN 0 ELSE 1 END) AS ISINDB  
        FROM CvRegistryKeys RK 
        LEFT JOIN APP_RegistryStaticInfo RI 
			ON RK.KEYNAME = RI.SubKey and RK.RELATIVEPATH = SUBSTRING(RI.KeyPath, LEN(RI.KeyPath) - CHARINDEX(''''\'''',REVERSE(RI.KeyPath))+2,CHARINDEX(''''\'''',REVERSE(RI.KeyPath)))
	WHERE RK.DELETED = 0 AND RK.KEYNAME NOT LIKE ''''%\_UNTIL'''' ESCAPE(''''\'''')) A 
    GROUP BY A.KEYNAME,A.RELATIVEPATH, A.VALUE, A.DEFAULTVALUE, A.ISINDB
    ORDER BY MACHINECOUNT DESC;
	''
END
ELSE IF NOT EXISTS(SELECT 1 FROM sys.columns WHERE Name = N''hidden'' AND Object_ID = Object_ID(N''dbo.APP_AdvanceSettingsEx'')) --V10, and < V11 SP7
BEGIN
SET @sqlCommand =	
	''
	--CASE 1 : Client Level
	SELECT 
		  AA.KEYNAME AS KeyName,
		  AA.RELATIVEPATH AS RelativePath,
		  CAST(AA.VALUE AS NVARCHAR(MAX)) AS Value, 
		  COUNT(DISTINCT AA.entityId) AS MachineCount,
		  AI.DEFAULTVALUE AS DefaultValue, 
		  (CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END) AS IsInLookUpDB,
		  COUNT(DISTINCT AA.ENTITYID) AS SettingCount,
		  ''''Client'''' AS SettingType 
	FROM APP_ADVANCESETTINGS AA 
		  LEFT JOIN APP_ADVANCEKEYINFO AI 
				ON AA.KEYNAME = AI.KEYNAME AND AA.RELATIVEPATH = AI.RELATIVEPATH 
	WHERE AA.ENTITYTYPE = 3 AND AA.SOURCEENTITYTYPE = 0 --Client level setting only
		  AND AA.DELETED = 0 AND AA.KEYNAME NOT LIKE ''''%\_UNTIL'''' ESCAPE(''''\'''')
	GROUP BY AA.KEYNAME, AA.RELATIVEPATH, CAST(AA.VALUE AS NVARCHAR(MAX)), AA.entityType, AI.DEFAULTVALUE, CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END 
	UNION
	--CASE 2: Client Group Level
	SELECT 
		  AA.KEYNAME AS KeyName,
		  AA.RELATIVEPATH AS RelativePath,
		  CAST(AA.VALUE AS NVARCHAR(MAX)) AS Value, 
		  COUNT(DK.entityId) AS MachineCount,
		  AI.DEFAULTVALUE AS DefaultValue, 
		  (CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END) AS IsInLookUpDB,
		  COUNT(DISTINCT AA.ENTITYID) AS SettingCount,
		  ''''Client Group'''' AS SettingType
	FROM APP_ADVANCESETTINGS AA 
		  LEFT JOIN APP_ADVANCEKEYINFO AI 
				ON AA.KEYNAME = AI.KEYNAME AND AA.RELATIVEPATH = AI.RELATIVEPATH 
		  LEFT OUTER JOIN APP_ADVANCESETTINGS DK
				ON DK.sourceEntityType = AA.entityType
				AND DK.sourceId = AA.entityId
				AND DK.deleted = 0
				AND DK.keyName = AA.keyName
				AND DK.relativePath = AA.relativePath
	WHERE AA.ENTITYTYPE = 28 AND AA.SOURCEENTITYTYPE = 0 --Client Group level setting only
		  AND AA.DELETED = 0 AND AA.KEYNAME NOT LIKE ''''%\_UNTIL'''' ESCAPE(''''\'''')
	GROUP BY AA.KEYNAME, AA.RELATIVEPATH, CAST(AA.VALUE AS NVARCHAR(MAX)), AA.entityType, AI.DEFAULTVALUE, CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END 
	ORDER BY MachineCount DESC
	''
END
ELSE
BEGIN
SET @sqlCommand =	
	''
	--CASE 1 : Client Level
	SELECT 
		  AA.KEYNAME AS KeyName,
		  AA.RELATIVEPATH AS RelativePath,
		  CAST(AA.VALUE AS NVARCHAR(MAX)) AS Value, 
		  COUNT(DISTINCT AA.entityId) AS MachineCount,
		  AI.DEFAULTVALUE AS DefaultValue, 
		  (CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END) AS IsInLookUpDB,
		  COUNT(DISTINCT AA.ENTITYID) AS SettingCount,
		  ''''Client'''' AS SettingType 
	FROM APP_ADVANCESETTINGS AA 
		  LEFT JOIN APP_ADVANCEKEYINFO AI 
				ON AA.KEYNAME = AI.KEYNAME AND AA.RELATIVEPATH = AI.RELATIVEPATH 
		  LEFT OUTER JOIN APP_AdvanceSettingsEx AdvEx
				ON AA.id = AdvEx.keyId 
	WHERE AA.ENTITYTYPE = 3 AND AA.SOURCEENTITYTYPE = 0 --Client level setting only
		  AND AA.DELETED = 0 AND AA.KEYNAME NOT LIKE ''''%\_UNTIL'''' ESCAPE(''''\'''')
		  AND ISNULL(AdvEx.hidden,0) = 0
	GROUP BY AA.KEYNAME, AA.RELATIVEPATH, CAST(AA.VALUE AS NVARCHAR(MAX)), AA.entityType, AI.DEFAULTVALUE, CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END 
	UNION
	--CASE 2: Client Group Level
	SELECT 
		  AA.KEYNAME AS KeyName,
		  AA.RELATIVEPATH AS RelativePath,
		  CAST(AA.VALUE AS NVARCHAR(MAX)) AS Value, 
		  COUNT(DK.entityId) AS MachineCount,
		  AI.DEFAULTVALUE AS DefaultValue, 
		  (CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END) AS IsInLookUpDB,
		  COUNT(DISTINCT AA.ENTITYID) AS SettingCount,
		  ''''Client Group'''' AS SettingType
	FROM APP_ADVANCESETTINGS AA 
		  LEFT JOIN APP_ADVANCEKEYINFO AI 
				ON AA.KEYNAME = AI.KEYNAME AND AA.RELATIVEPATH = AI.RELATIVEPATH 
		  LEFT OUTER JOIN APP_AdvanceSettingsEx AdvEx
				ON AA.id = AdvEx.keyId 
		  LEFT OUTER JOIN APP_ADVANCESETTINGS DK
				ON DK.sourceEntityType = AA.entityType
				AND DK.sourceId = AA.entityId
				AND DK.deleted = 0
				AND DK.keyName = AA.keyName
				AND DK.relativePath = AA.relativePath
	WHERE AA.ENTITYTYPE = 28 AND AA.SOURCEENTITYTYPE = 0 --Client Group level setting only
		  AND AA.DELETED = 0 AND AA.KEYNAME NOT LIKE ''''%\_UNTIL'''' ESCAPE(''''\'''')
		  AND ISNULL(AdvEx.hidden,0) = 0
	GROUP BY AA.KEYNAME, AA.RELATIVEPATH, CAST(AA.VALUE AS NVARCHAR(MAX)), AA.entityType, AI.DEFAULTVALUE, CASE WHEN AI.KEYNAME IS NULL THEN 0 ELSE 1 END 
	ORDER BY MachineCount DESC
	''
END
EXEC sp_executesql @sqlCommand ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [KeyName] AS '@KeyName',[RelativePath] AS '@RelativePath',[Value] AS '@Value',[MachineCount] AS '@MachineCount',[DefaultValue] AS '@DefaultValue',[IsInLookUpDB] AS '@IsInLookUpDB',[SettingCount] AS '@SettingCount',[SettingType] AS '@SettingType' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10231'), 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'))