

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

-- ----------------------------------------------------------------------
--
--           Copyright (c) 2018  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.
-- ----------------------------------------------------------------------*/
--  +===================================================================+
--  |	GetAdminConsoleExchangeClients									|
--  |   Procedure to get exchange clients based on backupset types		|
--  +===================================================================+
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/GetAdminConsoleExchangeClients.sp,v $ $Id: GetAdminConsoleExchangeClients.sp,v 1.1.2.26.6.1 2021/06/16 23:29:08 nuppu Exp $"
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='GetAdminConsoleExchangeClients')
	delete from GXDBVersions where aliasname = 'GetAdminConsoleExchangeClients'
GO
print '... Creating Procedure: GetAdminConsoleExchangeClients'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure GetAdminConsoleExchangeClients
-----------------------------------------------------------
---    PARAMETERS   &   OUTPUTS                         ---
  @inOutXmlReq XML OUTPUT
-----------------------------------------------------------
AS
SET NOCOUNT ON
BEGIN
    DECLARE @errorCode              INT = 0
    DECLARE @errorString            NVARCHAR(1024) = ''
    DECLARE @i_userId				INT = 0
  	DECLARE @i_subclientId			INT = 0
	DECLARE @BACKUPSET_TYPE_NAME	VARCHAR(40) = 'Exchange OnePass BackupSet Type'
	DECLARE @SEPARATOR				CHAR = ','
	DECLARE @ReturnAllClients		BIT = 0
	--Client types
	DECLARE @EMAIL_ARCHIVING		INT = 1
	DECLARE @EMAIL_JOURNALING		INT = 2
	DECLARE @CONTENSTORE_JOURNALING	INT = 3
	DECLARE @DATABASE				INT = 5
	--Backupset types
	DECLARE @USER_MAILBOX			INT = 1
	DECLARE @JOURNAL_MAILBOX		INT = 2
	DECLARE @SMTP_MAILBOX			INT = 3
	-- Exchange environment types
	DECLARE @ON_PREMISES			INT = 1
	DECLARE @HYBRID					INT = 2
	DECLARE @EXCHNAGE_ONLINE		INT = 4
	DECLARE @DB_PROP				INT = 112		/*PROPERTY_EXCHANGE_DB enum in ApplProperty.h */
	DECLARE @FilterClientTypesRequested	TABLE (backupsetTypes INT)
	IF OBJECT_ID('tempdb.dbo.#tmpExchangeClientList') IS NOT NULL
		DROP TABLE #tmpExchangeClientList
CREATE TABLE #tmpExchangeClientList ( backupsetId INT , backupsetName NVARCHAR(128), clientType INT,
clientId INT , clientName NVARCHAR(255), clientGuid UNIQUEIDENTIFIER, agentId INT, subclientId INT, subclientName NVARCHAR(128), applicationId INT,
planId INT, planName nvarchar(128), appBackupSize BIGINT, numberOfItems BIGINT, lastArchiveJobRanTime INT, isDeletedClient INT DEFAULT 0, emailCount BIGINT DEFAULT 0 , ciCount BIGINT DEFAULT 0 , idxCollectionTime INT DEFAULT 0, lastBkpJobInfo XML DEFAULT '', slaStatus INT)
	CREATE CLUSTERED INDEX #tmpExchangeClientList_backupsetId_clientId_agentId_SubclientId_Idx1 ON #tmpExchangeClientList (backupsetId, clientId, agentId, subclientId, applicationId)
	DECLARE	@ReqClientTypesList		TABLE (bkType INT)
	-- Parse input
	SELECT @i_userId = ISNULL(ref.value('(processinginstructioninfo/user/@userId)[1]', 'int'), @i_userId),
		 @i_subclientId = ISNULL(ref.value('(subclient/@subclientId)[1]', 'INT'),0)
	FROM @inOutXmlReq.nodes('App_GetAdminConsoleExchangeClientsReq') R(ref)
	INSERT INTO @ReqClientTypesList
	SELECT R.ref.value('(.)', 'INT') from @inOutXmlReq.nodes('App_GetAdminConsoleExchangeClientsReq/filterTypes') R(ref)
	IF NOT EXISTS (select bkType from @ReqClientTypesList)
		SET @ReturnAllClients = 1
	--Security
	IF OBJECT_ID('tempdb.dbo.#getIdaObjects') IS NOT NULL	DROP TABLE #getIdaObjects
	CREATE TABLE #getIdaObjects (clientId INT, apptypeId INT, instanceID INT, backupsetId INT, subclientID INT)
