

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/GetOffice365PseudoClients.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.
-- ----------------------------------------------------------------------*/
--  +===================================================================+
--  |	GetOffice365PseudoClients									|
--  |   Procedure to get Office365 Pseudo Clients based on backupset types		|
--  +===================================================================+
-- rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/GetOffice365PseudoClients.sp,v $ $Id: GetOffice365PseudoClients.sp,v 1.1.2.40.4.10 2021/06/16 23:29:06 nuppu Exp $"
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='GetOffice365PseudoClients')
	delete from GXDBVersions where aliasname = 'GetOffice365PseudoClients'
GO
print '... Creating Procedure: GetOffice365PseudoClients'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure GetOffice365PseudoClients
-----------------------------------------------------------
---    PARAMETERS   &   OUTPUTS                         ---
  @inOutXmlReq XML OUTPUT
-----------------------------------------------------------
AS
SET NOCOUNT ON
BEGIN
    DECLARE @errorCode              INT = 0
    DECLARE @errorString            NVARCHAR(1024) = ''
	 --O365 Agent types
	DECLARE @EXCHANGE_ONLINE           INT = 1
	DECLARE @SHAREPOINT_ONLINE         INT = 2
	DECLARE @ONEDRIVE					INT = 3
	DECLARE @ALL_AGENTS				INT = 4
	DECLARE @D365					INT = 5
	DECLARE @TEAMS					INT = 6
	DECLARE @OFFICE_365             INT = 7 -- Office365 soultion excludes D365
	DECLARE @ReturnAllAgents		BIT = 0
	DECLARE @i_userId				INT = 0
	DECLARE @V2CloudInstance		INT = 0
	DECLARE @ONEDRIVE_Instance		INT = 7
	--V2 CloudApps Instance Types
	DECLARE @D365_Instance INT = 35
	DECLARE @TEAMS_Instance INT = 36
	--Discover By Type
	DECLARE @DISCOVERTYPE_USER													INT = 1
	DECLARE @DISCOVERTYPE_ASSOCIATED_SHAREPOINT_WEB_SITES						INT = 6
	DECLARE @DISCOVERTYPE_TEAM													INT = 12
	DECLARE @DISCOVERTYE_DYNAMICS_365_ENTITY							INT = 14
	IF OBJECT_ID('tempdb.dbo.#tmpOffice365ClientList') IS NOT NULL
		DROP TABLE #tmpOffice365ClientList
