SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @LogDate AS BIGINT = dbo.getUnixTime(GETUTCDATE())
DECLARE @queryId AS BIGINT = 214
DECLARE @surveyXML NVARCHAR(MAX)

DECLARE @csReleaseId INT
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2
    
DECLARE @csHighestSP INT
SELECT  @csHighestSP = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2


DECLARE @result XML,
@UserNotLoggedIn XML,
@UserGroupWithNoUser XML,
@AlertsWithoutUser XML

----------------------------User not loggedIn in last 30 days---------------
DECLARE @nowTime BIGINT = dbo.getUnixTime(GETUTCDATE())

DECLARE @csTimeZoneName    NVARCHAR(1024) = ''
SELECT  @csTimeZoneName = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZoneName, '') = ''
BEGIN
    SELECT @csTimeZoneName = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZoneName = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZoneName, CHARINDEX(':', @csTimeZoneName, CHARINDEX(':', @csTimeZoneName, 0) + 1) + 1, 255)
END

IF object_id('tempdb.dbo.#tempUserNotLoggedIn') is not null DROP TABLE #tempUserNotLoggedIn
CREATE TABLE #tempUserNotLoggedIn (UserId INT ,UserName nvarchar(max),lastLogInTime DATETIME)

INSERT INTO #tempUserNotLoggedIn
  SELECT id AS 'id'
       ,login AS 'login',
	   dbo.UTCToLocalTime(dbo.GetDateTime(lastLogInTime ), @csTimeZoneName) 
FROM UMUsers (NOLOCK) 
WHERE 
    id > 0 
    AND lastLogInTime < (@nowTime - (30 * 24 * 60 * 60))
    AND flags & (CAST (0x200 AS INT) | CAST (0x004 AS INT) | CAST (0x080 AS INT) | CAST(0x100 AS INT) | CAST(0x800 AS INT)) = 0
    AND enabled = 1

SET @result = (SELECT UserId as '@Id' , UserName as '@UserName',lastLogInTime as '@lastLogInTime'
from #tempUserNotLoggedIn
for XML path ('UserNotLoggedIn'))

IF object_id('tempdb.dbo.#tempUserNotLoggedIn') is not null DROP TABLE #tempUserNotLoggedIn

SET @UserNotLoggedIn = (
 SELECT 
 @result 
 FOR XML PATH ('UserNotLoggedInDetails'))

 

 ----------------------------User Group With No Association-------------------------------

IF object_id('tempdb.dbo.#tempUserGroupWithoutAssoc') is not null DROP TABLE #tempUserGroupWithoutAssoc
CREATE TABLE #tempUserGroupWithoutAssoc (UserGroupId INT ,UserGroupName nvarchar(max),flags INT)

IF (@csReleaseId = 16 AND @csHighestSP >= 10) OR (@csReleaseId > 16)
BEGIN
	IF object_id('tempdb.dbo.#associations') is not null DROP TABLE #associations
	create table #associations (userOrGroupId int)

	INSERT INTO #associations 
		SELECT userOrGroupId
			FROM UMSecurityAssociations Sec
			WHERE isUser = 0
		UNION
		SELECT userOrGroupId
			FROM UMOwners O
			WHERE isUser = 0

	DECLARE @str1 nvarchar (max)  = '
	INSERT INTO #tempUserGroupWithoutAssoc
	 SELECT 
		UG.id
		,CASE 
			WHEN P.id > 0 AND P.domainName IS NOT NULL
				THEN P.domainName + ''\'' + UG.name
			ELSE UG.name 
		END AS UserGroup,1
	FROM UMGroups UG (NOLOCK) 	 
		 LEFT OUTER JOIN UMDSProviders P (NOLOCK)
			ON UG.umdsProviderId = P.id 
	WHERE 
		(SELECT COUNT(*) FROM(
			SELECT userOrGroupId FROM #associations
			INTERSECT
			(SELECT UG.id
				UNION
			SELECT umgroupId FROM UMDSGroupMaps WHERE umdsGroupId=UG.id)) DirectAndInheritedAssociations
		) = 0
		AND 
		(
			UG.umdsProviderId = 0 
			OR UG.groupFlags & 1 <> 0
		)
		AND UG.name NOT IN (''CV_Restricted_Visibility'', ''View All'')
		'
	EXEC sp_executesql @str1
END



 -----------------------------------------User groups with no users--------------------

 
IF object_id('tempdb.dbo.#tempUserGroupWithoutUser') is not null DROP TABLE #tempUserGroupWithoutUser
CREATE TABLE #tempUserGroupWithoutUser (UserGroupId INT ,UserGroupName nvarchar(max))
IF (@csReleaseId >= 16 AND @csHighestSP >= 10) 
BEGIN
DECLARE @str nvarchar (max)  = '
INSERT INTO #tempUserGroupWithoutUser
SELECT 
	UG.id
	,CASE 
		WHEN P.id > 0 AND P.domainName IS NOT NULL
		THEN P.domainName + ''\'' + UG.name  
		ELSE UG.name 
	END AS UserGroup
FROM UMGroups UG (NOLOCK) 
	 LEFT OUTER JOIN UMDSProviders P 
		ON UG.umdsProviderId = P.id 
WHERE 
	UG.id NOT IN 
	(
		SELECT groupId
		FROM UMUserGroup 
		UNION
		SELECT umGroupId 
		FROM UMDSGroupMaps
	)
	AND 
	(
		UG.groupFlags & 1 <> 0
	)
	AND UG.name NOT IN (''CV_Restricted_Visibility'', ''View All'')

