--@querytype    XML
--Name:- Configuration Audit

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

--------- END - GENERATED CODE ---------
--------- BEGIN SURVEY QUERY ---------

DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

IF @csReleaseId < 16
	RETURN

DECLARE @csSPVersion INT = 0
SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

DECLARE @FSAppType TABLE (appTypeId INT)
INSERT	INTO @FSAppType 
SELECT	type
FROM	APP_iDAType

WHERE	type IN (1,7,8,10,11,12,14,17,18,19,20,21,24,25,26,28,29,30,33,34,35,36,38,39,40,41,42,43,44,45,46,47,48,49,50,52,54,55,56,57,58,60,63,64,65,66,67,68,72,73,74,75,76,78,82,83,84,85,86,87,91,93,105,106,107,124,127,130,131,134,137,139)

DECLARE @UsedAppType TABLE (appTypeId INT)
INSERT	INTO @UsedAppType 
SELECT	DISTINCT appType
FROM	JMBkpStats
WHERE	appType > 0 AND appType < 600

CREATE TABLE #Param (EntityType NVARCHAR(64), Entity NVARCHAR(512), Property VARCHAR(64), Parameter VARCHAR(256), CurrentValue VARCHAR(64), DefaultValue VARCHAR(64))

INSERT	INTO #Param
SELECT	'Control Panel', 'Media Management', 'Service Configuration', 'Deletion of jobs will not force full on next backup', 'Enabled', 'Not Enabled'
FROM	MMConfigs
WHERE	name = 'MMCONFIGS_DEL_INCR_DIFF_NOT_FORCE_FULL_ON_NEXT_BACKUP' AND CAST(value AS NVARCHAR(MAX)) = '1'

INSERT	INTO #Param
SELECT	'Control Panel', 'Media Management', 'Data Aging', 'Disable RMAN External Pruning', 'Not Enabled', 'Enabled'
FROM	MMConfigs
WHERE	name = 'DA_CONFIG_DISABLE_RMAN_EXTERNAL_PRUNING' AND CAST(value AS NVARCHAR(MAX)) = '0'

/*INSERT	INTO #Param
SELECT	'Control Panel', 'Media Management', 'Data Aging', 'Ignore Cycles Retention On De-Configured Clients', 'Enabled', 'Not Enabled'
FROM	MMConfigs
WHERE	name = 'DA_CONFIG_IGNORE_CYCLES_ON_DECONFIGURED_CLIENTS' AND CAST(value AS NVARCHAR(MAX)) = '1'*/ --- remove this entity collection based on feedback from Parag

DECLARE @rc INT = 0

IF @csReleaseId >= 16 AND @csSPVersion >= 12
BEGIN
	EXEC sp_executesql N'SELECT @rc = 1 FROM GXUniqueSQLScriptExecTracking WITH(NOLOCK) WHERE SqlScriptGUID = ''2B70DC31-BD11-44D6-B625-92ACAAF0E3B9''', N'@rc INT OUTPUT', @rc OUTPUT
END

IF @csReleaseId >= 16 AND (@csSPVersion >= 16 OR ISNULL(@rc,0) = 1)
	INSERT	INTO #Param
	SELECT  'Control Panel', 'Media Management', 'Chunk Size (MB)', name, chunkSizeMB, 8192
	FROM	APP_iDAType T
			INNER JOIN @UsedAppType U ON T.type = U.appTypeId
	WHERE	type IN (SELECT appTypeId FROM @FSAppType)
			AND chunkSizeMB NOT IN ('Invalid Property', '8192')
ELSE
	INSERT	INTO #Param
	SELECT  'Control Panel', 'Media Management', 'Chunk Size (MB)', name, chunkSizeMB, 4096
	FROM	APP_iDAType T
			INNER JOIN @UsedAppType U ON T.type = U.appTypeId
	WHERE	type IN (SELECT appTypeId FROM @FSAppType)
			AND chunkSizeMB NOT IN ('Invalid Property', '4096', '8192')

INSERT	INTO #Param
SELECT  'Control Panel', 'Media Management', 'Chunk Size (MB)', name, chunkSizeMB, 16384
FROM	APP_iDAType T
		INNER JOIN @UsedAppType U ON T.type = U.appTypeId