CREATE TABLE #tmpOffice365ClientList ( backupsetId INT , backupsetName NVARCHAR(128), agentType INT,
clientId INT , clientName NVARCHAR(255), displayName NVARCHAR(255), clientGuid UNIQUEIDENTIFIER, subclientId INT, subclientName NVARCHAR(128), clientType INT,
planId INT, planName nvarchar(128), appBackupSize BIGINT, numberOfItems BIGINT, lastArchiveJobRanTime INT, emailCount BIGINT, ciCount BIGINT, isDeletedClient INT DEFAULT 0, idxCollectionTime INT DEFAULT 0, orgName NVARCHAR(128) DEFAULT '', lastBkpJobInfo XML, slaStatus INT)
	CREATE CLUSTERED INDEX #tmpExchangeClientList_backupsetId_clientId_SubclientId_Idx1 ON #tmpOffice365ClientList (backupsetId, clientId, subclientId)
	DECLARE	@bkType INT
	SET @bkType = isnull((select @inOutXmlReq.value('(App_GetOffice365PseudoClientsReq/agentType/@val)[1]', 'int')), 0)
	IF @bkType IN ( 0, @ALL_AGENTS, @OFFICE_365  )
		SET @ReturnAllAgents = 1
	SET @i_userId = isnull((select @inOutXmlReq.value('(App_GetOffice365PseudoClientsReq/processinginstructioninfo/user/@userId)[1]', 'int')), 0)
	--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'
	DECLARE @CountOnly		INT = 0
	DECLARE @Count_O365Clients INT = 0
	SET @CountOnly = isnull((select @inOutXmlReq.value('(App_GetOffice365PseudoClientsReq/@countO365Clients)[1]', 'int')), 0)
	IF @CountOnly = 1
	BEGIN
		DECLARE @D365Count INT = 0
		SELECT @D365Count =  ISNULL(count(distinct cp.componentNameId), 0) from APP_ClientProp cp (NOLOCK)
		INNER JOIN APP_Application SC (NOLOCK) ON SC.clientid = cp.componentNameId
		INNER JOIN APP_InstanceProp IP (NOLOCK) ON IP.componentNameId = SC.instance
		INNER JOIN #getIdaObjects sec ON  sec.instanceID = SC.instance
		 WHERE  cp.attrname = 'Office 365 Pseudo Client' and cp.attrval = '1' AND cp.modified = 0 AND IP.attrName = 'Cloud Apps Instance Type' AND IP.modified = 0 AND  IP.attrVal = @D365_Instance
		IF @bkType = @D365
		BEGIN
			SET @inOutXmlReq = (SELECT @D365Count as '@o365Count'
			FOR XML PATH('App_Office365PseudoClientsResp'))
			GOTO ERROR_EXIT
		END
		SELECT @Count_O365Clients =  count(distinct cp.componentNameId) from APP_ClientProp cp (NOLOCK)
		INNER JOIN APP_Application SC (NOLOCK) ON SC.clientid = cp.componentNameId
		INNER JOIN #getIdaObjects sec ON sec.subclientId = SC.Id
		 WHERE  cp.attrname = 'Office 365 Pseudo Client' and cp.attrval = '1' AND cp.modified = 0
		SET @inOutXmlReq = (SELECT @Count_O365Clients - @D365Count as '@o365Count'
		FOR XML PATH('App_Office365PseudoClientsResp'))
		GOTO ERROR_EXIT
	END
	BEGIN TRY
	--Populate Exchange O365 backupsets
	IF @bkType IN (@EXCHANGE_ONLINE, @ALL_AGENTS, @OFFICE_365 )
	BEGIN
	INSERT INTO #tmpOffice365ClientList ( backupsetId, backupsetName, agentType, clientId, clientName, displayName, clientGuid, subclientId, subclientName, clientType)
		SELECT BK.id as backupsetId, BK.name as backupsetName,  @EXCHANGE_ONLINE as agentType,
		C.id as clientId, C.name as clientName, C.displayName as displayName, C.GUID as clientGuid, SC.id as subclientId, SC.subclientName as subclientName,0
		FROM APP_Application 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
		INNER JOIN APP_IDAProp (NOLOCK) AP ON AP.componentNameId = A.id  AND SC.appTypeId = A.appTypeId
		INNER JOIN APP_BackupSetPROP (NOLOCK) BP ON BP.componentNameId = SC.backupSet AND BP.attrName= 'Exchange OnePass BackupSet Type' AND BP.attrVal = 1
		INNER JOIN #getIdaObjects sec ON sec.subclientId = SC.Id
WHERE (SC.subclientStatus & 0x00004 != 0x00004)
AND SC.appTypeId = 137 AND AP.attrName = 'Exchange Environment Type' and AP.attrval = '4'
	END
--Populate Sharepoint O365 backupsets
	IF @bkType IN (@SHAREPOINT_ONLINE , @ALL_AGENTS, @OFFICE_365 )
	BEGIN
	INSERT INTO #tmpOffice365ClientList ( backupsetId, backupsetName, agentType, clientId, clientName, displayName, clientGuid, subclientId, subclientName, clientType)
        SELECT BK.id as backupsetId, BK.name as backupsetName, @SHAREPOINT_ONLINE as agentType,
        C.id as clientId, C.name as clientName, C.displayName as displayName, C.GUID as clientGuid, SC.id as subclientId, SC.subclientName as subclientName,0
        FROM APP_Application SC  JOIN #getIdaObjects sec ON sec.subclientId = SC.Id
        INNER JOIN APP_Client(NOLOCK) C ON C.id = SC.clientId
        INNER JOIN APP_BackupSetName(NOLOCK) BK ON SC.backupSet = BK.id
