--@querytype    CSV
--Name:- Cleanup Clients

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--------- BEGIN SURVEY QUERY ---------

-- This is used for getting Client,Agent and Backupset Information for Cleanup Health report.

DECLARE @csReleaseId INT
SELECT  @csReleaseId = releaseId FROM APP_Client WHERE id = 2

IF @csReleaseId < 16
    RETURN
    
DECLARE @csHighestSP INT
SELECT  @csHighestSP = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2

IF @csReleaseId = 16 AND @csHighestSP < 10
    RETURN

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

CREATE TABLE #tempCleanupClients (CId INT, AppId INT,AppTypeId INT,InstanceId INT,backupsetId INT,Flags INT )

CREATE TABLE #tblSubclient (AppId INT, ClientId INT, AppTypeId INT, InstanceId INT, BackupSetId INT, SubclientName NVARCHAR(128), SubclientStatus INT, Disabled INT)

INSERT	INTO #tblSubclient
SELECT	A.id, A.clientId, A.appTypeId, A.instance, A.backupSet, A.subclientName, A.subclientStatus, 0
FROM	APP_Application A
WHERE	A.appTypeId < 600 AND A.appTypeId NOT IN (72, 84, 85, 107, 121, 122, 127, 136)
	AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subclientStatus&8 = 0)
	AND A.subclientStatus&(2|4|16|32) = 0 

DECLARE @ThirtyDaysAgo INT = dbo.GetUnixTime(DATEADD(DAY, -30, GETUTCDATE()))

-- Backupsets with no backups in last 30 days
INSERT INTO #tempCleanupClients
SELECT	
		CL.Id , 
		A.AppId,
		IT.type , 
		INS.id,
		BS.id,
		1
FROM	(
		SELECT  DISTINCT AppId,ClientId, AppTypeId, InstanceId, BackupSetId FROM #tblSubclient WHERE (SubclientStatus&131072) = 0
		) A 
		INNER JOIN APP_Client CL ON A.ClientId = CL.id 
		INNER JOIN APP_iDAType IT ON A.AppTypeId = IT.type
		INNER JOIN APP_InstanceName INS ON A.InstanceId = INS.id
		INNER JOIN APP_BackupSetName BS ON A.BackupSetId = BS.id 
		LEFT OUTER JOIN
		(
		SELECT	DISTINCT S.ClientId, S.AppTypeId, S.InstanceId, S.BackupSetId
		FROM	#tblSubclient S
				INNER JOIN JMBkpStats B ON S.AppId = B.appId AND B.servStartDate >= @ThirtyDaysAgo
		) T ON A.ClientId = T.ClientId AND A.AppTypeId = T.AppTypeId AND A.InstanceId = T.InstanceId AND A.BackupSetId = T.BackupSetId
WHERE	T.BackupSetId IS NULL AND BS.status & 16 = 0


	-- Deleting Exchange clients which are part of a DAG and have no backups
	DELETE T
	FROM #tempCleanupClients T
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON CP.attrType = 125 AND CP.modified = 0
	INNER JOIN APP_Client AC WITH (NOLOCK) ON AC.net_hostname = CP.attrName AND CP.componentnameId = T.CId

IF object_id('tempdb.dbo.#EdgeBackupsetNoBackup') is not null DROP TABLE #EdgeBackupsetNoBackup
CREATE TABLE #EdgeBackupsetNoBackup(CId INT, AppId INT,AppTypeId INT,InstanceId INT,backupsetId INT)
-- Backupsets with no backups in last 30 days for EdgeDrive

INSERT INTO #EdgeBackupsetNoBackup
SELECT  CL.Id , 
		A.AppId,
		IT.type , 
		INS.id,
		BS.id