WHERE	type < 600 AND type NOT IN (SELECT appTypeId FROM @FSAppType) AND type NOT IN (13,133)
		AND chunkSizeMB NOT IN ('Invalid Property', '16384')
UNION
SELECT  'Control Panel', 'Media Management', 'Chunk Size (MB)', name, chunkSizeMB, 16384
FROM	APP_iDAType T
		INNER JOIN @UsedAppType U ON T.type = U.appTypeId
WHERE	type = 13 AND chunkSizeMB NOT IN ('Invalid Property', '4096', '16384')

INSERT	INTO #Param
SELECT	'Storage Policy with Disk Primary Copy', AG.name, 'Storage Policy Properties', 'Enable stream randomization for backup/archiver jobs', 'Not Enabled', 'Enabled'
FROM	archGroup AG 
		INNER JOIN MMDataPath DPT ON AG.defaultCopy = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType = 10001
WHERE	AG.type = 1 AND AG.flags&(4|256|16384|8388608) = 0

INSERT	INTO #Param
SELECT	'Storage Policy with Disk Primary Copy', AG.name, 'Storage Policy Properties', 'Distribute data evenly among multiple streams for offline read operations', 'Not Enabled', 'Enabled'
FROM	archGroup AG 
		INNER JOIN MMDataPath DPT ON AG.defaultCopy = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType = 10001
WHERE	AG.type = 1 AND AG.flags&(131072|256|16384|8388608) = 0
UNION
SELECT	'Storage Policy with Tape Primary Copy', AG.name, 'Storage Policy Properties', 'Distribute data evenly among multiple streams for offline read operations', 'Enabled', 'Not Enabled'
FROM	archGroup AG 
		INNER JOIN MMDataPath DPT ON AG.defaultCopy = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType <> 10001
WHERE	AG.type = 1 AND AG.flags&(131072|256|16384|8388608) = 131072

INSERT	INTO #Param
SELECT	'Storage Policy', AG.name, 'Storage Policy Properties', 'Keep resource reservations cached for jobs on this storage policy', 'Enabled', 'Not Enabled'
FROM	archGroup AG 
WHERE	AG.type = 1 AND AG.flags&(1048576|256|16384|8388608) = 1048576

INSERT	INTO #Param
SELECT	'Storage Policy', AG.name, 'Storage Policy Properties', 'Prevent multiple copies of job across selective copies', 'Enabled', 'Not Enabled'
FROM	archGroup AG 
WHERE	AG.type = 1 AND AG.flags&(4194304|256|16384|8388608) = 4194304

INSERT	INTO #Param
SELECT	'Storage Policy with Disk Primary Copy', AG.name, 'Storage Policy Properties', 'Block level Deduplication factor (KB)', AG.SIBlockSizeKB, 128
FROM	archGroup AG 
		INNER JOIN MMDataPath DPT ON AG.defaultCopy = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType = 10001
WHERE	AG.type = 1 AND AG.flags&(256|16384|8388608) = 0 AND AG.SIBlockSizeKB > 0 AND AG.SIBlockSizeKB <> 128
	AND DPL.MasterPoolId NOT IN (SELECT DISTINCT MasterPoolId FROM MMMountPath WHERE MountPathTypeId = 7)

INSERT	INTO #Param
SELECT	'Storage Policy with Cloud Primary Copy', AG.name, 'Storage Policy Properties', 'Block level Deduplication factor (KB)', AG.SIBlockSizeKB, 512
FROM	archGroup AG 
		INNER JOIN MMDataPath DPT ON AG.defaultCopy = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType = 10001
WHERE	AG.type = 1 AND AG.flags&(256|16384|8388608) = 0 AND AG.SIBlockSizeKB > 0 AND AG.SIBlockSizeKB <> 512
	AND DPL.MasterPoolId IN (SELECT DISTINCT MasterPoolId FROM MMMountPath WHERE MountPathTypeId = 7)