WHERE (SC.subclientStatus & 0x00004 != 0x00004) AND	SC.appTypeId = 78 AND (BK.status & 0x80000 = 0x80000) AND (SC.subclientStatus & 0x00008  = 0x00008)
	END
		--Populate Onedrive instances
	IF @bkType IN (@ONEDRIVE , @ALL_AGENTS, @OFFICE_365 )
	BEGIN
		INSERT INTO #tmpOffice365ClientList ( backupsetId, backupsetName, agentType, clientId, clientName, displayName, clientGuid,  subclientId, subclientName, clientType)
		SELECT BK.id as backupsetId, BK.name as backupsetName,	@ONEDRIVE as agentType, C.id as clientId, C.name as clientName, C.displayName as displayName, C.GUID as clientGuid,
		SC.id as subclientId, SC.subclientName as subclientName, 0
		FROM APP_Application 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_InstanceProp (NOLOCK) I ON SC.instance = I.componentNameId AND  I.attrName = 'Cloud Apps Instance Type'  AND I.attrval = 7 AND I.modified = 0
		INNER JOIN #getIdaObjects sec ON sec.subclientId = SC.Id
WHERE (SC.subclientStatus & 0x00004 != 0x00004) AND  SC.appTypeId = 134  AND (SC.subclientStatus & 0x00020 = 0) AND (SC.subclientStatus & 0x00008  = 0x00008)
	END
		--Populate V2CloudApps instances like D365, TEAMS etc..
	IF @bkType IN (@D365 , @TEAMS, @ALL_AGENTS, @OFFICE_365 )
	BEGIN
		SET @V2CloudInstance = (CASE when @bkType = @D365 THEN @D365_Instance WHEN @bkType = @TEAMS THEN @TEAMS_Instance  ELSE 0 END)
		INSERT INTO #tmpOffice365ClientList ( backupsetId, backupsetName, agentType, clientId, clientName, displayName, clientGuid,  subclientId, subclientName, clientType)
		SELECT BK.id as backupsetId, BK.name as backupsetName,	CASE I.attrval WHEN @D365_Instance THEN @D365 WHEN @TEAMS_Instance THEN @TEAMS END  as agentType, C.id as clientId, C.name as clientName, C.displayName as displayName, C.GUID as clientGuid,
		SC.id as subclientId, SC.subclientName as subclientName, 0
		FROM APP_Application 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_InstanceProp (NOLOCK) I ON SC.instance = I.componentNameId
		INNER JOIN #getIdaObjects sec ON sec.subclientId = SC.Id
		WHERE 	 I.attrName = 'Cloud Apps Instance Type'  AND ((@V2CloudInstance > 0 AND I.attrval = @V2CloudInstance ) OR ( (@V2CloudInstance = 0) AND I.attrval IN (@D365_Instance , @TEAMS_Instance))) AND I.modified = 0