FROM	(
		SELECT  DISTINCT AppId ,ClientId, AppTypeId, InstanceId, BackupSetId FROM #tblSubclient WHERE (SubclientStatus&131072) > 0
		) A 
		INNER JOIN APP_Client CL ON A.ClientId = CL.id 
		INNER JOIN APP_iDAType IT ON A.AppTypeId = IT.type
		INNER JOIN APP_InstanceName INS ON A.InstanceId = INS.id
		INNER JOIN APP_BackupSetName BS ON A.BackupSetId = BS.id 
		LEFT OUTER JOIN
		(
		SELECT	DISTINCT S.ClientId, S.AppTypeId, S.InstanceId, S.BackupSetId
		FROM	#tblSubclient S
				INNER JOIN JMBkpStats B ON S.AppId = B.appId AND B.servStartDate >= @ThirtyDaysAgo
		) T ON A.ClientId = T.ClientId AND A.AppTypeId = T.AppTypeId AND A.InstanceId = T.InstanceId AND A.BackupSetId = T.BackupSetId
WHERE	T.BackupSetId IS NULL AND BS.status & 16 = 0

UPDATE T 
SET Flags = Flags | 2
from #tempCleanupClients T
INNER JOIN #EdgeBackupsetNoBackup E ON E.AppId = T.AppId AND E.CId = T.CId AND E.backupsetId = T.backupsetId AND E.InstanceId = T.InstanceId  AND E.APPTypeId = T.AppTypeId

INSERT INTO #tempCleanupClients
SELECT E.CId , E.AppId ,E.AppTypeId ,E.InstanceId,E.backupsetId,2
FROM #EdgeBackupsetNoBackup E
LEFT OUTER JOIN #tempCleanupClients T ON E.AppId = T.AppId AND E.CId = T.CId AND E.backupsetId = T.backupsetId AND E.InstanceId = T.InstanceId  AND E.APPTypeId = T.AppTypeId
where T.backupsetId IS NULL

DROP TABLE #EdgeBackupsetNoBackup


IF object_id('tempdb.dbo.#tempClientWithoutBackup') is not null DROP TABLE #tempClientWithoutBackup
CREATE TABLE #tempClientWithoutBackup (Cid Int)
-- Client with no backups in last 30 days
INSERT INTO #tempClientWithoutBackup
SELECT	CL.id
FROM	(
		SELECT	DISTINCT ClientId FROM #tblSubclient
		) A 
		INNER JOIN APP_Client CL ON A.ClientId = CL.id 
		LEFT OUTER JOIN
		(
		SELECT	DISTINCT S.ClientId
		FROM	#tblSubclient S
				INNER JOIN JMBkpStats B ON S.AppId = B.appId AND B.servStartDate >= @ThirtyDaysAgo
		) T ON A.ClientId = T.ClientId
WHERE	T.ClientId IS NULL

	-- Deleting Exchange clients which are part of a DAG and have no backups
	DELETE T
	FROM #tempClientWithoutBackup T
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON CP.attrType = 125 AND CP.modified = 0
	INNER JOIN APP_Client AC WITH (NOLOCK) ON AC.net_hostname = CP.attrName AND CP.componentnameId = T.CId

	--Deleting system created index server clients
	DELETE T
	FROM #tempClientWithoutBackup T
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON CP.attrName = 'Index Server Type' and attrVal In ('6','7','8','10','12','14','15') AND  T.Cid = CP.componentNameId -- Indexing client
	

	
UPDATE T 
SET Flags = Flags | 8
from #tempCleanupClients T
INNER JOIN #tempClientWithoutBackup E ON E.CId = T.CId 

INSERT INTO #tempCleanupClients (CId,Flags)
SELECT E.CId ,8
FROM #tempClientWithoutBackup E
LEFT OUTER JOIN #tempCleanupClients T ON  E.CId = T.CId  
where T.CId IS NULL

DROP TABLE #tempClientWithoutBackup


-- Clients with no package & no backups and not MediaAgents (not including VMs)

IF object_id('tempdb.dbo.#tempNoBackups') is not null DROP TABLE #tempNoBackups
CREATE TABLE #tempNoBackups(ClientId INT)

