

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

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/archFLAIndexingJob.sp,v $ $Id: archFLAIndexingJob.sp,v 1.1.2.23.16.2 2021/06/10 03:27:05 pankajkumar Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='archFLAIndexingjob')
	delete from GXDBVersions where aliasname = 'archFLAIndexingjob'
GO
print '... Creating Procedure: archFLAIndexingjob'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure archFLAIndexingjob
  @i_jobId BIGINT
AS
  DECLARE @o_retVal integer;
  DECLARE @o_cloudId int;
  DECLARE @o_clientId int;
SET NOCOUNT ON
DECLARE @analyticsMAId INT = 0
DECLARE @clientGroupId INT = 0
declare @rollback BIT = 0
--get clients
IF object_id('tempdb.dbo.#tmpFLAClients') is not null DROP TABLE #tmpFLAClients
CREATE TABLE #tmpFLAClients (clientId INT)
INSERT INTO #tmpFLAClients (clientId)
SELECT CAST(oc.value as xml).value('(/clients/@clientId)[1]', 'int')
FROM dbo.GetJobOptionList (@i_jobId, 1810277523) oc
INNER JOIN APP_Client C (NOLOCK)
ON C.id = cast(oc.value as xml).value('(/clients/@clientId)[1]', 'int')
IF NOT EXISTS (SELECT 1 FROM #tmpFLAClients)
BEGIN
	SET @clientGroupId     = dbo.GetJobOption(@i_jobId, 1152211801)  --CLIENTGROUPENTITY_CLIENT_GROUP_ID
	INSERT INTO #tmpFLAClients (clientId)
	SELECT CGA.clientId
	FROM APP_ClientGroupAssoc CGA WITH (NOLOCK)
LEFT JOIN APP_ClientProp CP (NOLOCK) ON CGA.clientId = CP.componentNameId AND CP.attrName = 'Index Server Type'  --exclude index server pseudoclients
	WHERE clientGroupId =@clientGroupId  AND CP.componentNameId is null
END
IF NOT EXISTS (SELECT 1 FROM #tmpFLAClients)
GOTO FINISH
--index server
Declare @indexServerClientId int = cast(dbo.GetJobOption(@i_jobId, 2101836446) as int)
set @o_retVal = @@ERROR
if @o_retVal <> 0 or @indexServerClientId is null or @indexServerClientId = 0 GOTO FINISH
IF OBJECT_ID('tempdb.dbo.#getIndexServers') IS NOT NULL     DROP TABLE #getIndexServers
					CREATE TABLE #getIndexServers
						(     serverURL NVARCHAR(512),
							  clientId INT,
							  clientName NVARCHAR(256),
							  cloudId INT,
							  hostName NVARCHAR(256),
							  basePort INT,
							  engineName  NVARCHAR(256),
							  serverType INT,
							  indexServerClientId INT,
							  internalCloudName NVARCHAR(256)
						)
EXEC DM2GetIndexServers 'Data Analytics', @indexServerClientId
SELECT TOP 1 @o_cloudId = cloudId, @analyticsMAId=ClientId  FROM #getIndexServers WHERE indexServerClientId = @indexServerClientId
IF OBJECT_ID('tempdb.dbo.#getIndexServers') IS NOT NULL    DROP TABLE #getIndexServers
set @o_retVal = @@ERROR
if @o_retVal <> 0 or @o_cloudId is null or @o_cloudId = 0 GOTO FINISH
-- Next resolve the actua iDA typa based on job option
declare @iDATypes table
(
    typeId int
)
--support for NAS agent
DECLARE @agentType INT = dbo.GetJobOption(@i_jobId, 140004)
IF @agentType = 13
	insert into @iDATypes(typeId)
VALUES (13)
else
begin
	declare @idaTypeValue int = null
	select @idaTypeValue = dbo.GetJobOption(@i_jobId, 900055445)  --CONTENTINDEXINGOPTION_IDA_TYPE
	-- Exchange Email Data
	if (@idaTypeValue = 0 or @idaTypeValue = 2)
	begin
		insert into @iDATypes(typeId)
		select distinct i.type from APP_AppTypeGroupAssoc a inner join APP_AppTypeGroup g on g.appTypeGroupId = a.appGroupId
		inner join APP_iDAType i on i.type = a.appTypeId
		where g.groupName in ('APPGRP_ExchangeMailboxArchiver', 'APPGRP_ExchangeComplainceArchiver', 'APPGRP_ExchangeMailbox', 'APPGRP_MS_EXCHANGE')
		order by i.type
	end
	-- File System Data
	if (@idaTypeValue = 1 or @idaTypeValue = 2)
	begin
		insert into @iDATypes(typeId)
		select distinct i.type from APP_AppTypeGroupAssoc a inner join APP_AppTypeGroup g on g.appTypeGroupId = a.appGroupId
		inner join APP_iDAType i on i.type = a.appTypeId
		where g.groupName in ('APPGRP_FileSystemLikeIDA')
		and ( i.ccpType in (33 /*CV_APPTYPE_WINDOWS_FILESYSTEM*/, 58 /*CV_APPTYPE_MS_FILE_ARCHIVER*/, 63 /*CV_APPTYPE_APPLEMAC_FS*/)
		or i.type = 63 )
		order by i.type
		--vsa apptypes -- only suupported at client group level
	--	 insert into @iDATypes(typeId)
	--	 VALUES (CV_APPTYPE_VIRTUAL_SERVER)
	end
end
-- Remove App-types we don't want to support
delete @iDATypes where typeId in (67  /*67*/, 68 /*68*/, 137 /*137*/)
--------
IF object_id('tempdb.dbo.#tmpFLASubClients') is not null DROP TABLE #tmpFLASubClients
CREATE TABLE #tmpFLASubClients (commcellId INT, clientid INT, appTypeId INT, appid INT, backupsetid INT, vmName VARCHAR(1024), type INT)
INSERT INTO #tmpFLASubClients
SELECT AP.origCCId, AP.clientId, AP.appTypeId, AP.id, AP.backupSet, AP.subclientName, 0
FROM APP_Application AP WITH (NOLOCK)
	INNER JOIN #tmpFLAClients T ON AP.clientId= T.clientId
	INNER JOIN @iDATypes D ON AP.appTypeId = D.typeId
WHERE ((( subclientStatus & 2 ) = 0)   -- CV_STATUS_UNINSTALLED
               AND    (( subclientStatus & 4 ) = 0)   -- CV_STATUS_DELETED
               AND    (( subclientStatus & 16 ) = 0)  -- CV_STATUS_DUMMY
               AND    (( subclientStatus & 32 ) = 0)  -- CV_STATUS_HIDDEN
               AND    (( subclientStatus & 64 ) = 0)  -- CV_STATUS_CMD_LINE
					OR      (Ap.origCCID > 2 ) ) -- Migrated subclients
               AND subClientName NOT IN ('IndexBackup', 'DDBBackup','IndexingSubclient','DR Subclient')
			   AND   AP.dataArchGrpID > 1
--vm clients at client group level
--use vm name from app_clientprop table
IF @clientGroupId > 0 AND  (@idaTypeValue = 1 or @idaTypeValue = 2)
BEGIN
	INSERT INTO #tmpFLASubClients
	SELECT DISTINCT C.origCCId, C.id, B.appType, B.appId, 0, CP.attrVal,1
	FROM APP_Client C (NOLOCK)
	JOIN #tmpFLAClients T ON C.id= T.clientId
	JOIN APP_VMProp P (NOLOCK) ON C.ID = P.VMclientId
	JOIN JMBkpStats B (NOLOCK) ON P.jobId = B.jobId
	JOIN APP_ClientProp CP (NOLOCK) ON CP.componentNameId = C.id
	WHERE CP.modified =0 AND CP.attrName ='Virtual Machine Name'
END
--Exclude analytics of subclients whose backup is in Tape and not in the library
DECLARE @excludeAnalyticsOfDataInTape   INT
SET @excludeAnalyticsOfDataInTape = ISNULL((SELECT CAST(value AS INT) FROM GXGlobalParam WHERE name = N'ExcludeAnalyticsOfDataInTape' AND modified = 0), 1)
IF @excludeAnalyticsOfDataInTape = 1
BEGIN
	--There is a control panel option in browse/restore, which allow recovery from aged jobs. It is not a new option , it’s been there always.
	DECLARE @ShowAgedDataForBrowseAndRecovery INT = 0
	SELECT @ShowAgedDataForBrowseAndRecovery = CAST(value AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'ShowAgedDataForBrowseAndRecovery'
	IF OBJECT_ID('tempdb..#subclientlastcycle') IS NOT NULL DROP TABLE #subclientlastcycle
	CREATE TABLE #subclientlastcycle (appId INT, fullCycleNum INT)
	CREATE CLUSTERED INDEX idx_subclientlastcycle ON #subclientlastcycle(appId, fullCycleNum)
	INSERT INTO #subclientlastcycle
	SELECT JBKP.appId, max(fullCycleNum)
	FROM JMBkpStats JBKP (NOLOCK)
	JOIN #tmpFLASubClients T ON JBKP.appId = T.appid
	WHERE JBKP.status in (1, 3, 14) AND (JBKP.dataStatus = 0 OR (@ShowAgedDataForBrowseAndRecovery = 1 AND JBKP.dataStatus = 1))
	GROUP BY JBKP.appid
	IF OBJECT_ID('tempdb..#subclientlastcyclejobs') IS NOT NULL DROP TABLE #subclientlastcyclejobs
	CREATE TABLE #subclientlastcyclejobs (appId INT, jobId INT, commcellId INT)
	CREATE CLUSTERED INDEX idx_subclientlastcyclejobs ON #subclientlastcyclejobs(jobId, commcellId)
	INSERT #subclientlastcyclejobs
	SELECT T1.appId, jbkp.jobId, jbkp.commCellId
	FROM #subclientlastcycle T1 INNER JOIN JMBkpStats JBKP (NOLOCK) on T1.appId = jbkp.appId AND T1.fullcycleNum = jbkp.fullCycleNum
	WHERE JBKP.status in (1, 3, 14) AND (JBKP.dataStatus = 0 OR (@ShowAgedDataForBrowseAndRecovery = 1 AND JBKP.dataStatus = 1))
	IF OBJECT_ID('tempdb..#subclientLastcycleDataNotOnDisk') IS NOT NULL DROP TABLE #subclientLastcycleDataNotOnDisk
	CREATE TABLE #subclientLastcycleDataNotOnDisk (appId int, jobId int, commcellId int, archFileId int)
	CREATE CLUSTERED INDEX idx_subclientLastcycleDataNotOnDisk_archFileId_commcellId ON #subclientLastcycleDataNotOnDisk(archFileId, commcellId)
	CREATE INDEX idx_subclientLastcycleDataNotOnDisk_jobId_commcellId ON #subclientLastcycleDataNotOnDisk(jobId, commcellId)
	--These are jobs with at least one valid archive file that does not have a valid disk copy. We need to check for tape location only for these data
	INSERT #subclientLastcycleDataNotOnDisk
	SELECT J.appId, J.jobId, J.commcellId, AF.id
	FROM #subclientlastcyclejobs J INNER JOIN archFile AF(NOLOCK) ON J.jobId = AF.jobId AND J.commcellId = AF.commCellId
	WHERE EXISTS (SELECT 1 FROM archFileCopy AFC (NOLOCK) WHERE AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId
	AND AFC.physicalSize > 0 AND AFC.isValid = 1)
	AND NOT EXISTS (select 1 from archFileCopy AFC (NOLOCK) WHERE AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId
	AND AFC.isValid = 1 AND AFC.flags & 256 = 0 AND AFC.mediaType = 10001)
	AND AF.isValid = 1
	--Now check if these archive files we got above have data in tape with tape location within library
	;WITH IN_LIBRARY_ARCHFILES_CTE AS
	(
	SELECT DISTINCT J.jobId, J.archFileId, J.commCellId
	FROM #subclientLastcycleDataNotOnDisk J
	JOIN archFileCopy AFC (NOLOCK) on J.archFileId = AFC.archFileId AND J.commCellId = AFC.commCellId
	JOIN archChunkMapping ACM (NOLOCK) on AFC.archFileId = ACM.archFileId AND AFC.commCellId = ACM.commCellId AND AFC.archCopyId = ACM.archCopyId
	JOIN archChunk AC (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
	JOIN MMVolume V (NOLOCK) ON AC.volumeId = V.VolumeId
	JOIN MMMedia M (NOLOCK) ON V.MediaId = M.MediaId AND M.MediaTypeId <> 10001
	WHERE AFC.isValid = 1 AND (@ShowAgedDataForBrowseAndRecovery = 1 OR AFC.flags & 256 = 0)
	GROUP BY J.jobId, J.archFileId, J.commCellId, AFC.archCopyId
	HAVING MAX(M.MediaLocation) <= 2
	)
	DELETE FROM #tmpFLASubClients WHERE appid In (
		SELECT DISTINCT J.appId
		FROM #subclientLastcycleDataNotOnDisk J
		LEFT OUTER JOIN IN_LIBRARY_ARCHFILES_CTE CTE
			ON J.jobId = CTE.jobId AND J.commcellId = CTE.commcellId AND J.archFileId = CTE.archFileId
			WHERE CTE.archFileId IS NULL
		)
	IF OBJECT_ID('tempdb..#subclientlastcycle') IS NOT NULL DROP TABLE #subclientlastcycle
	IF OBJECT_ID('tempdb..#subclientlastcyclejobs') IS NOT NULL DROP TABLE #subclientlastcyclejobs
	IF OBJECT_ID('tempdb..#subclientLastcycleDataNotOnDisk') IS NOT NULL DROP TABLE #subclientLastcycleDataNotOnDisk
END
--
--reanalyze data
declare @reAnalyze int = 0
select @reAnalyze = dbo.GetJobOption(@i_jobId, 747575566)  --CONTENTINDEXINGOPTION_REANALYZE
IF @reAnalyze = 1
BEGIN
	DELETE AF
	FROM archFlaIndexingInfo  AF
		INNER JOIN #tmpFLASubClients T ON AF.appId = T.appId
	DELETE CP
	FROM APP_ClientProp CP
	INNER JOIN #tmpFLASubClients T ON CP.componentNameId = T.clientid
	where attrName in ( 'AnalysisComplete', 'LastAnalysisTime')
END
--DELETE SUBCLIENTS on which backup is not run yet
DELETE T
FROM #tmpFLASubClients T
	LEFT JOIN JMBkpStats JM WITH (NOLOCK) ON T.appId = JM.appId
	WHERE JM.appId IS NULL
--DELETE SUBCLIENTS on which does not have any successfull backup or all jobs are aged
IF object_id('tempdb.dbo.#tmpAppIdList') is not null DROP TABLE #tmpAppIdList
CREATE TABLE #tmpAppIdList (t_appId int)
INSERT INTO #tmpAppIdList (t_appId)
SELECT DISTINCT JM.appId
FROM JMBkpStats JM (NOLOCK)
JOIN  #tmpFLASubClients T ON JM.appId = T.appid
WHERE JM.status IN (1,3,14) -- JMSUCCESS, PARTIALSUCCESS, JMSUCCESSWITHWARNINGS
		AND JM.dataStatus =0 --DATA_STATUS_VALID
DELETE T
FROM #tmpFLASubClients T
WHERE T.appId NOT IN (select t_appId from #tmpAppIdList)
DROP TABLE #tmpAppIdList
--
--delete appids for which new backups are not available
DELETE t
FROM #tmpFLASubClients T
	INNER JOIN archFLAIndexingInfo FI ON T.appId = FI.appId AND (FI.flags & 64) >0
	WHERE FI.archFileId >= (SELECT MAX( ID) FROM archfile af WITH (NOLOCK) WHERE  af.appId = T.appId AND af.fileType = 1)
--DELETE EXPIRED JOBS FOR SUBCLIENT SO THAT SUBCLIENT CAN BE ANALYZED.
DELETE T
FROM archFLAIndexingInfo T
JOIN #tmpFLASubClients F ON T.appId = F.appId
WHERE T.flags & 256 >0
--delete appids entries for which status is not success
DELETE FROM archFLAIndexingrunning WHERE extraFlags <> 0
--delete unprocessed appid for which analytics job is not running
DELETE  FR
FROM archFLAIndexingrunning FR
WHERE FR.adminJobId IN ( SELECT JI.JOBID FROM JMAdminJobStatsTable JI WHERE JI.optype= 53 /*OFFLINECONTENTINDEX */)
--DONOT POPULATE SAME APPID FOR WHICH ANALYTICS JOB IS ALREADY RUNNING
DELETE t
FROM #tmpFLASubClients T
	INNER JOIN archFLAIndexingrunning FR ON T.appId = FR.appId AND FR.adminJobId != @i_jobId
-- Filter out Silo subclients
DELETE  #tmpFLASubClients
FROM    archGroupCopy C WITH (NOLOCK)
WHERE   appId = C.siloAppId
begin transaction
set @rollback = 1
--Eligible jobs for FLA
INSERT INTO archFLAIndexingrunning (commcellid, clientid, apptype, appid, backupsetid, adminjobid, extraflags, vmname, pseudoclientid, cloudId, pushedItemsCount, type)
SELECT commcellid, clientId , appTypeId, appid, backupsetid, @i_jobId,0, vmName, CASE WHEN @clientGroupId >0 AND apptypeId =106  THEN clientId ELSE 0 END, @o_cloudId, 0, 0
FROM #tmpFLASubClients
set @o_retVal = @@ERROR
if @o_retVal <> 0 GOTO FINISH
set @rollback = 0
commit transaction
FINISH:
	IF object_id('tempdb.dbo.#tmpFLAClients') is not null DROP TABLE #tmpFLAClients
	IF object_id('tempdb.dbo.#tmpFLASubClients') is not null DROP TABLE #tmpFLASubClients
	if @rollback = 1 rollback transaction
	SELECT DISTINCT @o_retVal, @o_cloudId, clientId AS o_clientId
	FROM #tmpFLASubClients
GO

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

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

insert into GXDBVersions values(2, 'archFLAIndexingJob',  'v1.1.2.23.16.2', 'archFLAIndexingJob', 'v1.1.2.23.16.2')
GO