AND (SC.subclientStatus & 0x00004 != 0x00004) AND  SC.appTypeId = 134  AND (SC.subclientStatus & 0x00020 = 0) AND (SC.subclientStatus & 0x00008  = 0x00008)
	END
	--Office 365 Solution excludes D365 App
    IF @bkType = @OFFICE_365
    BEGIN
        DELETE FROM #tmpOffice365ClientList WHERE agentType = @D365
    END
		--update office 365 Pseudo client type
		update #tmpOffice365ClientList set clientType = (CASE WHEN EXISTS
		(select attrval from APP_ClientProp (NOLOCK)	WHERE componentNameId = clientId AND attrname = 'Office 365 Pseudo Client' and attrval = 1 AND modified = 0) THEN 1
		 ELSE 0 END)
		--Update client released license status
		Update #tmpOffice365ClientList
		SET isDeletedClient = ISNULL(CS.attrVal, 0)
		FROM (
			select clientId, attrVal FROM #tmpOffice365ClientList T
			JOIN App_ClientProp (NOLOCK) CP On T.clientId = CP.componentNameId
			WHERE AttrName ='PlatformDeleted 4'
		) CS WHERE #tmpOffice365ClientList.clientId = CS.clientId
		------------For Office365 mailbox client----------------
		--Update plan id and name
		UPDATE #tmpOffice365ClientList
		SET planId =tmp.planId,
			planName = tmp.planName
		FROM(
			SELECT Sp.attrVal AS planId, Ap.name AS planName, T.backupsetId
			FROM #tmpOffice365ClientList 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 = #tmpOffice365ClientList.backupsetId
		-- Update Exchange mailbox count for usermailbox
		UPDATE #tmpOffice365ClientList
		SET numberOfItems =   tCL.itemCount FROM
		(SELECT count(E.assocId) as itemCount, E.backupsetId FROM #tmpOffice365ClientList CL
			INNER JOIN APP_EmailConfigPolicyAssoc(NOLOCK) E ON CL.backupsetId = E.backupsetId AND E.modified = 0
			WHERE mailBoxType = 1  GROUP BY E.backupsetId) tCL
		WHERE tCL.backupsetId = #tmpOffice365ClientList.backupsetId	 AND agentType = 1
		--update Emails count and applicationSize
		UPDATE #tmpOffice365ClientList
		SET emailCount = ISNULL((SELECT R.ref.value('@itemsIndexed', '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 AND agentType = 1
		--get unique list of backupsets
		DECLARE @bklist table(bkId int)
		INSERT INTO @bklist select DISTINCT backupsetId FROM #tmpOffice365ClientList
		--update last Job ran time for Exch\OneDrive\Sp\D365\Teams
		UPDATE #tmpOffice365ClientList
		SET lastArchiveJobRanTime =  ISNULL(( SELECT JT.lastJobTime
		FROM
		(SELECT max(J.servendDate) as lastJobTime , BK.bkId as bkpSetId FROM  @bklist BK
JOIN APP_Application (nolock) A ON A.backupSet = BK.bkId AND A.appTypeId in (137, 134, 78)
		JOIN JMBkpStats (nolock) J ON J.appId = A.id AND J.status in (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/) AND J.opType = 4/*Backup*/
		group by BK.bkId ) JT
		WHERE backupsetId = JT.bkpSetId), 0)
		------------(END)For Office365 mailbox client----------------
		 --Sharepoint V1 Number of items
		 UPDATE #tmpOffice365ClientList
		SET numberOfItems = ISNULL(Tmp.countmb, 0)
		FROM (
			SELECT count(distinct P.attrName) as countmb , backupsetId as bksetid
			FROM #tmpOffice365ClientList C
INNER JOIN APP_Application (NOLOCK)  APP ON App.backupSet = C.backupsetId AND APP.appTypeId = 78
			INNER JOIN APP_BackupSetProp (NOLOCK) P ON P.componentNameId = App.backupSet AND P.attrType = 137 AND P.modified = 0
			WHERE C.clientid = APP.clientId AND C.agentType = @SHAREPOINT_ONLINE
AND (APP.subclientStatus & 0x00004) != 0x00004
AND (APP.subclientStatus & 0x00002) != 0x00002
AND (APP.subclientStatus & 0x00020) != 0x00020
			GROUP BY backupsetId
		) Tmp  WHERE bksetid = backupsetId AND agentType = @SHAREPOINT_ONLINE AND clientType = 0 /*V1*/
		 --OneDrive V1 clients: Number of Items
		 UPDATE #tmpOffice365ClientList
		SET numberOfItems =  ISNULL(Tmp1.countmb, 0)
		FROM
		(
			select count(distinct SP.attrName) as countmb , backupsetId as bksetid
			 FROM APP_Application (NOLOCK)  APP
			 JOIN #tmpOffice365ClientList O ON O.clientId = App.clientId AND O.agentType = 3 AND O.backupsetId = App.backupSet
			 JOIN APP_InstanceProp (NOLOCK)  Prop ON APP.instance = Prop.componentNameId AND prop.attrName = 'Cloud Apps Instance Type' AND prop.attrVal = '7'
			 JOIN APP_SubClientProp (NOLOCK)  SP ON SP.componentNameId = App.id AND SP.modified=0 AND SP.attrType = 134
WHERE App.appTypeId = 134 AND (APP.subclientStatus & 0x00004) != 0x00004
AND (APP.subclientStatus & 0x00002) != 0x00002
AND (APP.subclientStatus & 0x00020) != 0x00020
			GROUP BY backupsetId
		) tmp1 WHERE bksetid = backupsetId AND agentType = @ONEDRIVE AND clientType = 0 /*V1*/
		--Update v2 number of items (users/sites) for Sharepoint V2 & cloud Apps (OneDrive V2 , D365, Teams)
		UPDATE #tmpOffice365ClientList
		SET numberOfItems =  ISNULL(Tmp1.countmb, 0)
		FROM
		(
			select count(distinct AC.smtpAddress) as countmb , AC.subclientid as scId
			 FROM APP_Application (NOLOCK)  APP
			 JOIN #tmpOffice365ClientList O ON O.clientId = App.clientId AND O.backupsetId = App.backupSet
			 JOIN APP_CloudAppUserDetails (NOLOCK) AC ON APP.id = AC.subClientId
			 AND (
(App.appTypeId = 134 AND AC.status <> 1
					AND (AC.discoverByType = (CASE O.agentType WHEN  @ONEDRIVE   THEN @DISCOVERTYPE_USER
																		WHEN @TEAMS   THEN @DISCOVERTYPE_TEAM
																		WHEN @D365      THEN @DISCOVERTYE_DYNAMICS_365_ENTITY END) ))
					OR
(App.appTypeId = 78  AND AC.discoverByType = @DISCOVERTYPE_ASSOCIATED_SHAREPOINT_WEB_SITES AND  (AC.flags&2=0))
					)
					AND AC.modified = 0
			WHERE App.subclientStatus & 0x04 != 0x04 /*DELETED*/
			GROUP BY AC.subclientid
		) tmp1 WHERE scId = subclientId AND clientType = 1 /*V2*/
		-- Update stats Backup Size\count\idxcollectiontime of Sharepoint V2 AND cloud Apps (oneDrive, D365, Teams)
		UPDATE #tmpOffice365ClientList
        SET emailCount = ISNULL((SELECT R.ref.value('@itemsIndexed', '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 IN (78 , 200118,    200127,   200128)
        ) Idx
        where backupsetId = Idx.bkSetId AND clientType = 1 /*V2*/
		--Sharepoint V1 & Onedrive V1 application size
		IF OBJECT_ID('tempdb.dbo.#appCycle') IS NOT NULL     DROP TABLE #appCycle
		CREATE TABLE #appCycle (appid INT, maxCycle INT)
		CREATE NONCLUSTERED INDEX IDX_appCycle_appid ON #appCycle(appid)
		INSERT INTO #appCycle		SELECT J.appid, max(J.fullCycleNum)
		FROM JMBkpStats (NOLOCK) J
		INNER JOIN APP_Application (NOLOCK) A ON J.appId = A.id
		INNER JOIN #tmpOffice365ClientList CL ON CL.backupsetId = A.backupSet
WHERE (CL.agentType = @SHAREPOINT_ONLINE AND CL.clientType = 0 /*Sharepoint V1*/ AND J.appType = 78 AND A.appTypeId = 78 )
OR  (CL.agentType = @ONEDRIVE AND CL.clientType = 0 /*OneDrive V1*/ AND J.appType = 134 AND A.appTypeId = 134 )
		and J.fullCycleNum <> 0
		GROUP BY J.appId
		--Update Sharepoint V1 & onedrive V1 Application Size at backupset level.
		UPDATE #tmpOffice365ClientList
			SET appBackupSize = ISNULL(tSize.size , 0)
			FROM
			(
				SELECT backupSet as backupsetId, SUM(totalUncompBytes) as size
				FROM (
					SELECT BKS.appId, BKS.totalUncompBytes
					FROM JMBkpStats(NOLOCK) BKS
					JOIN #appCycle AC ON AC.appid = BKS.appId
					WHERE  BKS.fullCycleNum = AC.maxCycle
					AND bkpLevel IN (1/*FULL*/, 2 /*INCR*/, 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
				GROUP BY AP.backupSet
			)tSize WHERE tSize.backupSetId = #tmpOffice365ClientList.backupsetId
		IF OBJECT_ID('tempdb.dbo.#appCycle') IS NOT NULL     DROP TABLE #appCycle
		--get orgName here
		Update #tmpOffice365ClientList
		SET orgName =  D.hostName
		from App_Application E  WITH (NOLOCK)
		Join APP_ClientGroupAssoc A WITH(NOLOCK) ON  A.ClientId = E.clientId
		JOIN APP_ClientGroup B WITH (NOLOCK) ON A.clientGroupId = B.id
		JOIN App_CompanyProp C WITH (NOLOCK) ON C.attrName = 'Associated Smart Client Group' AND A.clientGroupId=C.attrValInt AND C.modIFied = 0
		JOIN UMDSProviders D WITH (NOLOCK) ON C.componentNameId = D.id
		WHERE E.id = subclientId and B.flag&0x1000 > 0
		-- V1 Sharepoint agents have backupset specific OrgName
		Update #tmpOffice365ClientList
		SET orgName =  D.hostName
		from App_Application E  WITH (NOLOCK)
		JOIN UMSecurityAssociations S WITH(NOLOCK) ON S.entityId4 = E.backupSet
		JOIN UMDSProviders D WITH(NOLOCK) ON D.id = S.companyId
		WHERE E.backupSet = backupsetId AND agentType = @SHAREPOINT_ONLINE AND          clientType = 0 AND S.companyId <> 0
		--get last backup job info
		Update #tmpOffice365ClientList
		SET lastBkpJobInfo = IP.attrVal
		FROM App_Idaprop IP (NOLOCK)
		JOIN APP_IdaName I (NOLOCK) ON I.id = IP.componentNameId
		JOIN #tmpOffice365ClientList T ON T.clientId = I.clientId AND I.appTypeId  = (CASE T.agentType  WHEN @EXCHANGE_ONLINE THEN  137 WHEN @ONEDRIVE THEN 134 WHEN @D365 THEN 134 WHEN @TEAMS THEN 134 END)
		WHERE backupsetId = T.backupsetId AND IP.attrname =  'Last Backup Job Info' AND IP.modified = 0
		--For Sharepoint - get last backup job info from backupset prop
		Update #tmpOffice365ClientList
		SET lastBkpJobInfo = BP.attrVal
		FROM APP_BackupSetProp (NOLOCK) BP
		WHERE backupsetId = BP.componentNameId AND agentType = @SHAREPOINT_ONLINE  AND BP.attrname = 'Last Backup Job Info' AND BP.modified = 0
		--get unique list of clients
		DECLARE @clientList table(clientId int, clientName nvarchar(256), displayName nvarchar(256), clientGuid UNIQUEIDENTIFIER, clientType INT, isDeleted INT)
		INSERT INTO @clientList SELECT DISTINCT clientId, clientName, displayName, clientGuid, clientType, isDeletedClient FROM #tmpOffice365ClientList
DECLARE @commCellName NVARCHAR(256)
DECLARE @csGuid NVARCHAR(40)
		SELECT 	@commCellName = aliasName,
				@csGuid = csGUID
		FROM APP_CommCell
		WHERE id = 2
		SET @inOutXmlReq = (SELECT
		(SELECT   clientId as '@clientId', clientName as '@clientName', displayName as '@displayName', clientGuid as '@clientGUID', clientType as '@o365PseudoClient',  isDeleted as '@isDeletedClient',
				(
				SELECT  @commCellName AS '@commCellName',
						@csGuid AS '@csGUID',
1 '@_type_'
				FOR XML PATH('commCell'),TYPE
				),
				(SELECT clientId as '@clientId', clientName as '@clientName', displayName as '@displayName' FOR XML PATH('client'), TYPE),
				(SELECT B.agentType as '@type', B.backupsetId as '@backupsetId', B.backupsetName as '@backupsetName', orgName as '@orgName', B.subclientId as '@subclientId' ,
				appBackupSize as '@applicationSize', numberOfItems as '@totalNumberOfItems', lastArchiveJobRanTime as '@lastJobRanTime',	idxCollectionTime as '@idxcollectiontime',
				(SELECT s.n.query('.') FROM B.lastBkpJobInfo.nodes('lastBackupJobInfo') s(n) ),
							(SELECT
							(SELECT planId as '@planId', planName as '@planName' FOR XML PATH('plan'), TYPE)
									FROM
									(
										select distinct backupsetId, backupsetName, clientId, agentType, subclientId, subclientName, planId, planName
										from #tmpOffice365ClientList
									) A WHERE clientId = C.clientId AND backupsetId = B.backupsetId AND ((A.agentType = @bkType)  OR (@ReturnAllAgents = 1))
							FOR XML PATH ('o365Subclient'), TYPE)
					FROM #tmpOffice365ClientList B, @bklist K WHERe B.clientId = C.clientId AND B.backupsetId = K.bkId
					FOR XML PATH ('O365BackupSet'), Type )
			FROM @clientList C
			FOR XML PATH ('o365Client'), type )
		FOR XML PATH('App_Office365PseudoClientsResp'))
	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_Office365PseudoClientsResp'))
	SELECT @inOutXmlReq
END
SET NOCOUNT OFF

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

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

insert into GXDBVersions values(2, 'GetOffice365PseudoClients',  'v1.1.2.40.4.10', 'GetOffice365PseudoClients', 'v1.1.2.40.4.10')
GO

