DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10317
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[IsMSP] int,[TotalMbV2] bigint,[OnPremMbV2] bigint,[O365MbV2] bigint,[MBLicensedV2] bigint,[OnPremMBLicensedV2] bigint,[O365MBLicensedV2] bigint,[TotalMBLicensedV2App] bigint,[TotalV2JrnAndSmtp] bigint,[ClassicMBs] bigint,[JournalMBs] bigint,[ContentStoreMBs] bigint,[TotalUsersJournled] bigint,[ArchiveEnabledMBV1] bigint,[TotalCiEnabledMBV2 ] bigint,[PurchasedArchiveMbV1] bigint,[PurchasedArchiveMbV2] bigint,[UserdArchiveMbV1] bigint,[UsedArchiveMbV2] bigint,[TotalExchMbAppSize] bigint,[ExchMbAppSizeV1] bigint,[ExchMbAppSizeV2] bigint,[TotalOneDriveCount] bigint,[OneDriveV1Count] bigint,[OneDriveV2Count] bigint,[totalLicensedOneDrive] bigint,[licenedOneDriveV1Count] bigint,[licenedOneDriveV2Count] bigint,[TotalLicensedOneDriveApp] bigint,[TotalOneDriveAppSize] bigint,[OneDriveAppSizeV1] bigint,[OneDriveAppSizeV2] bigint,[totalSharepoint] bigint,[totalSPV1Count] bigint,[totalSPV2Count] bigint,[TotalSharepointAppSize] bigint,[SharepointAppSizeV1] bigint,[SharepointAppSizeV2] bigint
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; -- CV_APPTYPE_EXCHANGE_ONEPASS      137
-- CV_APPTYPE_EXCHANGE_MAILBOX       54
-- CV_APPTYPE_CLOUD_APPS			134
-- CV_APPTYPE_SHAREPOINT_DOC_V2		 78

	DECLARE @isMSP INT = 0
	DECLARE @totalActiveMBClassic BIGINT = 0
	DECLARE @NoOfJournalMBs BIGINT = 0
	DECLARE @NoOfContenstoreMBs BIGINT = 0
	DECLARE @CS_SPVERSION INT = 0
	DECLARE @sqlCommand nvarchar(1000) = ''''
	 DECLARE @TotalMBCountV2 BIGINT = 0 
	 DECLARE @OnPremMBV2 BIGINT = 0
	 DECLARE @O365MBV2 BIGINT = 0
	 DECLARE @TotalMBLicensedV2 BIGINT = 0
	 DECLARE @OnPremMBLicensedV2 BIGINT = 0
	 DECLARE @O365MBLicensedV2 BIGINT = 0
	 DECLARE @TotalOneDriveCount BIGINT = 0
	 DECLARE @OneDriveV1Count BIGINT = 0
	 DECLARE @OneDriveV2Count BIGINT = 0 
	 DECLARE @TotalV2JrnAndSmtp  BIGINT = 0
	 DECLARE @totalSharepoint BIGINT = 0
	 DECLARE @totalSPV1Count BIGINT = 0
	 DECLARE @totalSPV2Count BIGINT = 0
	 DECLARE @totalLicensedOneDrive BIGINT = 0
	 DECLARE @licenedOneDriveV1Count BIGINT = 0
	 DECLARE @licenedOneDriveV2Count BIGINT = 0
	 DECLARE @totalArchiveMB  BIGINT = 0
	 DECLARE @NoOfJournalMBsV1 BIGINT = 0
	DECLARE @JrnMBUsersV2 BIGINT = 0
	DECLARE @NoOfContenstoreMBsV2 BIGINT = 0
	DECLARE @NoOfJournalMBsV2 BIGINT = 0
	DECLARE @ExchCleanupMBV2 BIGINT = 0
	DECLARE @ArchiveEnabledMBV1 BIGINT = 0
	DECLARE @TotalCiEnabledMBV2 BIGINT = 0
	DECLARE @ExchMbAppSizeV2 BIGINT = 0
	DECLARE @ExchMbAppSizeV1 BIGINT = 0
	DECLARE @OneDriveAppSize1 BIGINT = 0
	DECLARE @SharepointAppSize1 BIGINT = 0
	DECLARE @OneDriveAppSize2 BIGINT = 0
	DECLARE @SharepointAppSize2 BIGINT = 0
	DECLARE @AppEnvironmentType INT = 0
	DECLARE @TotalExchMbAppSize BIGINT = 0
	DECLARE @TotalOneDriveAppSize BIGINT = 0
	DECLARE @TotalSharepointAppSize BIGINT = 0
    DECLARE @purchasedArchiveMb_old BIGINT = 0
	DECLARE @purchasedMbArchive_New BIGINT = 0
	DECLARE @usedArchiveMb_Old BIGINT = 0
	DECLARE @usedMbArchive_New BIGINT = 0
	DECLARE @TotalMBLicensedV2App BIGINT = 0
	DECLARE @TotalLicensedOneDriveApp BIGINT = 0
	

SELECT Top 1 @CS_SPVERSION = PSP.SPMAJOR FROM simInstalledPackages SIM, PatchSPVersion PSP WHERE sim.ClientId = 2 AND Sim.simPackageID = 20 /*commserve package*/ AND sim.SPVersionID = psp.id AND psp.Release >= 16 
 
IF(@CS_SPVERSION < 16)
RETURN


	IF OBJECT_ID (''tempdb.dbo.#tmpApplication '') IS NOT NULL   DROP TABLE #tmpApplication
	CREATE TABLE #tmpApplication (id INT, clientId INT, instance INT, appTypeId INT, exchangeEnvironmentType INT, ver INT); 

	--Filter out UNINSTALLED | DELETED | HIDDEN subclients
	--V1 Subclients
			INSERT INTO   #tmpApplication
			SELECT distinct (A.id), A.clientId, A.instance, A.appTypeId, PRop.attrVal, 1  from App_Application A (nolock)
            INNER JOIN APP_IDAName DA (nolock) ON DA.clientId = A.clientId
            LEFT JOIN APP_IDAProp PROP (nolock) ON PROP.componentNameId = DA.id AND PROP.attrName = ''Exchange Environment Type'' AND PROP.modified = 0
            WHERE ( A.appTypeId = 54 OR A.appTypeId = 67)
            AND subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND subclientStatus & 0x04 != 0x04 /*DELETED*/ AND subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/
	--V2 Subclients
			INSERT INTO #tmpApplication
			SELECT  Distinct (App.id), App.clientId, App.instance , App.apptypeId, 
			CASE WHEN(exchangeVersion != 2 OR exchangeVersion IS NULL) /*On Prem V2*/	THEN 1
				WHEN (exchangeVersion = 2 ) /*Exchange online V2*/ THEN 4 END, 2				 
			from APP_EmailConfigPolicyAssoc A (nolock) 
			JOIN App_Application App (nolock) ON App.id = A.subClientId
			WHERE subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND subclientStatus & 0x04 != 0x04 /*DELETED*/ AND subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/
					  

	
	--Is MSP commcell        	
        SET @isMSP =   ISNULL((select TOP 1 1 from umdsproviders (nolock) where serviceType = 5 /* #define UMDS_PROVIDER_ORGANIZATIONS 5 */ ) , 0)

	
--	Total v2 mailboxes (licensed + non-licensed + on-prem)
     SET @sqlCommand = ''SELECT @OnPremMBV2 = count(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE mailboxType = 1 AND modified = 0 AND ((exchangeVersion != 2) OR (exchangeVersion IS NULL)) /*Exchange online*/
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137)''
       EXEC sp_executesql @sqlCommand , N''@OnPremMBV2 INT OUTPUT'', @OnPremMBV2 = @OnPremMBV2 OUTPUT

--	Total v2 mailboxes (licensed + non-licensed + O365)
       SET @sqlCommand =''SELECT @O365MBV2 = count(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE mailboxType = 1 AND modified = 0 AND  exchangeVersion = 2   /*Exchange online*/
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137)''
       EXEC sp_executesql @sqlCommand , N''@O365MBV2 INT OUTPUT'', @O365MBV2 = @O365MBV2 OUTPUT      

	   			
-- Total v2 mailboxes (licensed + non-licensed + on-prem + O365)
	SET @TotalMBCountV2 = (SELECT ISNULL(count(distinct smtpAdrress) , 0) 
	          FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE mailboxType = 1 And modified = 0
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137	))
	

-- Total v2 licensed mailboxes (O365)
       SET @sqlCommand =''SELECT @O365MBLicensedV2 = count(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE modified = 0 AND (msExchRecipientTypeDetails  IN(NULL, 1) AND mailboxType = 1 AND  exchangeVersion = 2   /*Exchange online*/)
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137)
			  AND subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)''
       EXEC sp_executesql @sqlCommand , N''@O365MBLicensedV2 INT OUTPUT'', @O365MBLicensedV2 = @O365MBLicensedV2 OUTPUT      

--	Total v2 licensed mailboxes (on-prem)
	SET @sqlCommand = ''SELECT @OnPremMBLicensedV2 = count(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE modified = 0 AND msExchRecipientTypeDetails IN(NULL, 1) AND mailboxType = 1 AND (exchangeVersion != 2 OR exchangeVersion IS NULL) /*Exchange online*/
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137) 
			  AND subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)''
       EXEC sp_executesql @sqlCommand , N''@OnPremMBLicensedV2 INT OUTPUT'', @OnPremMBLicensedV2 = @OnPremMBLicensedV2 OUTPUT
		
-- Total v2 licensed mailboxes FROM license table (on-prem + O365)
IF(@CS_SPVERSION < 17)	
	SET @TotalMBLicensedV2 = (	 select ISNULL(count(distinct objectname ),0) as objName from historydb.dbo.Lic_CurrentUsage  WITH (NOLOCK) where usageType = 18 AND AppTypeId in ( 137) and subclientname like ''usermailbox''  and LicType = 200011)
	ELSE 	
	SET @TotalMBLicensedV2 = (	 select ISNULL(count(distinct objectname ),0) as objName from Lic_CurrentUsage  WITH (NOLOCK) where usageType = 18 AND AppTypeId in ( 137) and subclientname like ''usermailbox''  and LicType = 200011)	
		
	-- Total v2 licensed mailboxes (on-prem + O365)	
	SET @sqlCommand =''SELECT @TotalMBLicensedV2App = count(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock)
              WHERE modified = 0 AND msExchRecipientTypeDetails  IN(NULL, 1) AND mailboxType = 1 			  
              AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137)
			  AND subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)''
       EXEC sp_executesql @sqlCommand , N''@TotalMBLicensedV2App INT OUTPUT'', @TotalMBLicensedV2App = @TotalMBLicensedV2App OUTPUT



    --- Total v2 Cleanup Mailboxes (where cleanup policy is associated)	
		SET @ExchCleanupMBV2 =  (select ISNULL(count(distinct smtpAdrress) , 0) from APP_EmailConfigPolicyAssoc E (nolock)
		JOIN APP_EmailConfigPolicies EP  (nolock) ON Ep.componentNameId = E.assocId	
		WHERE E.mailBoxType = 1 AND EP.policyType = 2 /* Ida::EmailPolicyType_CLEANUP=2 */ AND EP.modified = 0	
		AND E.status = 0 AND E.deleted = 0 AND E.modified = 0)
    
	--Total v2 CI mailboxes (content indexing enabled in archive policy)    
	SET @TotalCiEnabledMBV2 =  (select ISNULL(count(distinct smtpAdrress),0) from APP_EmailConfigPolicyAssoc E (nolock)
	JOIN APP_EmailConfigPolicies EP (nolock) ON Ep.componentNameId = E.assocId
	JOIN APP_ConfigurationPolicyDetails CD (nolock) on CD.componentNameId = EP.policyId
	WHERE E.mailBoxType = 1 AND E.modified = 0 AND EP.policyType = 1 /* Ida::EmailPolicyType_ARCHIVE=1 */ AND EP.modified = 0
	AND CD.policyDetails.value(''(/emailPolicy/archivePolicy/contentIndexProps/@enableContentIndex)[1]'',''INT'') = 1 
	AND E.status = 0 AND E.deleted = 0 AND E.modified = 0 AND CD.modified = 0)
    

	--Total v1 mailboxes (Exchange Mailbox Classic agent) � for active subclient check for subclient status and activity status	
	SET @totalActiveMBClassic = (SELECT ISNULL(count(distinct(attrVal)), 0) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0
				  AND componentNameId in  ( SELECT id from #tmpApplication (nolock) WHERE appTypeId = 54))
				  
	
	
	--Total v1 Archive enabled mailboxes (Mailbox classic agent: check for message rules enabled)			
	SET @ArchiveEnabledMBV1 = (SELECT ISNULL(count(distinct(attrVal)), 0) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0 AND componentNameId IN 
	(select componentNameId from app_subclientprop where attrname = ''Exch Arch Rules'' AND modified = 0 AND componentNameId IN (SELECT  id	FROM    #tmpApplication WITH(NOLOCK)	WHERE   appTypeId = 54)))
    
				
    -- Total v2 Journal Mailbox
		select @NoOfJournalMBsV2        =    ISNULL(count(DISTINCT(smtpAdrress)),0) FROM App_EmailConfigPolicyAssoc (nolock)
       WHERE modified = 0 AND subClientId IN (SELECT id FROM #tmpApplication (nolock) WHERE appTypeId = 137) and mailBoxType = 2-- journal mailbox
		
      --Total v2 SMTP Mailboxes
	  select @NoOfContenstoreMBsV2 =  ISNULL(count(DISTINCT(smtpAdrress)),0) FROM App_EmailConfigPolicyAssoc (nolock)
       WHERE modified = 0 AND subClientId IN (SELECT id FROM #tmpApplication (nolock) WHERE appTypeId = 137) and mailBoxType = 3		--contenstore mailbox
	
	--    Total v2 Journal Mailboxes (Journal + SMTP)
		SELECT @TotalV2JrnAndSmtp =  ISNULL(count(DISTINCT(smtpAdrress)),0) FROM App_EmailConfigPolicyAssoc (nolock)
       WHERE modified = 0 AND subClientId IN (SELECT id FROM #tmpApplication (nolock) WHERE appTypeId = 137) and mailboxtype in (2,3)   --jrn and contentstore
  
  
       --- Total v2 Users that are being journaled 
	   SELECT @JrnMBUsersV2 =   ISNULL(count(DISTINCT(smtpAdrress)),0) FROM App_EmailConfigPolicyAssoc (nolock)
      WHERE modified = 0 AND subClientId IN (SELECT id FROM #tmpApplication (nolock) WHERE appTypeId = 137) and discoverByType = 13
	   	   
	   
	--  Total v1 Journal Mailboxes (Compliance archiver agent) � for active subclient check for subclient status and activity status
       SET @NoOfJournalMBsV1 = (SELECT ISNULL(count(distinct(attrVal)),0) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0
			AND componentNameId in 	( SELECT  id	FROM    #tmpApplication WITH(NOLOCK)  WHERE   appTypeId = 67) 
			AND componentNameId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1))

		   
	--  Total OneDrive users (licensed + non-licensed + v1)	
		SET @OneDriveV1Count   =  (select ISNULL(count(distinct SP.attrName) ,0)
             FROM APP_Application (NOLOCK)  APP             
             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 /*$$(CV_APPTYPE_CLOUD_APPS)*/  AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/)
			--SET @TotalOneDriveV1Count   = ISNULL((select count(distinct mailbox) from #tmpmailBoxCountTable where InstanceType = 7), 0)
    

    --  Total OneDrive users (licensed + non-licensed + v2)	
		SET @OneDriveV2Count = (select ISNULL(count(distinct AC.smtpAddress) ,0)
             FROM APP_Application (NOLOCK)  APP           
             JOIN APP_CloudAppUserDetails (NOLOCK) AC ON APP.id = AC.subClientId AND AC.modified = 0             
		WHERE App.appTypeId =134 /* $$(CV_APPTYPE_CLOUD_APPS)*/ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/)
				
	
	--  Total OneDrive users (licensed + non-licensed + v1 + v2)
	       SET @TotalOneDriveCount = ISNULL((@OneDriveV1Count + @OneDriveV2Count),0)
    
	
	--Total licensed OneDrive users (v1)    
  SET @licenedOneDriveV1Count = ( select ISNULL( count( distinct (CASE WHEN CHARINDEX('';'',SP.attrName) = 0 THEN SP.attrName
          WHEN CHARINDEX('';'',SP.attrName) = len(SP.attrName) THEN SUBSTRING(SP.attrName,1,CHARINDEX('';'',SP.attrName)-1)
          WHEN CHARINDEX('';'',SP.attrName) < len(SP.attrName) THEN SUBSTRING(SP.attrName,CHARINDEX('';'',SP.attrName)+1,len(SP.attrName))
			END ) ),0)
             FROM APP_Application (NOLOCK)  APP             
             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 AND SP.attrVal like ''%::oq''
            WHERE App.appTypeId = 134 /*$$(CV_APPTYPE_CLOUD_APPS) */ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/ AND SP.componentNameId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
			 AND SP.componentnameId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1))
	
	--  Total licensed OneDrive users (v2)
	 SET @licenedOneDriveV2Count = (  select ISNULL(count( distinct AC.smtpAddress ), 0)
             FROM APP_Application (NOLOCK)  APP            
             JOIN APP_CloudAppUserDetails (NOLOCK) AC ON APP.id = AC.subClientId AND (AC.flags & 1 ) !=0 AND AC.modified = 0             
		WHERE App.appTypeId = 134 /*$$(CV_APPTYPE_CLOUD_APPS)*/ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/ AND AC.subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
		 AND AC.subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1))
	
	--OneDrive licensesd users
	SEt @TotalLicensedOneDriveApp = ( select ISNULL(count(distinct (OneDrive.odUser) ), 0) FROM
	(
		select  distinct (CASE WHEN CHARINDEX('';'',SP.attrName) = 0 THEN SP.attrName
          WHEN CHARINDEX('';'',SP.attrName) = len(SP.attrName) THEN SUBSTRING(SP.attrName,1,CHARINDEX('';'',SP.attrName)-1)
          WHEN CHARINDEX('';'',SP.attrName) < len(SP.attrName) THEN SUBSTRING(SP.attrName,CHARINDEX('';'',SP.attrName)+1,len(SP.attrName))
			END ) AS odUser
             FROM APP_Application (NOLOCK)  APP             
             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 AND SP.attrVal like ''%::oq''
            WHERE App.appTypeId = 134 /*$$(CV_APPTYPE_CLOUD_APPS) */ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/ AND SP.componentNameId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
			 AND SP.componentnameId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
	UNION

		select distinct AC.smtpAddress AS odUser
             FROM APP_Application (NOLOCK)  APP            
             JOIN APP_CloudAppUserDetails (NOLOCK) AC ON APP.id = AC.subClientId AND (AC.flags & 1 ) !=0 AND AC.modified = 0             
		WHERE App.appTypeId = 134 /*$$(CV_APPTYPE_CLOUD_APPS)*/ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/ AND AC.subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
		 AND AC.subClientId NOT IN (SELECT appId FROM JMJobAction (nolock) WHERE appId <> 1 AND opType = 4 AND action = 1)
	) OneDrive )
	
	--Total licensed onedrive FROM license table
	IF (@CS_SPVERSION < 17 )
	SEt @totalLicensedOneDrive = (select ISNULL(count(distinct ObjectName ), 0) from historydb.dbo.Lic_CurrentUsage L WITH (NOLOCK) where usagetype = 18 AND apptypeid =134 and LicType in (200015))
	ELSE 
	SEt @totalLicensedOneDrive = (select ISNULL(count(distinct ObjectName ), 0) from Lic_CurrentUsage L WITH (NOLOCK) where usagetype = 18 AND apptypeid =134 and LicType in (200015))

	--	Total SharePoint users (v1)
		SET @totalSPV1Count = (SELECT  ISNULL(count( DISTINCT P.attrName ), 0)
		FROM APP_BackupSetProp P (nolock)
		INNER JOIN APP_Application APP (nolock) ON APP.backupSet = P.componentNameId AND APP.appTypeId = 78 AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/
		WHERE P.attrType = 137 AND P.modified = 0) 
		
    -- Total SharePoint users (v2)
		SET @totalSPV2Count = (select ISNULL(count(distinct AC.smtpAddress) , 0)
             FROM APP_Application (NOLOCK)  APP            
             JOIN APP_CloudAppUserDetails (NOLOCK) AC ON APP.id = AC.subClientId AND AC.modified = 0             
		WHERE App.appTypeId = 78 /*$$(CV_APPTYPE_SHAREPOINT_DOC_V2)*/ AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/)
	
	--Total SharePoint users (v1 + v2)
		SET @totalSharepoint = ISNULL((	@totalSPV1Count + @totalSPV2Count	),0)
			      
		
		-- Delete Jrn Smtp MBs of Exchange Subclients
		DELETE FROM #tmpApplication WHERE Id in (select subclientId from  App_EmailconfigpolicyAssoc where appTypeId = 137 AND mailBoxType != 1 )

		--OneDrive Subclients
		INSERT INTO #tmpApplication
		SELECT distinct (A.id), A.clientId, I.componentNameId, A.apptypeId, 4 /*ONLINE*/, 1
		FROm APP_Application A (nolock)	
		JOIN APP_InstanceProp I(nolock) ON I.componentNameId = A.instance
		WHERE A.appTypeId = 134 AND subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND subclientStatus & 0x04 != 0x04 /*DELETED*/ AND subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/
		AND I.attrName = ''Cloud Apps Instance Type'' AND I.attrVal = ''7''
		
		--Sharepoint Subclients
		INSERT INTO #tmpApplication
		SELECT A.id, A.clientId, 0, A.apptypeId , 4 , 1 
		FROM  APP_Application A (nolock)
		JOIN APP_BackupSetName (nolock) B ON B.Id = A.backupSet   
		WHERE A.appTypeId = 78 AND subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND subclientStatus & 0x04 != 0x04 /*DELETED*/ AND subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/


		--update Indexing version for Sharepoint and OneDrive 
		Update T  SET ver = 2
		FROM #tmpApplication  T
		JOIN APP_CloudAppUserDetails C ON T.id = C.subClientId
		where C.modified = 0
		
		--JOIN App_clientProp C ON C.componentNameId = A.clientId  C.attrName = ''IndexingV2_OneDrive'' and P.attrVal = ''1'' and  
		IF OBJECT_ID (''tempdb.dbo.#bkCycleStats'') IS NOT NULL   DROP TABLE #bkCycleStats 
		CREATE TABLE #bkCycleStats(appId INT, currentCycle INT );

		INSERT INTO #bkCycleStats
		SELECT T.id, max(J.fullCycleNum) 
		FROM JMBkpStats J	(nolock)
		JOIN #tmpApplication T (nolock) ON T.id = J.appId AND T.appTypeId IN (134, 137, 78, 54)
		GROUP BY T.id
		
		DECLARE @appSizeTbl TABLE  ( apptype INT ,size BIGINT, ver INT )
		INSERT INTO @appSizeTbl 
		select tsize.appType, tSize.size , tSize.ver     
         FROM
         (SELECT  ISNULL(sum (J.totalUnCompBytes),0)  AS size, T.appTypeId as appType , T.ver as ver
		 FROM #tmpApplication T  (nolock)      
         JOIN JMBkpStats(nolock) J ON J.appId = T.id AND J.status IN  (1/*JMSUCCESS*/, 3/*PARTIALSUCCESS*/, 14/*JMSUCCESSWITHWARNINGS*/) 
		 JOIN #bkCycleStats B ON B.appId = T.id 
		 WHERE T.appTypeId IN (134, 54, 137, 78) --/*$$(CV_APPTYPE_CLOUD_APPS), $$(CV_APPTYPE_MS_SHAREPOINTV2_DOC)*/
         GROUP BY T.appTypeId, T.ver) tSize 	 
        

		 SET @ExchMbAppSizeV2 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 137 AND ver = 2)
		 SET @ExchMbAppSizeV1 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 54 and ver = 1)
		 SET @OneDriveAppSize1 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 134 ANd ver =1 )
		 SET @OneDriveAppSize2 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 134 ANd ver =2 )
		 SET @SharepointAppSize1 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 78 AND ver = 1)
		 SET @SharepointAppSize2 = (SELECT ISNULL(size, 0 ) from @appSizeTbl WHERE apptype = 78 AND ver = 2)
		 SET @TotalExchMbAppSize = ISNULL(SUM(ISNULL(@ExchMbAppSizeV1, 0) + ISNULL(@ExchMbAppSizeV2, 0)),0)
		 SET @TotalOneDriveAppSize = ISNULL(SUM(ISNULL(@OneDriveAppSize1 ,0) + ISNULL(@OneDriveAppSize2, 0)),0)
		 SET @TotalSharepointAppSize =   ISNULL(SUM(ISNULL(@SharepointAppSize1, 0) + ISNULL(@SharepointAppSize2, 0)),0)
	        
        --Purchased Mailbox license
        --Used Mailbox license
		
 -- purchased vArchive MB Old
SET @purchasedArchiveMb_old = (select ISNULL(PermTotal+EvalTotal, 0) from Lic_LicenseInfo  WITH (NOLOCK) where lictype  = 100013 )
-- purchased Archive MB New
SET @purchasedMbArchive_New = ( select ISNULL(PermTotal+EvalTotal,0) from Lic_LicenseInfo  WITH (NOLOCK) where lictype  = 100028)


-- used Archive MB Old 
IF (@CS_SPVERSION < 17)
SET @usedArchiveMb_Old = (select  ISNULL(count(distinct(objectname)),0) from HistoryDB.dbo.Lic_CurrentUsage  WITH (NOLOCK) where usagetype = 18 AND lictype  = 100013)
ELSE
SET @usedArchiveMb_Old = (select  ISNULL(count(distinct(objectname)),0) from Lic_CurrentUsage  WITH (NOLOCK) where usagetype = 18 AND lictype  = 100013)

-- used Archive MB New
DECLARE @EarlierstSp INT = 0
SET @EarlierstSp = (SELECT TOP 1 HighestSP FROM PatchInstallHistory (READUNCOMMITTED) WHERE ClientId = 2 AND simPackageID = 20 ORDER BY OpTime ASC)
IF (@EarlierstSp >= 19)
SET @usedMbArchive_New = (select  ISNULL(count(distinct(objectname)), 0) from Lic_CurrentUsage  WITH (NOLOCK) where usagetype = 18  AND (apptypeid IN (54, 78, 137) OR lictype in (200011, 200015) ))
ELSE
BEGIN
	IF (@CS_SPVERSION < 17)
	SET @usedMbArchive_New = (select  ISNULL(count(distinct(objectname)),0) from HistoryDB.dbo.Lic_CurrentUsage  WITH (NOLOCK) where usagetype = 18  AND  lictype != 100013)
	ELSE
	SET @usedMbArchive_New = (select  ISNULL(count(distinct(objectname)),0) from Lic_CurrentUsage  WITH (NOLOCK) where usagetype = 18  AND  lictype != 100013)
END


SELECT ISNULL(@isMSP , 0) as ''IsMSP'',
	@TotalMBCountV2 as ''TotalMbV2'',
	@OnPremMBV2 as ''OnPremMbV2'',
	@O365MBV2 ''O365MbV2'',
	@TotalMBLicensedV2 as ''MBLicensedV2'',
	@OnPremMBLicensedV2 as ''OnPremMBLicensedV2'',
	@O365MBLicensedV2 as ''O365MBLicensedV2'',
	@TotalMBLicensedV2App as ''TotalMBLicensedV2App'',	

	@TotalV2JrnAndSmtp as ''TotalV2JrnAndSmtp'',
	@totalActiveMBClassic as ''ClassicMBs'',
	@NoOfJournalMBsV2 as ''JournalMBs'',
	@NoOfContenstoreMBsV2  as ''ContentStoreMBs'',
	
	@JrnMBUsersV2 as ''TotalUsersJournled'',

	@ArchiveEnabledMBV1 as ''ArchiveEnabledMBV1'',
	@TotalCiEnabledMBV2  as ''TotalCiEnabledMBV2 '',

	ISNULL(@purchasedArchiveMb_old, 0) as ''PurchasedArchiveMbV1'',
	ISNULL(@purchasedMbArchive_New, 0) as ''PurchasedArchiveMbV2'',
	ISNULL(@usedArchiveMb_Old, 0) as ''UserdArchiveMbV1'',
	ISNULL(@usedMbArchive_New, 0) as ''UsedArchiveMbV2'',
 
	ISNULL(@TotalExchMbAppSize, 0) as ''TotalExchMbAppSize'',
	ISNULL(@ExchMbAppSizeV1, 0) as ''ExchMbAppSizeV1'', 
	ISNULL(@ExchMbAppSizeV2, 0) as ''ExchMbAppSizeV2'',

	 @TotalOneDriveCount as ''TotalOneDriveCount'',
	 @OneDriveV1Count as ''OneDriveV1Count'',
	 @OneDriveV2Count as ''OneDriveV2Count'',
 
	 @totalLicensedOneDrive as ''totalLicensedOneDrive'',
	 @licenedOneDriveV1Count as ''licenedOneDriveV1Count'',
	 @licenedOneDriveV2Count as ''licenedOneDriveV2Count'' ,
	 @TotalLicensedOneDriveApp as  ''TotalLicensedOneDriveApp'',
	 
	 ISNULL(@TotalOneDriveAppSize, 0) as ''TotalOneDriveAppSize'',
	 ISNULL(@OneDriveAppSize1, 0) as ''OneDriveAppSizeV1'',
	 ISNULL(@OneDriveAppSize2, 0) as ''OneDriveAppSizeV2'',

	 @totalSharepoint as ''totalSharepoint'' ,
	 @totalSPV1Count as ''totalSPV1Count'',
	 @totalSPV2Count as ''totalSPV2Count'',

	 ISNULL(@TotalSharepointAppSize, 0) as ''TotalSharepointAppSize'', 
	 ISNULL(@SharepointAppSize1, 0) as ''SharepointAppSizeV1'', 
	ISNULL( @SharepointAppSize2, 0) as ''SharepointAppSizeV2''

IF OBJECT_ID (''tempdb.dbo.#tmpApplication '') IS NOT NULL   DROP TABLE #tmpApplication ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [IsMSP] AS '@IsMSP',[TotalMbV2] AS '@TotalMbV2',[OnPremMbV2] AS '@OnPremMbV2',[O365MbV2] AS '@O365MbV2',[MBLicensedV2] AS '@MBLicensedV2',[OnPremMBLicensedV2] AS '@OnPremMBLicensedV2',[O365MBLicensedV2] AS '@O365MBLicensedV2',[TotalMBLicensedV2App] AS '@TotalMBLicensedV2App',[TotalV2JrnAndSmtp] AS '@TotalV2JrnAndSmtp',[ClassicMBs] AS '@ClassicMBs',[JournalMBs] AS '@JournalMBs',[ContentStoreMBs] AS '@ContentStoreMBs',[TotalUsersJournled] AS '@TotalUsersJournled',[ArchiveEnabledMBV1] AS '@ArchiveEnabledMBV1',[TotalCiEnabledMBV2 ] AS '@Col_1438847096',[PurchasedArchiveMbV1] AS '@PurchasedArchiveMbV1',[PurchasedArchiveMbV2] AS '@PurchasedArchiveMbV2',[UserdArchiveMbV1] AS '@UserdArchiveMbV1',[UsedArchiveMbV2] AS '@UsedArchiveMbV2',[TotalExchMbAppSize] AS '@TotalExchMbAppSize',[ExchMbAppSizeV1] AS '@ExchMbAppSizeV1',[ExchMbAppSizeV2] AS '@ExchMbAppSizeV2',[TotalOneDriveCount] AS '@TotalOneDriveCount',[OneDriveV1Count] AS '@OneDriveV1Count',[OneDriveV2Count] AS '@OneDriveV2Count',[totalLicensedOneDrive] AS '@totalLicensedOneDrive',[licenedOneDriveV1Count] AS '@licenedOneDriveV1Count',[licenedOneDriveV2Count] AS '@licenedOneDriveV2Count',[TotalLicensedOneDriveApp] AS '@TotalLicensedOneDriveApp',[TotalOneDriveAppSize] AS '@TotalOneDriveAppSize',[OneDriveAppSizeV1] AS '@OneDriveAppSizeV1',[OneDriveAppSizeV2] AS '@OneDriveAppSizeV2',[totalSharepoint] AS '@totalSharepoint',[totalSPV1Count] AS '@totalSPV1Count',[totalSPV2Count] AS '@totalSPV2Count',[TotalSharepointAppSize] AS '@TotalSharepointAppSize',[SharepointAppSizeV1] AS '@SharepointAppSizeV1',[SharepointAppSizeV2] AS '@SharepointAppSizeV2' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10317'), type
)
  FOR XML PATH ('SurveyResults') 
)

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
SET @outputXML = (SELECT @queryId AS '@QueryId', @EndTime AS '@LogDate', (@EndTime - @LogDate) AS '@QueryRunningTime', @surveyXML FOR XML PATH('Rpt_CSSXMLDATA'))