EXEC sec_getIdaObjectsForUser @i_userId, 7, 0 , 0,'#getIdaObjects', 0, '2, 20, 22, 13, 141, 25, 12, 253, 1, 16, 107, 31'
	BEGIN TRY
	--Populate Exchange Mailbox and DAG clients
	INSERT INTO #tmpExchangeClientList ( backupsetId, backupsetName, clientType, clientId, clientName, clientGuid, agentId, subclientId, subclientName, applicationId)
		SELECT DISTINCT BK.id as backupsetId, BK.name as backupsetName,
CASE WHEN SC.appTypeId = 137 THEN @EMAIL_ARCHIVING
WHEN SC.appTypeId = 53 THEN @DATABASE
		END as clientType,
		C.id as clientId, C.displayName as clientName, C.GUID as clientGuid, A.id as agentId,
(CASE WHEN SC.appTypeId = 53 THEN -1 ELSE SC.id END )as subclientId,	/*Return 1 entry per database backupset*/
(CASE WHEN SC.appTypeId = 53 THEN '' ELSE SC.id END )as subclientName, sc.appTypeId
		FROM APP_Application (NOLOCK) SC
		INNER JOIN APP_Client(NOLOCK) C ON C.id = SC.clientId
		INNER JOIN APP_BackupSetName(NOLOCK) BK ON SC.backupSet = BK.id
		INNER JOIN APP_IDAName(NOLOCK) A ON C.id = A.clientId AND SC.appTypeId = A.appTypeId
		INNER JOIN #getIdaObjects sec ON sec.subclientId = SC.id
		WHERE
		((@i_subclientId > 0 AND SC.id = @i_subclientId) OR @i_subclientId = 0)		--Filter by subclient if given in request
		AND
(SC.subclientStatus & 0x00004 != 0x00004)
AND ( (SC.appTypeId = 137) --Mailbox
OR (	SC.appTypeId = 53
AND (C.status & 0x200000 = 0x200000)
					)) -- DAG
	--Filter Case manager clients, GDPR clients	and Office 365 Clients
	DELETE T FROM #tmpExchangeClientList T
	JOIN APP_ClientProp (NOLOCK) CP ON CP.componentNameId = T.clientId
	WHERE (CP.attrName = 'Office 365 Pseudo Client' AND CP.attrVal = '1' ) OR (CP.attrName = 'EDiscovery Subtype' AND CP.attrVal IN ('1', '2')) OR (CP.attrname = 'Case Manager Pseudo Client' AND CP.attrVal = '1')
	--Filter all exchange online clients (created upto sp20 using exchange solution)
	DELETE T FROM #tmpExchangeClientList T
	JOIN APP_IDAProp(NOLOCK) IDP ON T.agentId = IDP.componentNameId
	JOIN APP_BackupSetPROP (NOLOCK) BP ON BP.componentNameId = T.backupsetId