INSERT INTO #tempNoBackups
SELECT CL.id
FROM    APP_Client CL
            INNER JOIN (SELECT DISTINCT clientId FROM APP_Application WHERE subclientStatus&(2|4) = 0) IDA ON CL.id = IDA.clientId
        LEFT OUTER JOIN (
        SELECT DISTINCT clientId FROM APP_Application A INNER JOIN JMBkpStats B ON A.id = B.appId) P ON CL.id = P.clientId
            LEFT OUTER JOIN MMHost H ON CL.id = H.ClientId
WHERE P.clientId IS NULL
      AND H.ClientId IS NULL

	-- Deleting Exchange clients which are part of a DAG and have no backups
	DELETE T
	FROM #tempNoBackups T
	INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON CP.attrType = 125 AND CP.modified = 0
	INNER JOIN APP_Client AC WITH (NOLOCK) ON AC.net_hostname = CP.attrName AND CP.componentnameId = T.clientId

UPDATE T 
SET Flags = Flags | 16
from #tempCleanupClients T
INNER JOIN #tempNoBackups E ON E.ClientId = T.CId

INSERT INTO #tempCleanupClients (CId,Flags)
SELECT E.ClientId, 16
FROM #tempNoBackups E
LEFT OUTER JOIN #tempCleanupClients T ON E.ClientId = T.CId  
where T.CId IS NULL
 

-- Client with double backup (Flag = 32)
IF object_id('tempdb.dbo.#tempClientWithDoubleBackup') is not null DROP TABLE #tempClientWithDoubleBackup
CREATE TABLE #tempClientWithDoubleBackup (Cid Int,AppId INT , AppTypeId INT,JOBID INT)

IF object_id('tempdb.dbo.#tempExclude') is not null DROP TABLE #tempExclude
CREATE TABLE #tempExclude (CLientId Int, AppTypeId INT, AppId INT, InstanceId INT)

IF object_id('tempdb.dbo.#tempJobAction') is not null DROP TABLE #tempJobAction
CREATE TABLE #tempJobAction (clientId INT, appType INT, appId INT, clientGroupId INT, instanceID INT)

DECLARE @sqlStr NVARCHAR(256)
IF COL_LENGTH('JMJobAction', 'instanceID') IS NOT NULL
	SET @sqlStr = N'INSERT INTO #tempJobAction SELECT clientId, appType, appId, clientGroupId, instanceID FROM JMJobAction WITH (NOLOCK) WHERE opType = 4 AND action = 1'
ELSE
	SET @sqlStr = N'INSERT INTO #tempJobAction SELECT clientId, appType, appId, clientGroupId, 1 FROM JMJobAction WITH (NOLOCK) WHERE opType = 4 AND action = 1'

EXEC sp_executesql @sqlStr

-- Commcell level backup activity disbaled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, 0, 0, 0
FROM APP_Application App
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = 1 and JM.AppId = 1 AND JM.ClientId = 1 AND JM.ClientGroupId = 0 AND JM.AppType = 0)
-- Client Group level activity disabled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, 0, 0, 0
FROM APP_Application App
	INNER JOIN APP_ClientGroupAssoc CG ON App.clientId = CG.clientId
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = 1 and JM.AppId = 1 AND JM.ClientId = 1 AND JM.ClientGroupId > 0 AND JM.ClientGroupId = CG.ClientGroupId AND JM.AppType = 0)
	LEFT OUTER JOIN #tempExclude E ON App.ClientId = E.ClientId
	WHERE E.clientId IS NULL
-- Client level activity disabled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, 0, 0, 0
FROM APP_Application App
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = 1 and JM.AppId = 1 AND JM.ClientId = App.clientId AND JM.AppType = 0)
	LEFT OUTER JOIN #tempExclude E ON App.ClientId = E.ClientId
	WHERE E.clientId IS NULL
