--@querytype 	CSV
--Name:- Index Backup Stats (Health)
--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET QUOTED_IDENTIFIER OFF
--------- BEGIN SURVEY QUERY ---------
DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2
DECLARE @csSPVersion INT = 0
IF @csReleaseId >= 15
	SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2
IF @csReleaseId < 15 OR @csReleaseId = 15 AND @csSPVersion < 7
BEGIN
	RETURN
END
DECLARE @timezonename    NVARCHAR(1024) = ''
SELECT  @timezonename = dbo.GetClientTimeZone(2)
IF ISNULL(@timezonename, '') = ''
BEGIN
    SELECT @timezonename = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @timezonename = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@timezonename, CHARINDEX(':', @timezonename, CHARINDEX(':', @timezonename, 0) + 1) + 1, 255)
END
IF object_id('dbo.IdxDbState') IS NOT NULL 
BEGIN
	IF object_id('tempdb.dbo.#AppsDisabled') IS NOT NULL DROP TABLE #AppsDisabled
	IF object_id('tempdb.dbo.#AppsBackupDisableForInstance') IS NOT NULL DROP TABLE #AppsBackupDisableForInstance
	IF object_id('tempdb.dbo.#tmpDetails') is not null DROP TABLE #tmpDetails
	IF object_id('tempdb.dbo.#tempValidApps') is not null DROP TABLE #tempValidApps
	IF object_id('tempdb.dbo.#tempSCIndexBS') is not null DROP TABLE #tempSCIndexBS
	IF object_id('tempdb.dbo.#tmpLegalHoldBackupsets') is not null DROP TABLE #tmpLegalHoldBackupsets
	IF object_id('tempdb.dbo.#tmpBackupHistory') is not null DROP TABLE #tmpBackupHistory
	IF object_id('tempdb.dbo.#tmpIndexMigrations') is not null DROP TABLE #tmpIndexMigrations
	CREATE TABLE #tmpDetails(
		ClientId INT, 
		Client NVARCHAR(255),
		AppType VARCHAR(255),
		Backupset NVARCHAR(128),
		backupSetGUID VARCHAR(36),
		dbName VARCHAR(128),
		subclientName NVARCHAR(255),
		idxEngineType INT,
		IndexServer NVARCHAR(255),
		uptodate INT NULL,
		lastTimeDbUptodate BIGINT NULL,
		lastPlayedJobTime BIGINT NULL,
		lastCompactionTime BIGINT NULL,
		errorLvl INT NULL,
		errorDesc NVARCHAR(4000) NULL,
		LastBackupTime BIGINT NULL,
		LastBackupForDb BIGINT NULL,
		LastIdxErrorTime BIGINT NULL,
		FirstBackupForDb BIGINT NULL,
		ScIdxConversionTime BIGINT NULL,
		DatabaseSize BIGINT NULL,
		LogsSize BIGINT NULL,
		ReportsSize BIGINT NULL,
		MaintenanceSize BIGINT NULL,
		IndexedItems BIGINT NULL,
		ApplicationSize BIGINT NULL,
		LastIndexServer NVARCHAR(255),
		MigrationReason INT NULL
	)
	DECLARE @dynQuery NVARCHAR(MAX)
	-- Get backups disabled for instance if CommCell version is >= 11.4.0
	CREATE TABLE #AppsBackupDisableForInstance(appId INT, PRIMARY KEY(appId))
	IF @csReleaseId > 15 AND @csSPVersion >= 4
	BEGIN
		SET @dynQuery = 'SELECT DISTINCT APP.id
		FROM APP_Application APP WITH (NOLOCK)
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND J.instanceId = APP.instance AND J.instanceId > 1'
		INSERT INTO #AppsBackupDisableForInstance(appId)
			EXEC sp_executeSQL @dynQuery
	END
	--> List of apps that has backup activity disabled thru different entry points
	CREATE TABLE #AppsDisabled(appId INT, PRIMARY KEY(appId))
	INSERT INTO #AppsDisabled	
	SELECT DISTINCT APP.id   -- Backup disabled for Client Group
	FROM APP_Application APP WITH (NOLOCK)
		JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON APP.clientId = CGA.clientId
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND CGA.clientGroupId = J.clientGroupId AND J.clientGroupId > 0 AND J.clientId = 1 AND J.appType = 0 AND J.appId = 1
	UNION
	SELECT DISTINCT APP.id   -- Backup disabled for Subclient Policy
	FROM APP_Application APP WITH (NOLOCK)
		JOIN (
			SELECT componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
			FROM APP_SubClientProp WITH (NOLOCK)
			WHERE attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
		) T ON APP.id = T.appId
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND J.appId = T.subclientPolicyAppId
	UNION
	SELECT DISTINCT APP.id   -- Backup disabled for Subclient
	FROM APP_Application APP WITH (NOLOCK)
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND J.appId = APP.id
	UNION
	SELECT DISTINCT APP.id   -- Backup disabled for Client: 1) For all appTypes 2) For a particular appType
	FROM APP_Application APP WITH (NOLOCK)
		JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1
		AND J.clientId = APP.ClientId AND J.appType IN (APP.appTypeId, 0)  
	UNION
	SELECT appId FROM #AppsBackupDisableForInstance
	UNION
	SELECT DISTINCT APP.id 
	FROM APP_BackupSetName BS WITH (NOLOCK)
		JOIN APP_BackupSetProp PROP WITH (NOLOCK) ON PROP.componentNameId = BS.id
		JOIN APP_Application APP WITH(NOLOCK) ON BS.id = APP.backupSet
	WHERE PROP.attrName = 'OnDemand Backupset' AND PROP.attrVal = '1' AND PROP.modified = 0
	
	CREATE TABLE #tempValidApps(appId INT, bsId INT, firstBackup BIGINT, lastBackup BIGINT, scCreationTime BIGINT) 
	INSERT INTO #tempValidApps(appId, bsId, firstBackup, lastBackup, scCreationTime)
	SELECT DISTINCT APP.id, APP.backupSet, MIN(AF.cTime), MAX(AF.ctime), APP.refTime 
	FROM APP_Application APP WITH(NOLOCK)
	JOIN ARCHFILE AF WITH(NOLOCK) ON AF.appId = APP.id and AF.isValid = 1 AND AF.fileType IN(2, 6)
	JOIN archFileCopy AFC WITH(NOLOCK) ON AF.id = AFC.archFileId AND AFC.isValid = 1 AND(AFC.flags & 256 = 0)
	LEFT JOIN #AppsDisabled INV ON APP.id = INV.appId
	WHERE APP.appTypeId != 137
	AND APP.subclientStatus & 4214 = 0 --CV_STATUS_UNINSTALLED, CV_STATUS_DELETED, CV_STATUS_DUMMY, CV_STATUS_HIDDEN, CV_STATUS_CMD_LINE, CV_STATUS_LOGCMD_LINE
	AND INV.appId IS NULL
	GROUP BY APP.id, APP.backupSet, APP.refTime
	
	CREATE TABLE #tempSCIndexBS(bsId INT, scIdxPropCreationTime INT)
	INSERT INTO #tempSCIndexBS(bsId, scIdxPropCreationTime)
	SELECT DISTINCT APP.backupSet, PROP.created
	FROM APP_Application APP WITH(NOLOCK)
	JOIN APP_IDAName IDA WITH(NOLOCK) ON APP.clientId = IDA.clientId AND APP.appTypeId = IDA.appTypeId
	JOIN APP_IDAProp PROP WITH(NOLOCK) ON PROP.componentNameId = IDA.id
	WHERE PROP.attrName = 'Subclient Index' AND PROP.attrVal = '1' AND PROP.modified = 0
	
	CREATE TABLE #tmpBackupHistory (
		dbId INT,
		indexedItems BIGINT,
		applicationSize BIGINT
	)
	CREATE TABLE #tmpIndexMigrations (
		dbId INT,
		currentIdxServer INT,
		lastServerName NVARCHAR(255),
		migrationReason INT DEFAULT -1
	)

	IF @csReleaseId >= 16
	BEGIN
		SET @dynQuery = NULL
		-- For index playback history, we need to check the existance of table IdxDbPlaybackStats in either CS db or HistoryDB
		IF @csSPVersion >= 18 AND @csSPVersion < 23 -- Between 11.0 SP18 AND 11.0 SP23
			SET @dynQuery = 'SELECT DISTINCT DB.id, JH.indexedItems, JH.applicationSizeMB
				FROM App_IndexDBInfo DB WITH(NOLOCK)
				LEFT JOIN (
					SELECT OH.dbId, OH.indexedItems, OH.applicationSizeMB
					FROM IdxDbPlaybackStats OH WITH(NOLOCK)
					INNER JOIN (
						SELECT dbId, MAX(jobId) AS maxJobId
						FROM IdxDbPlaybackStats WITH(NOLOCK)
						GROUP BY dbId
					) IH ON OH.dbId = IH.dbId AND OH.jobId = IH.maxJobId
				) JH ON DB.id = JH.dbId'
		ELSE IF @csSPVersion >= 23 -- >= 11.0 SP23
			SET @dynQuery = 'SELECT DISTINCT DB.id, JH.indexedItems, JH.applicationSizeMB
				FROM App_IndexDBInfo DB WITH(NOLOCK)
				LEFT JOIN (
					SELECT OH.dbId, OH.indexedItems, OH.applicationSizeMB
					FROM HistoryDB..IdxDbPlaybackStats OH WITH(NOLOCK)
					INNER JOIN (
						SELECT dbId, MAX(jobId) AS maxJobId
						FROM HistoryDB..IdxDbPlaybackStats WITH(NOLOCK)
						GROUP BY dbId
					) IH ON OH.dbId = IH.dbId AND OH.jobId = IH.maxJobId
				) JH ON DB.id = JH.dbId'
		IF @dynQuery IS NOT NULL
			-- Add to backup history table
			INSERT INTO #tmpBackupHistory(dbId, indexedItems, applicationSize)
				EXEC sp_executeSQL @dynQuery
		-- For migration history
		IF @csSPVersion >= 12 -- >= 11.0 SP12
		BEGIN
			IF @csSPVersion < 23
				SET @dynQuery = 'SELECT IM.indexId, IM.toClientId, IMT.name, -1
					FROM (
						SELECT indexId, fromClientId, toClientId, MAX(endTime) AS [lastEndTime]
						FROM IdxServerMigrations WITH(NOLOCK)
						GROUP BY indexId, fromClientId, toClientId
					) TM
					JOIN IdxServerMigrations IM WITH(NOLOCK) ON TM.indexId = IM.indexId AND TM.fromClientId = IM.fromClientId AND TM.toClientId = IM.toClientId AND TM.lastEndTime = IM.endTime
					JOIN APP_Client IMT WITH(NOLOCK) ON IM.toClientId = IMT.id
					WHERE IM.fromClientId <> IM.toClientId'
			ELSE
				SET @dynQuery = 'SELECT IM.indexId, IM.toClientId, IMT.name, IM.reason
					FROM (
						SELECT indexId, fromClientId, toClientId, MAX(endTime) AS [lastEndTime]
						FROM IdxServerMigrations WITH(NOLOCK)
						GROUP BY indexId, fromClientId, toClientId
					) TM
					JOIN IdxServerMigrations IM WITH(NOLOCK) ON TM.indexId = IM.indexId AND TM.fromClientId = IM.fromClientId AND TM.toClientId = IM.toClientId AND TM.lastEndTime = IM.endTime
					JOIN APP_Client IMT WITH(NOLOCK) ON IM.toClientId = IMT.id
					WHERE IM.fromClientId <> IM.toClientId'

			-- Insert into index migrations history table
			INSERT INTO #tmpIndexMigrations(dbId, currentIdxServer, lastServerName, migrationReason)
				EXEC sp_executeSQL @dynQuery
		END
		
	END
	
	INSERT INTO #tmpDetails(ClientId, Client, AppType, Backupset, backupSetGUID, dbName, subclientName, idxEngineType, IndexServer, uptodate, lastTimeDbUptodate, lastPlayedJobTime, lastCompactionTime, LastBackupForDb, errorLvl, errorDesc, LastIdxErrorTime, FirstBackupForDb, ScIdxConversionTime, DatabaseSize, LogsSize, ReportsSize, MaintenanceSize, IndexedItems, ApplicationSize, LastIndexServer, MigrationReason)
	SELECT DISTINCT 
		app.clientId,                                                                                                                                        -- ClientId
		CC.name,                                                                                                                                             -- Client
		ida.name,                                                                                                                                            -- AppType
		bs.name,                                                                                                                                             -- Backupset
		idx.backupSetGUID,                                                                                                                                   -- backupSetGUID
		idx.DbName,                                                                                                                                          -- dbName
		(CASE WHEN TBS.bsId IS NOT NULL THEN APP.subclientName ELSE '' END),                                                                                 -- subclientName
		idx.idxDbEngineType,                                                                                                                                 -- idxEngineType
		mm.name,                                                                                                                                             -- IndexServer
		st.dbUptoDate,                                                                                                                                       -- uptodate
		ISNULL(st.properties.value('(/Indexing_DbStats/@lastCompletedBkpTime)[1]', 'BIGINT'), -1),                                                           -- lastTimeDbUptodate
		ISNULL(st.properties.value('(/Indexing_DbStats/@lastPlayedBkpTime)[1]', 'BIGINT'), -1),                                                              -- lastPlayedJobTime
		ISNULL(st.properties.value('(/Indexing_DbStats/@lastCompactionTime)[1]', 'BIGINT'), 0),                                                              -- lastCompactionTime
		(CASE WHEN TBS.bsId IS NULL THEN (SELECT MAX(lastBackup) FROM #tempValidApps TVA WHERE TVA.bsId = BS.id) ELSE APPS.lastBackup END),                  -- LastBackupForDb
		st.errorlvl,                                                                                                                                         -- errorLvl
		ISNULL(st.errordesc, ''),                                                                                                                            -- errorDesc
		ISNULL(st.properties.value('(/Indexing_DbStats/@lastIdxErrorTime)[1]', 'BIGINT'), -1),                                                               -- LastIdxErrorTime
		(CASE WHEN TBS.bsId IS NULL THEN (SELECT MIN(firstBackup) FROM #tempValidApps TVA WHERE TVA.bsId = BS.id) ELSE APPS.firstBackup END),                -- FirstBackupForDb
		(CASE WHEN (TBS.bsId is NOT NULL AND TBS.scIdxPropCreationTime > APPS.scCreationTime) THEN TBS.scIdxPropCreationTime ELSE -1 END),                   -- ScIdxConversionTime
		ISNULL(ISNULL(st.properties.value('(/Indexing_DbStats/@dbSize)[1]', 'BIGINT'), st.properties.value('(/Indexing_DbStats/@dbsize)[1]', 'BIGINT')), 0), -- DatabaseSize
		ISNULL(st.properties.value('(/Indexing_DbStats/@logsSize)[1]', 'BIGINT'), 0),                                                                        -- LogsSize
		ISNULL(st.properties.value('(/Indexing_DbStats/@reportsSize)[1]', 'BIGINT'), 0),                                                                     -- ReportsSize
		ISNULL(st.properties.value('(/Indexing_DbStats/@maintenanceSize)[1]', 'BIGINT'), 0),                                                                 -- MaintenanceSize
		ISNULL(TBH.indexedItems, -1),                                                                                                                        -- IndexedItems
		ISNULL(TBH.applicationSize, -1),                                                                                                                     -- ApplicationSize
		ISNULL(TIM.lastServerName, ''),                                                                                                                      -- LastIndexServer
		ISNULL(TIM.migrationReason, -1)                                                                                                                      -- MigrationReason
	FROM App_IndexDBInfo idx WITH(NOLOCK)
		INNER JOIN Idxdbstate st WITH(NOLOCK) ON idx.id = st.dbId
		INNER JOIN APP_BackupSetName bs WITH(NOLOCK) on bs.id = idx.backupSetId
		INNER JOIN APP_Application app WITH(NOLOCK) on app.backupSet = bs.id
		INNER JOIN #tempValidApps apps ON APP.id = APPS.appId
		INNER JOIN APP_Client CC WITH(NOLOCK) on cc.id = app.clientId
		INNER JOIN app_client mm WITH(NOLOCK) on mm.id = idx.currentIdxServer
		INNER JOIN APP_iDAType ida WITH(NOLOCK) on APP.appTypeId = IDA.type
		LEFT JOIN #tempSCIndexBS TBS ON BS.id = TBS.bsId
		LEFT JOIN #tmpBackupHistory TBH ON idx.id = TBH.dbId
		LEFT JOIN #tmpIndexMigrations TIM ON TIM.dbId = idx.id AND TIM.currentIdxServer = idx.currentIdxServer
	WHERE (TBS.bsId is NULL OR IDX.dbName = APP.GUID)
		AND (IDX.type != 200107 OR @csSPVersion < 20)
	
	UPDATE T SET
	T.LastBackupTime = MAXAF.cTime
	FROM #tmpDetails T
	CROSS APPLY
	(SELECT TOP 1 cTime FROM archFile AF WITH(NOLOCK)
	WHERE AF.cs_afname = CHECKSUM('IdxCheckPoint_' + CAST(T.backupSetGUID AS NVARCHAR(36)) + ':' + CAST(T.dbName AS NVARCHAR(36)))
	AND AF.name = 'IdxCheckPoint_' + CAST(T.backupSetGUID AS VARCHAR(36)) + ':' + CAST(T.dbName AS VARCHAR(36)) and af.isValid = 1 ORDER BY cTime DESC) MAXAF
	WHERE T.idxEngineType = 1
	SELECT 
		t.clientId AS '@ClientId', 
		REPLACE(t.Client, ',', '&comma&') AS '@Client',
		t.AppType AS '@AppType',
		REPLACE(t.Backupset, ',', '&comma&') AS '@Backupset',
		REPLACE(t.subclientName, ',', '&comma&') AS '@subclientName',
		t.dbName AS '@dbName',
		REPLACE(t.IndexServer, ',', '&comma&') AS '@IndexServer',
		t.uptodate AS '@uptodate',
		ISNULL(t.lastTimeDbUptodate,-1) AS '@lastTimeDbUptodate',
		ISNULL(t.lastPlayedJobTime,-1) AS '@lastPlayedJobTime',
		ISNULL(t.lastCompactionTime, 0) AS '@lastCompactionTime',
		ISNULL(t.errorlvl,-1) AS '@errorLvl',
		REPLACE(t.errordesc, ',', '&comma&') AS '@errorDesc',
		ISNULL(t.LastBackupTime,-1) AS '@LastBackupTime',
		ISNULL(t.LastBackupForDb,-1) AS '@LastBackupForDb',
		(CASE WHEN t.idxEngineType = 2 THEN 'Dynamic' ELSE 'Classic' END) AS '@IndexType',
		ISNULL(t.LastIdxErrorTime,-1) AS '@LastIdxErrorTime',
		ISNULL(t.FirstBackupForDb,-1) AS '@FirstBackupForDb',
		ISNULL(t.ScIdxConversionTime,-1) AS '@ScIdxConversionTime',
		t.DatabaseSize AS '@DatabaseSize',
		t.LogsSize AS '@LogsSize',
		t.ReportsSize AS '@ReportsSize',
		t.MaintenanceSize AS '@MaintenanceSize',
		t.IndexedItems AS '@IndexedItems',
		t.ApplicationSize AS '@ApplicationSize',
		t.LastIndexServer AS '@LastIndexServer',
		t.MigrationReason AS '@MigrationReason'
	FROM #tmpDetails t
END
ELSE
BEGIN
	RETURN
END
--------- END SURVEY QUERY ---------
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
SET QUOTED_IDENTIFIER ON
SET NOCOUNT OFF
--------- END - GENERATED CODE ---------
