--- Please follow the below comments to insert SQL statements.

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

SET NOCOUNT ON
DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 250
DECLARE @surveyXML NVARCHAR(MAX)

BEGIN TRY

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



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

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

--==========================================================================================================

IF OBJECT_ID('tempdb..#SecurityDetails') IS NOT NULL 
	DROP TABLE #SecurityDetails

create table #SecurityDetails (
	attrName varchar(256) , 
    attrVal varchar(max) ,
	attrType INT )


----------------------------------Get CS Version and SP----------------------------------------
DECLARE @CSVersion INT
SELECT @CSVersion = MAX(id) FROM simAllGalaxyRel WITH (NOLOCK)
DECLARE @SPNumber  INT = (SELECT MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) 
								WHERE clientId = (select TOP 1 clientid from APP_Platform 
												where platformType = 1 order by clientid ASC))
-----------------------------------------------------------------------------------------------


---------------------------------Users with Master Capabilities--------------------------------
IF object_id('tempdb.dbo.#userObjects') is not null
DROP TABLE #userObjects
IF object_id('tempdb.dbo.#tgroupObjects') is not null
DROP TABLE #tgroupObjects
IF object_id('tempdb.dbo.#tempObjects') is not null
DROP TABLE #tempObjects

DECLARE @resultXML XML
CREATE TABLE #tempObjects (UserId INT, UserName nvarchar(512), UserGroup nvarchar(1024), Valid INT, Status INT)

IF OBJECT_ID (N'UMPermissions', N'U') IS NOT NULL
	BEGIN
	--for v11
	DECLARE @NumberOfPermissions INT
	DECLARE @sql_query nvarchar(max) = 'SET @NOP = (select COUNT(*) from UMPermissions  with (nolock) where flags&2 = 0 AND id > 0)'
	EXEC sp_executesql @sql_query, N'@NOP int out', @NumberOfPermissions out

	CREATE TABLE #userObjects(
		userOrGroupId INT,
		isUser INT,
		permissionCount INT
	)          
	CREATE TABLE #tgroupObjects([userORGroupId] integer, [isUSer] integer, [roleId] integer, [permissionId] integer, [entitytype1]  integer, [entityId1] integer, [entityType2] integer DEFAULT 0,
	  [entityId2] integer DEFAULT 0,                            
	  [entityType3] integer DEFAULT 0,                          
	  [entityId3] integer DEFAULT 0,                            
	  [entityType4] integer DEFAULT 0,                          
	  [entityId4] integer DEFAULT 0,                            
	  [entityType5] integer DEFAULT 0,                          
	  [entityId5] integer DEFAULT 0)

	EXEC ('exec sec_getUMEntityandPermissionOnEntity ''#tgroupObjects'', 1, ''<entityList><entity entityType1="1" entityId1="1" /></entityList>'', 1, 1, 0, 0')
	
	INSERT INTO #userObjects
	SELECT DISTINCT userORGroupId, isUSer, COUNT(DISTINCT permissionId) AS PermissionCount from #tgroupObjects
	GROUP BY userORGroupId, isUSer

	INSERT INTO #tempObjects
			SELECT DISTINCT 
				U.id    as 'UserId',
				U.login as 'UserName',
				U.usergroup as 'UserGroup',
				U.flags&1 as 'Valid',
				CASE WHEN U.enabled = 1 THEN 0
				ELSE 1 END AS Status
				FROM (SELECT U.id, U.login , U.flags, ISNULL(UMG.name,'') as usergroup, U.enabled from #userObjects UO
						INNER JOIN UMUsers U with (nolock) ON U.id = UO.userOrGroupId AND UO.isUser = 1 AND UO.permissionCount = @NumberOfPermissions
						LEFT OUTER JOIN UMUserGroup UG with (nolock) ON U.id = UG.userId 
						LEFT OUTER JOIN UMGroups UMG with (nolock) ON UG.groupId = UMG.id
						WHERE U.flags & 4096 = 0 --remove SystemCreatedAdmin from Users with master capabilities list
							AND  UMG.groupFlags & 16 = 0 --remove hidden groups from the list
						) U 

	END
