

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/DAMarkClientsWithNoData.sp] ---------- 

/******************************************************************************/
/*  Copyright (c) CommVault Systems                                           */
/*  All Rights Reserved                                                       */
/*                                                                            */
/*  THIS IS UNPUBLISHED PROPRIETARY SOURCE CODE OF CommVault Systems          */
/*  The copyright notice above does not evidence any                          */
/*  actual or intended publication of such source code.                       */
/*                                                                            */
/*  File name   :  DAGetDeconfiguredClientsWithNoData.sp                      */
/*                                                                            */
/*  Description :  Stored procedure that sets the 0x400	*/
/*								 bit for clients on which backup activity is disabled and   */
/*								 all the backup data is either aged or deleted							*/
/******************************************************************************/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/DAMarkClientsWithNoData.sp,v $ $Id: DAMarkClientsWithNoData.sp,v 1.1.2.22.14.1 2021/07/02 03:27:05 pkrishnan Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='DAMarkClientsWithNoData')
BEGIN
	print '>>> Drop Stored Procedure: DAMarkClientsWithNoData <<<'
	drop procedure DAMarkClientsWithNoData
END
IF EXISTS (select * from GxQscripts where name='DAMarkClientsWithNoData')
	delete from GxQscripts where name = 'DAMarkClientsWithNoData'
GO

IF EXISTS (select * from GXDBVersions where aliasname='DAMarkClientsWithNoData')
	delete from GXDBVersions where aliasname = 'DAMarkClientsWithNoData'
GO
print '... Creating Procedure: DAMarkClientsWithNoData'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure DAMarkClientsWithNoData
  @i_clientId INTEGER,
  @i_appTypeId INTEGER,
  @i_CheckBit INTEGER,
  @i_dummy INTEGER
AS
  DECLARE @o_clientId INTEGER;
  DECLARE @o_clientName NVARCHAR(255);
  DECLARE @o_reason NVARCHAR(1024);
  DECLARE @o_reasonFlags INTEGER;
-- reasonBit =    1 		Archive files or associated jobs exist
-- reasonBit =    2 		Client is not de-configured
-- reasonBit =    4 		Client is configured as migrated client
-- reasonBit =    8 		NOT IN USE
-- reasonBit =   16 		NOT IN USE
-- reasonBit =   32 		Packages still exists on client
-- reasonBit =   64 		Jobs still running on client
-- reasonBit = 	128 		Client is not older than @numberOfDaysToKeepDeconfiguredClients
-- reasonBit = 	256 		Client is configured for snap array management
-- reasonBit = 	512 		Client is configured for Index Server
-- reasonBit = 1024 		Special Client in use
--This will turn off message: "xxx rows affected".
SET NOCOUNT ON
	BEGIN TRAN
	IF OBJECT_ID('tempdb.dbo.#tmpClientList') IS NOT NULL DROP TABLE #tmpClientList
	CREATE	TABLE #tmpClientList(
			clientId 		INTEGER,
			reasonFlags INTEGER,
			reason			NVARCHAR(1024)
			PRIMARY KEY (clientId)
	)
	IF OBJECT_ID('tempdb.dbo.#tmpVMJobList') IS NOT NULL DROP TABLE #tmpVMJobList
	CREATE TABLE #tmpVMJobList (
			VMClientId 	INTEGER,
			jobId 			INTEGER,
			commcellId 	INTEGER
			PRIMARY KEY (VMClientId, jobId, commcellId)
	)
	IF OBJECT_ID('tempdb.dbo.#tmpVSAPseudoClientList') IS NOT NULL DROP TABLE #tmpVSAPseudoClientList
	CREATE TABLE #tmpVSAPseudoClientList (
			clientId 		INTEGER,
			PRIMARY KEY (clientId)
	)
	DECLARE @considerAgedJobsForClientsWithNoProtectedData INTEGER
	SET 		@considerAgedJobsForClientsWithNoProtectedData = 0
	DECLARE @considerClientsWithBackupActivityDisabled INTEGER
	SET 		@considerClientsWithBackupActivityDisabled = 0
	DECLARE @numberOfDaysToKeepDeconfiguredClients INTEGER
	SET 		@numberOfDaysToKeepDeconfiguredClients = 30
	DECLARE @errorCode		INTEGER
	SET			@errorCode		= 0
	DECLARE @flagBit  INTEGER