-- App Type level activity disabled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, App.AppTypeId, 0, 0
FROM APP_Application App
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = 1 and JM.AppId = 1 AND JM.ClientId = App.clientId AND JM.AppType = App.AppTypeId)
	LEFT OUTER JOIN #tempExclude E ON App.ClientId = E.ClientId AND App.AppTypeId = E.AppTypeId
	WHERE E.clientId IS NULL
-- SubClient level activity disabled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, App.AppTypeId, App.Id, 0
FROM APP_Application App
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = 1 and JM.AppId = App.Id AND JM.ClientId = 1 AND JM.AppType =0)
	LEFT OUTER JOIN #tempExclude E ON App.ClientId = E.ClientId
	WHERE E.clientId IS NULL
-- Instance level activity disabled
INSERT INTO #tempExclude
SELECT DISTINCT App.clientId, App.AppTypeId, 0, App.Instance
FROM APP_Application App
	INNER JOIN #tempJobAction JM ON (JM.InstanceId = App.Instance and JM.AppId = 1 AND JM.ClientId = 1 AND JM.ClientGroupId = 0 AND JM.AppType =0)
	LEFT OUTER JOIN #tempExclude E ON App.ClientId = E.ClientId
	WHERE E.clientId IS NULL

INSERT INTO #tempClientWithDoubleBackup
select DISTINCT
App.clientId,LIB.AppId,JMbkp.appType,MAX(LIB.JobId) 
from JMBkpStats JMbkp 
	INNER JOIN APP_Application App ON App.id = JMbkp.appId
	 INNER JOIN (SELECT DISTINCT Q.clientId,J.appId,MAX(Q.jobId) AS 'JobId'
    FROM    JMQinetixUpdateStatus Q 
	INNER JOIN JMBkpStats J ON J.jobId = Q.jobId
	where Q.status in (0,3) AND J.servEndDate > @ThirtyDaysAgo
	GROUP BY Q.clientId,J.appId) LIB ON LIB.clientId = App.clientId 
	LEFT OUTER JOIN #tempExclude E ON E.ClientId = App.clientId AND E.AppTypeId IN (0, App.AppTypeId) 
	AND E.AppId IN (0, LIB.AppId) AND E.InstanceId IN (0, App.Instance)
		WHERE E.clientId IS NULL
	GROUP BY App.clientId,LIB.AppId,JMbkp.appType HAVING JMbkp.appType <> 106
	order by clientId 




UPDATE T 
SET Flags = Flags | 32 
from #tempCleanupClients T
INNER JOIN #tempClientWithDoubleBackup E ON E.CId = T.CId AND E.APPTypeId = T.AppTypeId AND E.AppId = T.AppId

INSERT INTO #tempCleanupClients (CId,AppTypeId,AppId,Flags)
SELECT E.CId ,E.AppTypeId,E.AppId ,32
FROM #tempClientWithDoubleBackup E
LEFT OUTER JOIN #tempCleanupClients T ON  E.CId = T.CId  AND E.APPTypeId = T.AppTypeId AND E.AppId = T.AppId
where T.APPTypeId IS NULL OR T.CId IS NULL OR T.AppId IS NULL

-------remove entries with activities disabled at client and client group level.
DELETE  T
FROM #tempCleanupClients T
INNER JOIN #tempExclude E
ON T.Cid= E.clientId 
AND E.AppTypeId = 0
AND E.appID = 0
AND E.InstanceId = 0  
AND T.FLAGs & 8 >0 -- for cleanup clients

DROP TABLE #tempExclude

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

SELECT  CId AS [ClientId],
		ISNULL(AppId, -1) AS [AppId],
		ISNULL(AppTypeId, -1)  AS [AppType],
		ISNULL(InstanceId, -1) AS [InstanceId],
		ISNULL(backupsetId, -1) AS [BackupSetId],
		Flags AS [Flags]
 FROM   #tempCleanupClients

DROP TABLE #tempNoBackups
DROP TABLE #tblSubclient
DROP TABLE #tempCleanupClients

--------- END SURVEY QUERY ---------

SET NOCOUNT OFF