--- 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, '''', 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,'') AS nvarchar(max)) , 2),
('DLP',CAST(ISNULL(@DLP,'') AS nvarchar(max)), 2),
('UsersWithMasterCapabilities', CAST(ISNULL(@resultXML,'') 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)),'''')), 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)),'''')), 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 ---------