SET 		@flagBit 	= CAST(0x400 AS INT)
	DECLARE @deletionTime 		INTEGER
	IF(@i_appTypeId > 0 AND @i_clientId = 0)
	BEGIN
		RAISERROR('Client Id not set, but AppType Id set. Fail request', 16, 1)
		SET	@errorCode =	-1
		GOTO CX_EXIT
	END
	--Get parameter to consider number of days to keep de-configured clients (Pre-Created client usecase)
	SELECT @numberOfDaysToKeepDeconfiguredClients = isnull(value, 30)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_NUMBER_OF_DAYS_TO_KEEP_DECONFIGURED_CLIENTS'
	SET	@errorCode	=	@@ERROR
	IF	@errorCode != 0 GOTO CX_EXIT
	SET @deletionTime	= dbo.GetUnixTime(GETUTCDATE()) - @numberOfDaysToKeepDeconfiguredClients * 24 * 3600
	--Get parameter to consider aged jobs for marking clients with CLIENT_WITH_NO_ARCHIVE_DATA
	SELECT @considerAgedJobsForClientsWithNoProtectedData = isnull(value, 0)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_CONSIDER_AGED_JOBS_FOR_CLIENTS_WITH_NO_PROTECTED_DATA'
	SET	@errorCode	=	@@ERROR
	IF	@errorCode != 0 GOTO CX_EXIT
	--Get parameter to consider clients with backup activity disabled for marking them with CLIENT_WITH_NO_ARCHIVE_DATA
	SELECT @considerClientsWithBackupActivityDisabled = isnull(value, 0)
	FROM MMConfigs WITH (NOLOCK)
	WHERE name = 'DA_CONFIG_CONSIDER_CLIENTS_WITH_BACKUP_ACTIVITY_DISABLED'
	SET	@errorCode	=	@@ERROR
	IF	@errorCode != 0 GOTO CX_EXIT
	--Get the list of clients with backup activities disabled
	IF (@i_clientId = 0 AND @considerClientsWithBackupActivityDisabled != 0)
	BEGIN
		INSERT INTO #tmpClientList
		SELECT DISTINCT C.id, 0, N''
		FROM 	APP_Client C WITH (NOLOCK), JMJobAction JA WITH (NOLOCK)
		WHERE C.id = JA.clientId
		AND		C.id != 1
		AND 	JA.optype = 4
		AND 	JA.action = 1
		AND		JA.apptype = 0
		AND 	JA.mediaAgentId = 1
		UNION
		SELECT DISTINCT C.id, 0, N''
		FROM 	APP_Client C WITH (NOLOCK), App_clientGroupAssoc CGA WITH (NOLOCK), JMJobAction JA WITH (NOLOCK)
		WHERE C.id = CGA.clientId
		AND		CGA.clientGroupId = JA.clientGroupId
		AND 	JA.clientId = 1
		AND 	JA.optype = 4
		AND 	JA.action = 1
		AND		JA.apptype = 0
		AND 	JA.mediaAgentId = 1
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Get the list of clients that are de-configured
	IF (@i_clientId = 0)
	BEGIN
		INSERT INTO #tmpClientList
		SELECT DISTINCT C.id, 0, N''
		FROM 	APP_Client C WITH (NOLOCK)
		WHERE C.id != 1