INSERT	INTO #Param
SELECT	'Storage Policy / Copy', AG.name + ' / ' + AGC.name, 'Deduplication Properties', 'Enable DASH Full (Read Optimized Synthetic Full)', 'Not Enabled', 'Enabled'
FROM	archGroupCopy AGC
		INNER JOIN archGroup AG ON AGC.archGroupId = AG.id
WHERE	AGC.type IN (1,2,9) AND AGC.isActive = 1 AND AGC.dedupeFlags&(262144|8388608|268435456) = 262144

INSERT	INTO #Param
SELECT	'Storage Policy / Copy', AG.name + ' / ' + AGC.name, 'Deduplication Properties', 'Enable Deduplication on Clients', 'Not Enabled', 'Enabled'
FROM	archGroupCopy AGC
		INNER JOIN archGroup AG ON AGC.archGroupId = AG.id AND AGC.id = AG.defaultCopy
WHERE	AGC.type IN (1,2,9) AND AGC.isActive = 1 AND AGC.dedupeFlags&(262144|524288|268435456) = 262144

INSERT	INTO #Param
SELECT	'Storage Policy / Copy', AG.name + ' / ' + AGC.name, 'Deduplication Properties', 'Enable source side disk cache', 'Enabled', 'Not Enabled'
FROM	archGroupCopy AGC
		INNER JOIN archGroup AG ON AGC.archGroupId = AG.id
WHERE	AGC.type IN (1,2,9) AND AGC.isActive = 1 AND AGC.dedupeFlags&(262144|268435456|1073741824) = (262144|1073741824)

INSERT	INTO #Param
SELECT	'Disk/Cloud Library', AliasName, 'Library Properties', 'Automatically add data paths for new drivepools configured', 'Not Enabled', 'Enabled'
FROM	MMLibrary
WHERE	LibraryId > 0 AND LibraryEnabled = 1 AND LibraryTypeId = 3 AND LibraryAttribute&CAST(2147483648 AS BIGINT) = 0
UNION
SELECT	'Tape Library', AliasName, 'Library Properties', 'Automatically add data paths for new drivepools configured', 'Enabled', 'Not Enabled'
FROM	MMLibrary
WHERE	LibraryId > 0 AND LibraryEnabled = 1 AND LibraryTypeId <> 3 AND LibraryAttribute&CAST(2147483648 AS BIGINT) > 0

INSERT	INTO #Param
SELECT	'Disk Library', AliasName, 'Library Properties', 'Prevent accidental deletion of data from mount paths', 'Not Enabled', 'Enabled'
FROM	MMLibrary
WHERE	LibraryId > 0 AND LibraryEnabled = 1 AND LibraryTypeId = 3 AND ExtendedAttributes&262144 = 0
	AND LibraryId NOT IN (SELECT DISTINCT LibraryId FROM MMMountPath WHERE MountPathTypeId = 7)

INSERT	INTO #Param
SELECT	'Disk/Cloud Library', AliasName, 'Library Properties', 'Spill and fill mount paths (Load balance use of mount paths)', 'Not Enabled', 'Enabled'
FROM	MMLibrary
WHERE	LibraryId > 0 AND LibraryEnabled = 1 AND LibraryTypeId = 3 AND ExtendedAttributes&1 = 0

INSERT	INTO #Param
SELECT	'Disk/Cloud Library', AliasName, 'Library Properties', 'Select mount paths for MediaAgent according to the preferred setting', 'Enabled', 'Not Enabled'
FROM	MMLibrary
WHERE	LibraryId > 0 AND LibraryEnabled = 1 AND LibraryTypeId = 3 AND ExtendedAttributes&16777216 > 0

SET @surveyXML = (
SELECT (
	SELECT	EntityType   as '@EntityType', 
			Entity       as '@Entity', 
			Property     as '@Property',
			Parameter    as '@Parameter', 
			CurrentValue as '@CurrentValue',
			DefaultValue as '@DefaultValue'
FROM	#Param
ORDER BY EntityType, Entity, Property, Parameter
	For XML PATH ('ConfigurationAudit'),type
	)For  XML PATH ('Survery')
)

DROP TABLE #Param
--------- 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 ---------

--------- END SURVEY QUERY ---------
SET NOCOUNT OFF