WHERE T.applicationId = 137 AND BP.attrName = 'Exchange OnePass BackupSet Type' AND BP.attrVal = '1'/*userMailbox*/ AND IDP.attrName = 'Exchange Environment Type' AND IDP.attrVal = 4/*Exchange Online*/
	--Update client released licensed status
	Update #tmpExchangeClientList
	SET isDeletedClient = ISNULL(CS.attrVal, 0)
	FROM (
		select clientId, attrVal FROM #tmpExchangeClientList T
		JOIN App_ClientProp (NOLOCK) CP On T.clientId = CP.componentNameId
		WHERE AttrName ='PlatformDeleted 4'
	) CS WHERE #tmpExchangeClientList.ClientId = CS.clientId
	--Update plan id and name
	UPDATE #tmpExchangeClientList
	SET planId = tmp.planId,
		planName = tmp.planName
	FROM(
		SELECT Sp.attrVal AS planId, Ap.name AS planName, T.backupsetId FROM #tmpExchangeClientList T
		INNER JOIN APP_Application (NOLOCK) SC ON T.backupsetId = SC.backupSet
		LEFT JOIN APP_SubClientProp (NOLOCK) SP ON SC.Id = SP.componentNameId
		LEFT JOIN APP_Plan (NOLOCK) AP  ON AP.id = SP.attrVal
WHERE SP.attrName = 'Associated Plan' AND SP.modified = 0
	)tmp WHERE tmp.backupsetId = #tmpExchangeClientList.backupsetId
	--Update Exchange mailbox type
	UPDATE #tmpExchangeClientList SET clientType = T.clientType
		FROM (
			SELECT
			backupsetId,
			CASE WHEN BKP.attrVal = @USER_MAILBOX THEN @EMAIL_ARCHIVING
			WHEN BKP.attrVal = @JOURNAL_MAILBOX THEN @EMAIL_JOURNALING
			WHEN BKP.attrVal = @SMTP_MAILBOX THEN @CONTENSTORE_JOURNALING
			ELSE @EMAIL_ARCHIVING END AS clientType
			FROM #tmpExchangeClientList T
--INNER JOIN APP_IDAPROP (NOLOCK) AP ON T.agentId = AP.componentNameId AND AP.attrName =  'Exchange Environment Type'
			INNER JOIN APP_BackupSetProp(NOLOCK) BKP ON T.backupsetId = BKP.componentNameId AND BKP.attrName = @BACKUPSET_TYPE_NAME
		) T where #tmpExchangeClientList.backupsetId = T.backupsetId
	-- Update Exchange mailbox count for usermailbox, journal mailbox and contentstore mailbox
	UPDATE #tmpExchangeClientList
	SET numberOfItems = tCL.itemCount FROM
	(SELECT count(E.assocId) as itemCount, E.subclientId FROM #tmpExchangeClientList CL
INNER JOIN APP_EmailConfigPolicyAssoc(NOLOCK) E ON CL.subclientId = E.subClientId AND CL.applicationId = 137 /*exchange mailbox */ AND E.modified = 0
			WHERE mailBoxType IN (@USER_MAILBOX, @JOURNAL_MAILBOX, @SMTP_MAILBOX) GROUP BY E.subClientId) tCL
	WHERE tCL.subclientId = #tmpExchangeClientList.subclientId
	--Update Exchange mailbox backup size
	--update EmailCount, ciCount
		UPDATE #tmpExchangeClientList
		SET emailCount = ISNULL((SELECT R.ref.value('@itemsIndexed', 'BIGINT') from Idx.prop.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0),
		ciCount = ISNULL((SELECT R.ref.value('@itemsContentIndexed', 'BIGINT') from Idx.prop.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0),
		appBackupSize = ISNULL((SELECT R.ref.value('@applicationSize', 'BIGINT') from Idx.prop.nodes('(/Indexing_DbStats/apps/stat)[1]') R(ref)), 0),
	idxCollectionTime = ISNULL((SELECT R.ref.value('@lastPlayedBkpJobTime', 'BIGINT') from Idx.prop.nodes('(/Indexing_DbStats/apps)[1]') R(ref)), 0)
		FROM
		(
			select I.properties as 'prop', AI.backupsetId as 'bkSetId' From IdxDbState (nolock) I
			INNER JOIN App_IndexDBInfo  (nolock) AI ON AI.Id = I.dbId AND AI.type = 137
		) Idx
		where backupsetId = Idx.bkSetId
	UPDATE #tmpExchangeClientList
	SET lastArchiveJobRanTime = tSize.lastJobtime
	FROM
	(SELECT max(BKS.servEndDate) as lastJobtime, CL.backupsetId
		FROM #tmpExchangeClientList CL
		INNER JOIN APP_Application (nolock) A ON A.backupSet = CL.backupsetId
INNER JOIN JMBkpStats(nolock) BKS ON A.id = BKS.appId WHERE BKS.AppType IN (137, 53)
		AND BKS.status IN  (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/) AND BKS.opType = 4 /*Backup*/
AND (A.subclientStatus & 0x00002 != 0x00002)
AND (A.subclientStatus & 0x00004 != 0x00004)
	GROUP BY CL.backupsetId) tSize
	WHERE tSize.backupsetId = #tmpExchangeClientList.backupsetId
	-------------- DAG clients-----------------------------
	-- Update Exchange databases count protected for DAG clients
	UPDATE #tmpExchangeClientList
	SET numberOfItems = tCL.itemCount FROM
	(SELECT count(E.id) as itemCount, CL.backupsetId
		FROM #tmpExchangeClientList CL
		INNER JOIN APP_Application AP (NOLOCK) ON AP.backupSet = CL.backupsetId
LEFT JOIN App_SubClientProp (NOLOCK) E ON AP.id = E.componentNameId AND CL.applicationId = 53
			WHERE E.modified = 0 AND E.attrType = @DB_PROP /* PROPERTY_EXCHANGE_DB IN ApplProperty.h*/
			GROUP BY CL.backupsetId) tCL
	WHERE tCL.backupsetId = #tmpExchangeClientList.backupsetId
	--Update Exchange database backup size
	--count the total application size on all subclients based on last Full / Synth Full ran size per backupset
	UPDATE #tmpExchangeClientList
	SET appBackupSize = tSize.size
	FROM
	(
	SELECT SUM(totalUncompBytes) as size, backupSet as backupsetId
		FROM (
		SELECT ROW_NUMBER() OVER( PARTITION BY BKS.AppId ORDER BY (BKS.servEndDate) DESC) as ROWNUMBER,
		BKS.appId, BKS.totalUncompBytes
		FROM JMBkpStats(nolock) BKS
WHERE BKS.AppType=53 AND bkpLevel IN (1/*FULL*/,64/*SYNTH_FULL*/)
		AND BKS.status IN  (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/)
		)TMP
		INNER JOIN APP_Application AP( NoLOCK) ON AP.id = TMP.appId
		INNER JOIN #tmpExchangeClientList CL ON CL.backupsetId = AP.backupSet
		WHERE TMP.ROWNUMBER = 1 GROUP BY AP.backupSet
	)tSize
	WHERE tSize.backupSetId = #tmpExchangeClientList.backupSetId
	Update #tmpExchangeClientList
	SET lastBkpJobInfo = IP.attrVal
	FROM App_Idaprop IP (NOLOCK)
	JOIN APP_IdaName I (NOLOCK) ON I.id = IP.componentNameId
	JOIN #tmpExchangeClientList T ON T.clientId = I.clientId AND T.agentid = I.id
WHERE I.appTypeId IN ( 137, 53 ) AND backupsetId = T.backupsetId AND agentid = I.id AND  IP.attrname =  'Last Backup Job Info' AND IP.modified = 0
	-- get commcell name and guid for comet
DECLARE @commCellName NVARCHAR(256)
DECLARE @csGuid NVARCHAR(40)
SELECT  @commCellName = aliasName, @csGuid = csGUID FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
	--Read into XML
	SET @inOutXmlReq = (SELECT ( SELECT clientType as '@type', isDeletedClient as '@isDeletedClient',
								( SELECT backupsetId as '@backupsetId', backupsetName as '@backupsetName',
									clientId as '@clientId', clientName as '@clientName',  clientGuid as '@clientGUID',
									agentId  as '@agentId', subclientId as '@subclientId', applicationId as '@applicationId',
									subclientName as '@subclientName' FOR XML PATH ('subclient'), TYPE),
									(SELECT planId as '@planId', planName as '@planName' FOR XML PATH('plan'), TYPE), /*Populate plan entity */
									(SELECT s.n.query('.') FROM T.lastBkpJobInfo.nodes('lastBackupJobInfo') s(n) ),
									(SELECT appBackupSize as '@appArchiveSize', numberOfItems as '@numberOfItems', emailCount as '@emailCount', ciCount as '@ciCount', idxCollectionTime as '@idxcollectiontime',
									(SELECT lastArchiveJobRanTime as '@time' FOR XML PATH ('lastArchiveJobRanTime'), TYPE)
										FOR XML PATH('clientStats'), TYPE),
(SELECT  @commCellName AS '@commCellName', @csGuid AS '@csGUID', 1 '@_type_' FOR XML PATH('commCell'), TYPE)
							   FROM #tmpExchangeClientList T WHERE (@ReturnAllClients = 1) OR (T.clientType IN (SELECT bkType From @ReqClientTypesList))
							   ORDER BY clientType FOR XML PATH ('exchangeClientList'), Type )
						FOR XML PATH('App_AdminConsoleExchangeClientsResp'))
	END TRY
    BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
        SELECT @errorCode = ERROR_NUMBER(), @errorString = ERROR_MESSAGE()
    END CATCH
ERROR_EXIT:
    IF @errorCode <> 0
        SET @inOutXmlReq = (SELECT ISNULL(@errorCode,0) AS '@errorCode', ISNULL(@errorString,'')
								AS '@errorMessage' FOR XML PATH('App_AdminConsoleExchangeClientsResp'))
	IF OBJECT_ID('tempdb.dbo.#tmpExchangeClientList') IS NOT NULL
		DROP TABLE #tmpExchangeClientList
	SELECT @inOutXmlReq
END
SET NOCOUNT OFF

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

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

insert into GXDBVersions values(2, 'GetAdminConsoleExchangeClients',  'v1.1.2.26.6.1', 'GetAdminConsoleExchangeClients', 'v1.1.2.26.6.1')
GO