AND		C.status & 0x00002 > 0
		AND NOT EXISTS (
											SELECT clientId
											FROM #tmpClientList
											WHERE clientId = C.id
									)
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	ELSE
	BEGIN
		INSERT INTO #tmpClientList
		SELECT @i_clientId, 0, N''
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients with archive files\jobs
	IF (@i_CheckBit = 0 OR @i_CheckBit & 1 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 1,
				reason = reason + N'Archive files or associated jobs exist' + char(13)
		FROM #tmpClientList T, APP_Application APP WITH (NOLOCK), archFile AF WITH (NOLOCK), archFileCopy AFC WITH (NOLOCK)
		WHERE T.clientId = APP.clientId
		AND 	(App.appTypeId = @i_appTypeId OR @i_appTypeId = 0)
		AND		APP.id = AF.appId
		AND		AF.id = AFC.archFileId
		AND		AF.commCellId = AFC.commCellId
AND		((@considerAgedJobsForClientsWithNoProtectedData = 0) OR (@considerAgedJobsForClientsWithNoProtectedData != 0 AND AFC.flags & 256 = 0))
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
IF(@i_appTypeId = 0 OR @i_appTypeId  = 106)
		BEGIN
			--Get VM client to job list
			INSERT INTO #tmpVMJobList (VMClientId, jobId, commcellId)
			SELECT DISTINCT VMClientId, jobId, commCellId
			FROM APP_VMProp Prop WITH (NOLOCK)
			WHERE EXISTS (SELECT 1 FROM #tmpClientList T WHERE Prop.VMclientId = T.clientId)
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
			--Mark VM clients with archive files\jobs associated to parent job
			UPDATE #tmpClientList
			SET reasonFlags = reasonFlags | 1,
					reason = reason + N'Archive files or associated jobs exist' + char(13)
			FROM #tmpClientList T, #tmpVMJobList VMP, archFile AF WITH (NOLOCK), archFileCopy AFC WITH (NOLOCK)
			WHERE T.clientId = VMP.VMClientId
			AND		VMP.jobId = AF.jobId
			AND		VMP.commCellId = AF.commCellId
			AND		AF.id = AFC.archFileId
			AND		AF.commCellId = AFC.commCellId
AND		((@considerAgedJobsForClientsWithNoProtectedData = 0) OR (@considerAgedJobsForClientsWithNoProtectedData != 0 AND AFC.flags & 256 = 0))
			AND 	T.reasonFlags & 1 = 0
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
			TRUNCATE TABLE #tmpVMJobList
			--Get only clients that are VSA V2 parent clients
			INSERT INTO #tmpVSAPseudoClientList
			SELECT DISTINCT T.clientId
			FROM #tmpClientList T INNER JOIN APP_Application App WITH(NOLOCK) ON T.clientId = App.clientId
			INNER JOIN JMJobDataLink JDL WITH(NOLOCK) ON App.id = JDL.parentAppid
WHERE JDL.linkType IN (7, 8)
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
			IF(@considerAgedJobsForClientsWithNoProtectedData = 0)
			BEGIN
				--Delete those that do not have associated VM job archive files
				DELETE #tmpVSAPseudoClientList
				WHERE NOT EXISTS (
								SELECT TOP 1 1
								FROM 	App_Application APP WITH (NOLOCK), JMJobDataLink JDL WITH (NOLOCK), archFile AF WITH (NOLOCK)
								WHERE #tmpVSAPseudoClientList.clientId = APP.clientId
								AND		APP.id = JDL.parentAppId
AND		JDL.linkType IN (7, 8)
								AND		JDL.childJobId = AF.jobId
								AND		JDL.commCellId = AF.commCellId
								)
			END
			ELSE
			BEGIN
				--Delete those that do not have associated VM job archive files
				DELETE #tmpVSAPseudoClientList
				WHERE NOT EXISTS (
								SELECT TOP 1 1
								FROM 	App_Application APP WITH (NOLOCK), JMJobDataLink JDL WITH (NOLOCK), archFile AF WITH (NOLOCK), archFileCopy AFC WITH (NOLOCK)
								WHERE #tmpVSAPseudoClientList.clientId = APP.clientId
								AND		APP.id = JDL.parentAppId
AND		JDL.linkType IN (7, 8)
								AND		JDL.childJobId = AF.jobId
								AND		JDL.commCellId = AF.commCellId
								AND		AF.id = AFC.archFileId
								AND		AF.commCellId = AFC.commCellId
AND		AFC.flags & 256 = 0
								)
			END
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
			--Mark VSA V2 parent clients with archive files\jobs associated to VM level jobs
			UPDATE #tmpClientList
			SET reasonFlags = reasonFlags | 1,
					reason = reason + N'Archive files or associated jobs exist' + char(13)
			FROM #tmpClientList T1 INNER JOIN #tmpVSAPseudoClientList T2 ON T1.clientId = T2.clientId
			WHERE T1.reasonFlags & 1 = 0
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
			TRUNCATE TABLE #tmpVSAPseudoClientList
		END
	END
	--Mark clients that are not de-configured when option to consider clients with backup activity disabled is off
	IF (@i_CheckBit = 0 OR @i_CheckBit & 2 > 0)
	BEGIN
		IF @considerClientsWithBackupActivityDisabled = 0
		BEGIN
			UPDATE #tmpClientList
			SET reasonFlags = reasonFlags | 2,
			reason = reason + N'Client is not de-configured' + char(13)
			FROM #tmpClientList T, APP_Client C WITH (NOLOCK)
			WHERE T.clientId = C.id
AND		(C.status & 0x00002) = 0
			SET	@errorCode	=	@@ERROR
			IF	@errorCode != 0 GOTO CX_EXIT
		END
	END
	--Mark clients that still have packages installed on them
	--Exclude 1-Touch Clients that are older than 24 hours
	IF (@i_CheckBit = 0 OR @i_CheckBit & 32 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 32,
			reason = reason + N'Packages still exists on client' + char(13)
		FROM #tmpClientList T, simInstalledPackages S WITH (NOLOCK)
		WHERE T.clientId = S.clientId
		AND 	T.clientId NOT IN (	SELECT P.componentNameId
															FROM App_ClientProp P WITH (NOLOCK), APP_Client C WITH (NOLOCK)
															WHERE P.componentNameId = C.id
															AND	P.attrName = N'1-Touch Client'
															AND P.attrVal = '1'
															AND P.modified = 0
															AND	C.refTime < (dbo.GetUnixTime(GETUTCDATE()) - (24 * 3600))
														)
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that still have jobs running on them
	IF (@i_CheckBit = 0 OR @i_CheckBit & 64 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 64,
			reason = reason + N'Jobs still running on client' + char(13)
		FROM #tmpClientList T, JMBkpJobInfo J WITH (NOLOCK), APP_Application A WITH (NOLOCK)
		WHERE T.clientId = A.clientId
		AND		A.Id = J.applicationId
		AND 	(A.appTypeId = @i_appTypeId OR @i_appTypeId = 0)
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
		--Mark clients that still have jobs running on them
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 64,
			reason = reason + N'Jobs still running on client' + char(13)
		FROM #tmpClientList T, JMRstJobInfo J WITH (NOLOCK)
		WHERE (T.clientId = J.srcClientId OR T.clientId = J.destClientId)
		AND (J.appType = @i_appTypeId OR @i_appTypeId = 0)
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that are not older than @numberOfDaysToKeepDeconfiguredClients
	IF (@i_CheckBit = 0 OR @i_CheckBit & 128 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 128,
			reason = reason + N'Client is not older than [' + CAST(@numberOfDaysToKeepDeconfiguredClients AS NVARCHAR(10)) + '] days' + char(13)
		FROM #tmpClientList T, APP_Client C WITH (NOLOCK)
		WHERE T.clientId = C.id
		AND	C.refTime > @deletionTime
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that are configured for snap array management
	IF (@i_CheckBit = 0 OR @i_CheckBit & 256 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 256,
			reason = reason + N'Client is configured for snap array management' + char(13)
		FROM #tmpClientList T, SMControlHost C WITH (NOLOCK)
		WHERE T.clientId = C.clientId
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that are configured for Index Server
	IF (@i_CheckBit = 0 OR @i_CheckBit & 512 > 0)
	BEGIN
		IF OBJECT_ID('tempdb.dbo.#tmpIndexServerNodes') IS NOT NULL DROP TABLE #tmpIndexServerNodes
		CREATE TABLE #tmpIndexServerNodes
		(
		    clientId INT PRIMARY KEY
		)
		INSERT INTO #tmpIndexServerNodes
		EXEC AppGetDAExcludeClients
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 512,
				reason = reason + N'Client is configured for Index Server' + char(13)
		FROM #tmpClientList T, #tmpIndexServerNodes TMP
		WHERE T.clientId = TMP.clientId
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that are special and in use
	IF (@i_CheckBit = 0 OR @i_CheckBit & 1024 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 1024,
				reason = reason + N'Special Client in use' + char(13)
		FROM #tmpClientList T, App_ClientProp CP WITH (NOLOCK)
		WHERE T.clientId = CP.componentNameId
		AND		CP.attrName = 'Special Client in use'
		AND 	CP.attrVal = '1'
		AND 	CP.modified = 0
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--Mark clients that are migrated clients
	IF (@i_CheckBit = 0 OR @i_CheckBit & 4 > 0)
	BEGIN
		UPDATE #tmpClientList
		SET reasonFlags = reasonFlags | 4,
			reason = reason + N'Client is configured as migrated client' + char(13)
		FROM #tmpClientList T, APP_Client C WITH (NOLOCK)
		WHERE T.clientId = C.id
AND C.specialClientFlags & 0x20 > 0
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	--For client cleanup from DataAging
	IF (@i_clientId = 0 AND @i_CheckBit = 0)
	BEGIN
		--Reset the CLIENT_WITH_NO_ARCHIVE_DATA bit
		UPDATE APP_Client
		SET specialClientFlags = specialClientFlags & (~@flagBit)
		WHERE specialClientFlags & @flagBit > 0
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
		--Set the CLIENT_WITH_NO_ARCHIVE_DATA bit if all archive files are aged or deleted
		UPDATE APP_Client
		SET specialClientFlags = C.specialClientFlags | @flagBit
		FROM  APP_Client C, #tmpClientList T
		WHERE C.id = T.clientId
		AND		T.reasonFlags = 0
		SET	@errorCode	=	@@ERROR
		IF	@errorCode != 0 GOTO CX_EXIT
	END
	CX_EXIT:
		IF @errorCode != 0
		BEGIN
			IF OBJECT_ID('tempdb.dbo.#tmpClientListOutput') IS NOT NULL
			BEGIN
				INSERT INTO #tmpClientListOutput (clientId, name, reason, reasonFlags)
				SELECT -1, N'', N'', -1
			END
			ELSE
			BEGIN
				SELECT -1, N'', N'', -1
			END
			ROLLBACK TRAN
		END
		ELSE
		BEGIN
			IF OBJECT_ID('tempdb.dbo.#tmpClientListOutput') IS NOT NULL
			BEGIN
				INSERT INTO #tmpClientListOutput (clientId, name, reason, reasonFlags)
				SELECT DISTINCT T.clientId, C.Name, T.reason, T.reasonFlags
				FROM #tmpClientList T, APP_Client C WITH (NOLOCK)
				WHERE T.clientId = C.Id
			END
			ELSE
			BEGIN
				SELECT DISTINCT T.clientId, C.Name, T.reason, T.reasonFlags
				FROM #tmpClientList T, APP_Client C WITH (NOLOCK)
				WHERE T.clientId = C.Id
			END
			COMMIT TRAN
		END
		IF OBJECT_ID('tempdb.dbo.#tmpClientList') IS NOT NULL DROP TABLE #tmpClientList
		IF OBJECT_ID('tempdb.dbo.#tmpVMJobList') IS NOT NULL DROP TABLE #tmpVMJobList
		IF OBJECT_ID('tempdb.dbo.#tmpIndexServerNodes') IS NOT NULL DROP TABLE #tmpIndexServerNodes
		IF OBJECT_ID('tempdb.dbo.#tmpVSAPseudoClientList') IS NOT NULL DROP TABLE #tmpVSAPseudoClientList
		RETURN @errorCode;
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'DAMarkClientsWithNoData')
	delete from GxQscripts where name = 'DAMarkClientsWithNoData'
GO

IF EXISTS (select * from GXDBVersions where aliasname='DAMarkClientsWithNoData')
	delete from GXDBVersions where aliasname = 'DAMarkClientsWithNoData'
GO

insert into GXDBVersions values(2, 'DAMarkClientsWithNoData',  'v1.1.2.22.14.1', 'DAMarkClientsWithNoData', 'v1.1.2.22.14.1')
GO