UPDATE #tempUserGroupWithoutAssoc
 SET flags = 3
 where UserGroupId IN (select UserGroupId from #tempUserGroupWithoutUser)

 INSERT INTO #tempUserGroupWithoutAssoc
 select T.UserGroupId , T.UserGroupName,2
 from #tempUserGroupWithoutUser T
 LEFT OUTER JOIN #tempUserGroupWithoutAssoc U ON T.UserGroupId = U.UserGroupId
 where U.UserGroupId IS NULL'

 	EXEC sp_executesql @str
	END

SET @result = (SELECT UserGroupId as '@UserGroupId' , UserGroupName as '@UserGroupName',flags AS '@Flags'
from #tempUserGroupWithoutAssoc 
for XML path ('UserGroupWithoutUser'))


IF object_id('tempdb.dbo.#tempUserGroupWithoutUser') is not null DROP TABLE #tempUserGroupWithoutUser
IF object_id('tempdb.dbo.#tempUserGroupWithoutAssoc') is not null DROP TABLE #tempUserGroupWithoutAssoc

SET @UserGroupWithNoUser = (
 SELECT 
 @result 
 FOR XML PATH ('UserGroupWithoutAssocUser'))
 
 -----------------------Alerts---------------------------------------------------------------

IF object_id('tempdb.dbo.#AlertSubScriptions') is not null DROP TABLE #AlertSubScriptions
CREATE TABLE #AlertSubScriptions (Id INT ,AlertName nvarchar(1024))

IF OBJECT_ID('NTAlertSubScriptions', 'U') IS NOT NULL 
	INSERT INTO #AlertSubScriptions
	SELECT NTnotificationRule.id , NTnotificationRule.notificationName FROM NTnotificationRule
	INNER JOIN  NTAlertSubScriptions ON NTAlertSubScriptions.ntRuleId = NTnotificationRule.id
	INNER JOIN UMUsers ON UMUsers.id = NTAlertSubScriptions.userId
	WHERE (UMUsers.flags & 1 ) = 1 

 
IF object_id('tempdb.dbo.#tempAlertsWithoutuser') is not null DROP TABLE #tempAlertsWithoutuser
CREATE TABLE #tempAlertsWithoutuser (Id INT ,AlertName nvarchar(1024))

INSERT INTO #tempAlertsWithoutuser

 SELECT NTnotificationRule.id , NTnotificationRule.notificationName  FROM NTnotificationRule 
WHERE revision = 1 AND (status &2 ) <>2 AND (status & 1) <> 1
EXCEPT(

	SELECT NTnotificationRule.id , NTnotificationRule.notificationName FROM NTnotificationRule
	INNER JOIN  NTnotificationExternalGroups ON NTnotificationExternalGroups.NTnotificationID = NTnotificationRule.id
	INNER JOIN UMGroups ON UMGroups.id = NTnotificationExternalGroups.externalGroupId
	WHERE (UMGroups.groupFlags & 1) = 1

	UNION
	-- Take all alert with atleast one valid user through usergroups. -- 
	SELECT NTnotificationRule.id , NTnotificationRule.notificationName FROM NTnotificationRule
	INNER JOIN  NTnotificationGroups ON NTnotificationGroups.NTnotificationID = NTnotificationRule.id
	LEFT OUTER JOIN UMGroups ON UMGroups.id = NTnotificationGroups.groupId
	LEFT OUTER JOIN UMDSGroupMaps ON UMDSGroupMaps.umgroupid = NTnotificationGroups.groupId
	INNER JOIN UMUserGroup ON  UMUserGroup.groupId =  UMGroups.id OR UMUserGroup.groupId = UMDSGroupMaps.umDSgroupid
	INNER JOIN UMUsers ON UMUserGroup.userId = UMUsers.id
	WHERE (UMGroups.groupFlags & 1) = 1
	AND (UMUsers.flags & 1 ) = 1 

	-- Take all alert with atleast one valid user associated normally --

	UNION

	SELECT NTnotificationRule.id , NTnotificationRule.notificationName FROM NTnotificationRule
	INNER JOIN  NTnotificationUsers ON NTnotificationUsers.NTnotificationID = NTnotificationRule.id
	INNER JOIN UMUsers ON UMUsers.id = NTnotificationUsers.userId
	WHERE (UMUsers.flags & 1 ) = 1 

	---  Remove all subscription based alert if atleast one user subscribe it --
	UNION

	SELECT Id, AlertName FROM #AlertSubScriptions

	UNION 
	---  Remove alert if atleast one non galaxy users are present --

	SELECT NTnotificationRule.id , NTnotificationRule.notificationName FROM NTnotificationRule
	INNER JOIN  NTnotificationNonGalaxyUsers ON NTnotificationNonGalaxyUsers.NTnotificationID = NTnotificationRule.id

)



SET @result = (SELECT Id as '@AlertId' , AlertName as '@AlertName'
from #tempAlertsWithoutuser 
for XML path ('AlertsWithoutUser'))

IF object_id('tempdb.dbo.#AlertSubScriptions') is not null DROP TABLE #AlertSubScriptions
IF object_id('tempdb.dbo.#tempAlertsWithoutuser') is not null  DROP TABLE #tempAlertsWithoutuser

SET @AlertsWithoutuser = (
 SELECT 
 @result 
 FOR XML PATH ('AlertsWithoutUserDetails'))


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




 SET @surveyXML = (
SELECT 1 as '@flag',
    @UserNotLoggedIn,@UserGroupWithNoUser,@AlertsWithoutuser	
    FOR XML PATH('SecurityCleanup'))

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--select cast(@surveyXML as XML) 
SET @outputXML = 
(
    SELECT
        @queryId AS '@QueryId',
        @EndTime AS '@LogDate',
        (@EndTime - @LogDate) AS '@QueryRunningTime',
        @surveyXML
    FOR XML PATH('Rpt_CSSXMLDATA')
)
SET NOCOUNT OFF