ELSE
	BEGIN
	--for V10 and V9

	--add few conditions to the following select statement if not v9
	DECLARE @extra_conditions_for_v10 nvarchar(max) = ''
	IF (@CSVersion >= 15)
		BEGIN
		SET @extra_conditions_for_v10 = 'AND a.taskId=0 AND a.workflowId=0 AND a.logMonitoringPolicyId=0 AND a.arrayId=0 
											AND a.cloudId=0 AND a.userGroupId=0 AND a.providerId=0 AND a.entityId1=0 
											AND a.entityId2=0 AND a.entityId3=0 AND a.entityId4=0 AND a.entityId5=0 
											AND a.entityId6=0 AND a.entityId7=0 AND a.entityId8=0 AND a.entityId9=0'
		END
	INSERT INTO #tempObjects
	execute(
	'
	DECLARE @allCaps bigint
	SET @allCaps = (select SUM(POWER(CAST(2 as bigint),id-1)) from UMCapability with (nolock) where id<64)
	select a.userID as ''UserId'',
		ISNULL(NULLIF(U.name,''''),U.login) as ''UserName'',
		ISNULL (UMG.name,'''') as ''UserGroup'',
		U.flags&1 as ''Valid'',
		CASE WHEN U.enabled = 1 THEN 0
				ELSE 1 END AS Status
		from UMUserCredentials a with (nolock)
		INNER JOIN UMUsers U with (nolock) ON U.id = a.userId
		LEFT OUTER JOIN UMUserGroup UG with (nolock) ON U.id = UG.userId 
		LEFT OUTER JOIN UMGroups UMG with (nolock) ON UG.groupId = UMG.id
		where 
		capabilities&@allCaps=@allCaps and
		a.commCellId=2 AND a.clientGroupId=0 AND a.clientId=0 AND a.appTypeId=0 AND a.instanceId=0 AND a.backupsetId=0 AND a.subClientId=0 AND a.mediaAgentId=0 AND  
											a.libraryId=0 AND a.archGroupId=0 AND a.vtContainerId=0 AND a.vtPolicyId=0 AND a.vtShelfId=0 AND a.reviewSetId=0 AND a.querySetId=0 AND a.downloadSetId=0 AND  
											a.ermId=0 AND a.legalHoldId=0 AND a.tagId=0 AND a.cdPolicyId=0 AND a.complianceReportId=0'
											+ @extra_conditions_for_v10
											)


	END

--XML generation for both
	SET @resultXML = (
	select Main.UserId, Main.UserName, LEFT(Main.UserGroups,Len(Main.UserGroups)-1) AS 'UserGroup', Main.Status 
		FROM
		(
			SELECT DISTINCT ST2.UserId, ST2.UserName, ST2.Valid ,ST2.Status ,
				(
					SELECT ST1.UserGroup + ', ' AS [text()]
					FROM #tempObjects ST1
					WHERE ST1.UserId = ST2.UserId
					ORDER BY ST1.UserId
					FOR XML PATH ('')
				) [UserGroups]
			FROM #tempObjects ST2
		) [Main]
		WHERE Valid = 1
		FOR XML RAW ('User'), ROOT('UsersWithMasterCapabilities')
	)	


IF object_id('tempdb.dbo.#userObjects') is not null
DROP TABLE #userObjects
IF object_id('tempdb.dbo.#tgroupObjects') is not null
DROP TABLE #tgroupObjects
IF object_id('tempdb.dbo.#tempObjects') is not null
DROP TABLE #tempObjects
------------------------------------------------------------------------------------------------------------
IF object_id('tempdb.dbo.#GranularAuthWorkflows') IS NOT null DROP TABLE #GranularAuthWorkflows
CREATE TABLE #GranularAuthWorkflows(Workflow NVARCHAR(MAX), Enabled nvarchar(5))
--temptable will store if the various granular authorization workflows are enabled(1), disabled(0) or not installed(-1)
INSERT INTO #GranularAuthWorkflows values
	('DeleteClientAuthorization','-1'), 
	('Delete Jobs Authorization','-1'), 
	('RestoreRequestAuthorization','-1'),
	('DeleteBackupSetAuthorization','-1'),
	('DeleteStoragePolicyAuthorization','-1'),  
	('DeleteLibraryMountPathAuthorization','-1')

DECLARE @GetProcessAuthEnabled INT = -1

--only for V10 and later
IF OBJECT_ID (N'WF_Definition', N'U') IS NOT NULL
	BEGIN

		SELECT @GetProcessAuthEnabled  = ISNULL((SELECT CASE WHEN flags&1=1 THEN 0 ELSE 1 END from WF_Definition with (nolock) where Name ='GetAndProcessAuthorization'),-1)

		--IF @GetProcessAuthEnabled = 1
		BEGIN
			UPDATE G
			SET Enabled =  CASE WHEN ActivityId is null THEN -1 WHEN flags&1=1 THEN 0 ELSE 1 END 
				 FROM WF_Definition (NOLock) WF 
					inner join #GranularAuthWorkflows G on G.Workflow = WF.Name
		END
	END



-------------------------------------Ransomware Protection for windows and Linux [os id 98 only]-------------------------------------

IF OBJECT_ID('tempdb..#MountPathsStatus') IS NOT NULL
    DROP TABLE #MountPathsStatus
CREATE TABLE #MountPathsStatus (
    MAId INT ,
    MAName nvarchar(255) ,
    MAStatus INT DEFAULT 0)
INSERT INTO #MountPathsStatus (MAId,MAName, MAStatus)
   SELECT distinct AC.id, AC.name ,
   CASE WHEN cp.enabled = 1 THEN 1
        ELSE 0
   END
   from APP_Client AC with (nolock)
        INNER JOIN MMHOST MM with (nolock) ON AC.id = MM.ClientId	
		LEFT JOIN (       -- for linux and windows MA
				SELECT componentNameId,
				CASE WHEN ISNULL(PKG.HighestSP,0) < 26
				AND SUM(CAST(attrVal AS int)) = 2 then 1
				WHEN ISNULL(PKG.HighestSP,0) >= 26
				AND SUM(CAST(attrVal AS int)) = 1 then 1
				ELSE 0 END AS 'enabled'
				FROM app_clientprop ACP with (nolock) INNER JOIN simInstalledPackages PKG with (nolock) on componentNameId = PKG.clientid -- v11 and < SP 26 we check for 2 parameters and >= 26 we check for 1 only
				WHERE (( attrName IN ('dlpEnableAutomaticDecryption','enableDLP') AND (ISNULL(PKG.HighestSP,0) < 26) )  OR
	                        (attrName IN ('enableRansomware') AND ISNULL(PKG.HighestSP,0) >= 25) )
                      AND ACP.modified = 0  AND PKG.simPackageID IN(51,1301)
				group by componentNameId,PKG.HighestSP
				) cp ON cp.componentNameId = MM.ClientId
		LEFT JOIN (
				SELECT VC.PMClientId ClientId
				FROM APP_VMToPMMap VC WITH(nolock)
				) VM ON VM.ClientId = cp.componentNameId
		LEFT JOIN (
				SELECT MD.clientId AS clientId, count(MD.DrivePoolId) AS LibraryCount
				FROM MMDrivePool MD with (nolock)
				INNER JOIN MMMountPath MP with (nolock) ON MD.MasterPoolId = MP.MasterPoolId
				WHERE MP.MountPathTypeId <> 7
				GROUP BY MD.clientId
				) AS MD ON MD.clientId = MM.ClientId
        INNER JOIN simOperatingSystem SOS with (nolock) on AC.simOperatingSystemId = SOS.id
        LEFT JOIN simInstalledPackages PKG with (nolock) on AC.id = PKG.clientid AND PKG.simPackageID IN(51,1301)
		WHERE AC.id<>1 
		and  (ISNULL(MD.LibraryCount,0) > 0 OR ISNULL(VM.ClientId,0) > 0) --  must be a physical node of a cluster MA or have libraries
		AND MM.mmhostsoftstate= 1 AND MM.mmhostenabled = 1 --Consider enabled and online media agents only
		AND (SOS.Type = 'Windows' OR (AC.simOperatingSystemId = 98 AND ISNULL(PKG.HighestSP,0) > 25)) -- consider windows and linux sim os id = 98 above FR 25
		AND (AC.releaseid >= 16 AND ISNULL(PKG.HighestSP,0) >=8) --Consider V11 SP8 and older only if its not a virtual client
-----------------------------------------------------------------------------------------------


-------------------------------------Single SignOn Details-------------------------------------
DECLARE @SSODetails XML

SET @SSODetails = (
select domainName AS 'Provider',
		description AS Description,
		flags AS 'SSOStatus',
		serviceType AS 'ServiceType'
		from UMDSProviders with (nolock) where id > 0 and serviceType = 2 --only active directory can be used as SSO provider 
		FOR XML RAW ('ProviderDetails'), ROOT ('SSODetails')
)

-----------------------------------------------------------------------------------------------

----------------------------------------------DLP----------------------------------------------
DECLARE @DLP XML
SET @DLP = 
	(select AC.id as 'clientId', ISNULL ((ACP.attrVal),0) as 'DLPEnabled' 
		from APP_CLient AC with (nolock)
				inner join APP_ClientProp ACP with (nolock) on ACP.componentNameId = AC.id
				where (status & 0x1000) > 0 --only for laptop clients
				and ACP.attrName = 'enableDLP'
	FOR XML RAW ('ClientDetails'), ROOT ('DLP'))
-----------------------------------------------------------------------------------------------


DECLARE @check_modified_if_v10_or_later nvarchar(max) = ''
IF (@CSVersion >= 15)
BEGIN
	SET @check_modified_if_v10_or_later = 'AND modified = 0'
END


DECLARE @V10V11_specific_queries nvarchar(max) = ''
IF (@CSVersion >= 15) AND OBJECT_ID (N'WF_Definition', N'U') IS NOT NULL
BEGIN
	SET @V10V11_specific_queries = 
			', --this comma is reqired
			(''PCWEnabled'',	CONVERT(varchar(3),(SELECT ISNULL(~(SELECT flags FROM WF_Definition with (nolock) WHERE Name=''Check Password Complexity'')&1,-1))),	1)
			'
END

DECLARE @V11_specific_queries nvarchar(max) = ''
IF (@CSVersion >= 16)
BEGIN
	SET @V11_specific_queries = 
			', --this comma is reqired
			(''RansomwareReportId'', (select isnull((select CONVERT(varchar(10),ReportId) from app_reports where guid =''63bdac12-06c8-4049-b9a7-d2adfcc75eb5''),''0'')),3)
			'
END


--include all queries that do no involve variables here
--because dynamic sql runs in different scope
DECLARE @general_queries nvarchar(max) = '
		select * from (VALUES
		--Access Security
		(''2FAEnabled'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''EnableTwoFactorAuthentication''  '+@check_modified_if_v10_or_later+' ),''0'')),	1),
		(''PasswordComplexityLevel'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''passwordComplexityLevel''  '+@check_modified_if_v10_or_later+' ),''2'')),	1),
		(''FailedLoginAttemptLimit'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''FailedLoginAttemptLimit''  '+@check_modified_if_v10_or_later+' ),''0'')),	1),
		(''AccountLockDuration'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''AccountLockDuration''  '+@check_modified_if_v10_or_later+' ),''0'')),	1),
		(''GuiTimeout'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''Gui timeout''  '+@check_modified_if_v10_or_later+' ),''0'')),	1),
		--Auditing
		(''AuditTrailRetentionDaysCritical'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''AuditTrailRetentionDaysCritical''  '+@check_modified_if_v10_or_later+' ),''365'')),	1),
		(''AuditTrailRetentionDaysHigh'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''AuditTrailRetentionDaysHigh''  '+@check_modified_if_v10_or_later+' ),''365'')),	1),
		(''AuditTrailRetentionDaysMedium'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''AuditTrailRetentionDaysMedium''  '+@check_modified_if_v10_or_later+' ),''240'')),	1),
		(''AuditTrailRetentionDaysLow'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''AuditTrailRetentionDaysLow''  '+@check_modified_if_v10_or_later+' ),''120'')),	1),
		--(''ApiUsageLoggingEnabled'',	(SELECT ISNULL((SELECT value FROM GXGlobalParam with (nolock) WHERE name = ''nRestApiLog''),''0'')),	1),
		(''CommcellAliasName'',	(SELECT aliasName FROM APP_Commcell with (nolock) WHERE id = 2),	1), -- this will be required to create link for user and user groups permissions report
		--Platform Security
		/* Replaced Storage Policy encryprion information with Storage Pool encryption Information
		(''PrimaryCopiesWithoutEncryption'', (SELECT CONVERT(varchar(10), COUNT(*)) from archGroupCopy AGC with (nolock)
										INNER JOIN archGroup AG with (nolock) on AG.id = AGC.archGroupId 
										AND AG.defaultCopy = AGC.id 
										AND AGC.isSnapCopy = 0 
										AND AGC.id>0 AND AGC.encType = 0
										AND (AGC.dedupeFlags & 268435456 /*CVA_HOST_GLOBAL_DEDUP_STORE_FLAG*/) = 0),	1),
		(''SecondaryCopiesWithoutEncryption'', (SELECT CONVERT(varchar(10), COUNT(*)) from archGroupCopy AGC with (nolock)
													INNER JOIN archGroup AG with (nolock) on AG.id = AGC.archGroupId 
													AND AG.defaultCopy != AGC.id 
													AND AGC.isSnapCopy = 0 AND AGC.id>0 AND AGC.encType = 0),	1),
		*/
		(''SecureMountPaths-Secure'', (SELECT CONVERT(varchar(10), COUNT(*)) FROM #MountPathsStatus WHERE MAStatus=1),1),
		(''SecureMountPaths-Total'', (SELECT CONVERT(varchar(10), COUNT(*)) FROM #MountPathsStatus),1),
		--Tenant Security
		(''PrivacyFeature'',	(SELECT ISNULL((select value from GXGlobalParam with (nolock) where name = ''3c8d1ac5575382ed39b961906a331a2ac82766fdcfa15580a''  '+@check_modified_if_v10_or_later+' ),''0'')),   1),
		--Capabilities
		(''RequiresAuthenticationForInstalls'',	(SELECT ISNULL((select value from GXGlobalParam with (nolock) where name = ''Secure Agent Install''  '+@check_modified_if_v10_or_later+' ),''0'')),   1)
		'
		+
		@V10V11_specific_queries
		+
		@V11_specific_queries
		+
		') as a (attrName, attrVal, attrType)'

INSERT into #SecurityDetails execute ( @general_queries )

--insert queries that involve variables in this scope
INSERT INTO #SecurityDetails (attrName, attrVal, attrType) VALUES
		('Version', CONVERT(varchar(3), @CSVersion), 1),
		('SSOMethod', CAST(ISNULL(@SSODetails,'<SSODetails/>') AS nvarchar(max)) , 2),
		('DLP',CAST(ISNULL(@DLP,'<DLP/>') AS nvarchar(max)),	2),
		('UsersWithMasterCapabilities',	CAST(ISNULL(@resultXML,'<UsersWithMasterCapabilities/>') AS nvarchar(max)),	2),
		('GetProcessAuthEnabled',	CAST(@GetProcessAuthEnabled AS nvarchar(max)),	1)

INSERT INTO #SecurityDetails (attrName, attrVal, attrType) 
	SELECT Workflow,Enabled,1
		FROM #GranularAuthWorkflows


--insert KMS info only if V11 and later than sp4
IF (@CSVersion >= 16) AND OBJECT_ID (N'ArchEncKeyProvider', N'U') IS NOT NULL
BEGIN
	EXEC (	'INSERT INTO #SecurityDetails VALUES 
			(''KMS'', (select ISNULL(CAST((select KeyProviderName,KeyProviderType from ArchEncKeyProvider with (nolock) where KeyProviderType>1 FOR XML RAW(''KMS''), ROOT(''KMSDetails'')) AS nvarchar(max)),''<KMSDetails/>'')),	2),
			(''PasskeyForRestore'', (select ISNULL(CAST((SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = ''AuthorizationType'' and attrVal = 2 and modified = 0 FOR XML RAW(''ClientDetails''), ROOT(''PasskeyForRestore'')) AS nvarchar(max)),''<PasskeyForRestore/>'')),	2)
		')
END


------------------------------------Storage Pool Encryption------------------------------------
-- this setting isonly valid on V11 machines
-- get storage pools and their encryption information
-- query of Client Encryption Information custom report

IF (@CSVersion >= 16)
BEGIN
EXEC('
	IF OBJECT_ID(''tempdb..#lt_ArchGroup'') IS NOT NULL DROP TABLE #lt_ArchGroup
	CREATE TABLE #lt_ArchGroup (id int)
	
	INSERT INTO #lt_ArchGroup
		SELECT  DISTINCT AG.id FROM ArchGroup AG WITH(READUNCOMMITTED)
			WHERE (AG.flags & (256 | 16384 | 8388608)) > 0

	INSERT INTO #SecurityDetails VALUES 
			(''StoragePoolsWithoutPrimaryEncryption'', (SELECT CONVERT(varchar(10), COUNT(*)) 
									FROM	#lt_ArchGroup TAG, ArchGroup AG WITH(READUNCOMMITTED), ArchGroupCopy AGC WITH(READUNCOMMITTED)
									WHERE	TAG.id = AG.id AND AG.id = AGC.archGroupId AND AG.defaultCopy = AGC.id
									AND  AGC.extendedFlags & 262144 /*CVA_ENCRYPT_ON_DEPENDENT_PRIMARY*/ = 0 AND AG.flags & 16384 /*CVA_SP_GLOBAL_AUXCOPY_POLICY_FLAG*/ = 0),	1),
			(''StoragePoolsWithoutSecondaryEncryption'', (SELECT CONVERT(varchar(10), COUNT(*)) 
									FROM	#lt_ArchGroup TAG, ArchGroup AG WITH(READUNCOMMITTED), ArchGroupCopy AGC WITH(READUNCOMMITTED)
									WHERE	TAG.id = AG.id AND AG.id = AGC.archGroupId AND AG.defaultCopy = AGC.id
									AND AGC.flags &  2097152 /*CVA_AUXCOPY_DECRYPTED_DATA*/ > 0),	1),
			(''StoragePoolsCount'',(SELECT CONVERT(varchar(10), COUNT(1))
									FROM	#lt_ArchGroup TAG, ArchGroup AG WITH(READUNCOMMITTED), ArchGroupCopy AGC WITH(READUNCOMMITTED)
									WHERE	TAG.id = AG.id AND AG.id = AGC.archGroupId AND AG.defaultCopy = AGC.id),1),
			(''ClientEncryptionReportId'', (select isnull((select ReportId from app_reports where guid =''4e4882eb-9a3b-404e-c814-2a1ff7118eaf''),''0'')),3)

	')
END
-----------------------------------------------------------------------------------------------

--------------------------------------KMS for Encryption---------------------------------------
-- this setting is valid only on v11 machines
-- get the Key management server used for password encryption (https://documentation.commvault.com/commvault/v11/article?p=118009.htm)

IF (@CSVersion >= 16) AND OBJECT_ID (N'ArchEncKeyProvider', N'U') IS NOT NULL
BEGIN
EXEC('
	declare @KMSForEncryption INT = 0
	select @KMSForEncryption  = CONVERT(INT, value)from GXGlobalParam where name = ''CvPwdV5_KeyProviderId''
	IF (@KMSForEncryption <> 0)
	BEGIN
	INSERT INTO #SecurityDetails VALUES 
		(''KMSForEncryption'',(select KeyProviderName + CASE	WHEN KeyProviderType = 1 THEN '''' --Built-in, name will be the same also
										WHEN KeyProviderType = 2 THEN '' (KMIP)'' 
										WHEN KeyProviderType = 3 THEN '' (AWS Key Management Service)'' 
										WHEN KeyProviderType = 6 THEN '' (Passphrase)'' 
										WHEN KeyProviderType = 3 THEN '' (AWS Key Management Service)'' 
										ELSE  ''Azure KMS'' END
		from ArchEncKeyProvider where KeyProviderId = @KMSForEncryption),3)
	END
	')
END
-----------------------------------------------------------------------------------------------

--------------------------------File Activity Anomally ALert-----------------------------------
-- run only on V11 machines

IF (@CSVersion >= 16)
BEGIN
EXEC('		
	IF OBJECT_ID(''tempdb..#AlertStatus'') IS NULL
		create table #AlertStatus (status int)
	declare @AlertStatus INT = 0
	declare @sp24patch INT = 0
	select @sp24patch = 1 FROM GXUniqueSQLScriptExecTracking WITH (NOLOCK) WHERE sqlScriptGUID = ''C735C4F9-2687-477A-B726-63E855775984''
	insert into #AlertStatus
				select status from ntnotificationrule NT with(nolock)
				inner join
				(
					select notifRuleId,
					cast(cast(criteriaxml as varchar(max)) as xml).value(''(/LogMonitoring_ConditionsList/conditions/@value1)[1]'', ''nvarchar(max)'') value,
					cast(cast(criteriaxml as varchar(max)) as xml).value(''(/LogMonitoring_ConditionsList/conditions/@isregex)[1]'', ''nvarchar(max)'') isregex,
					cast(cast(criteriaxml as varchar(max)) as xml).value(''(/LogMonitoring_ConditionsList/conditions/@operation)[1]'', ''nvarchar(max)'') operation,
					cast(cast(criteriaxml as varchar(max)) as xml).value(''(/LogMonitoring_ConditionsList/conditions/column/@columnName)[1]'', ''nvarchar(max)'') colname
					from ntsimpanaeventcriteria  Crit  with(nolock)
				)Availcrit
				on Availcrit.notifRuleId=NT.id 
				where Availcrit.isregex=1 and Availcrit.operation = 0 and Availcrit.colname=''Event Code'' and 
					 ( (Availcrit.value=''7:211|7:212'' and '+@SPNumber+' <= 22 ) --regex in machines older than V11 SP23 
						or (Availcrit.value=''7:211|7:212|7:293'' and '+@SPNumber+' IN (23,24) and @sp24patch = 0) -- regex in V11 SP23 
					 	or (Availcrit.value=''7:211|7:212|7:293|7:269'' and '+@SPNumber+' >= 24 and @sp24patch = 1) -- regex in machines on and later than V11 SP24
						or (Availcrit.value=''7:211|7:212|7:293|7:269|14:323|69:52'' and '+@SPNumber+' >= 26) -- regex in machines on and later than V11 SP 26
						or (Availcrit.value=''7:211|7:212|7:293|7:269|14:325|69:52'' and '+@SPNumber+' >= 27) -- regex in machines on and later than V11 SP 27
						or (Availcrit.value=''7:211|7:212|7:293|7:269|14:325|69:52|7:333'' and '+@SPNumber+' >= 29) -- regex in machines on and later than V11 SP 29
					 )  
					 and NT.status&2<>2  

	if exists( select 1 from #AlertStatus where status & 1 = 0 )
		set @AlertStatus = 1	--file anomaly alert is enabled
	else if exists( select 1 from #AlertStatus where status & 1 = 1 )
		set @AlertStatus = 0	--file anomaly is disabled
	else
		set @AlertStatus = -1	--file anomaly is deleted

	INSERT INTO #SecurityDetails VALUES 
			(''FileActivityAlert'', @AlertStatus, 1)
        

	IF OBJECT_ID(''tempdb..#AlertStatus'') IS NOT NULL
		drop table #AlertStatus
')
END
-----------------------------------------------------------------------------------------------

--get webconsole timeout period from APP_AdditionalSetting table only if V10 or later
IF (@CSVersion >= 15) AND OBJECT_ID('APP_AdvanceSettingsEx', 'U') IS NOT NULL
BEGIN
	DECLARE @use_hidden_only_if_V11 nvarchar(max)
	SELECT @use_hidden_only_if_V11  = CASE WHEN COL_LENGTH('APP_AdvanceSettingsEx', 'hidden') IS NULL
		THEN ''
		ELSE 'AND ISNULL(AdvEx.hidden,0) = 0'
		END

	EXECUTE('
	--first check if there is SessionTimeoutMinutes additional setting
	DECLARE @SessionTimeoutVal varchar(5) = ''-1''
	DECLARE @SessionTimeoutType varchar(5) = ''-1''

	SET @SessionTimeoutType = ''2''
	SET @SessionTimeoutVal = ISNULL((select TOP 1 AST.value from APP_AdvanceSettings AST  WITH (NOLOCK)
								LEFT OUTER JOIN APP_AdvanceSettingsEx AdvEx WITH (NOLOCK)
									ON AST.id = AdvEx.keyId
								WHERE AST.keyName = ''SessionTimeoutMinutes'' AND relativePath = ''WebConsole'' AND type= ''INTEGER'' AND enabled = 1
								AND AST.entityId = (SELECT clientId FROM APP_CommCell WHERE id = 2) AND AST.entityType = 3 AND AST.deleted = 0
								'+@use_hidden_only_if_V11  +'), ''-1'')
	IF(@SessionTimeoutVal = ''-1'')
		BEGIN
		--SessionTimeoutMinutes is not set
		--Try to get SessionTimeout value
		SET @SessionTimeoutType = ''1''
		SET @SessionTimeoutVal = ISNULL((select TOP 1 AST.value from APP_AdvanceSettings AST  WITH (NOLOCK)
								LEFT OUTER JOIN APP_AdvanceSettingsEx AdvEx WITH (NOLOCK)
									ON AST.id = AdvEx.keyId
								WHERE AST.keyName = ''SessionTimeout'' AND relativePath = ''WebConsole'' AND type= ''INTEGER'' AND enabled = 1
								AND AST.entityId = (SELECT clientId FROM APP_CommCell WHERE id = 2) AND AST.entityType = 3 AND AST.deleted = 0
								'+@use_hidden_only_if_V11  +'), ''-1'')
		END
	INSERT INTO #SecurityDetails VALUES (''SessionTimeoutVal'', @SessionTimeoutVal, 1)
	INSERT INTO #SecurityDetails VALUES (''SessionTimeoutType'', @SessionTimeoutType, 1)
	')
END

IF object_id('tempdb.dbo.#GranularAuthWorkflows') IS NOT null DROP TABLE #GranularAuthWorkflows		

SET @surveyXML = (
	select * from #SecurityDetails
FOR XML RAW('Nodes'), ROOT('SecurityDetails'))

IF OBJECT_ID('tempdb..#MountPathsStatus') IS NOT NULL 
	DROP TABLE #MountPathsStatus

IF OBJECT_ID('tempdb..#SecurityDetails') IS NOT NULL DROP TABLE #SecurityDetails


--==========================================================================================================


--------- END SURVEY QUERY ---------

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
	 
END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

SET @outputXML = (
SELECT
	@queryId AS '@QueryId',
	@EndTime AS '@LogDate',
	(@EndTime - @LogDate) AS '@QueryRunningTime',
	@surveyXML
FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

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