DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10220
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[V2MailboxTotal] bigint,[V2MailboxOnPremises] bigint,[V2MailboxO365] bigint,[MBClassicActive] bigint,[MBO365Classic] bigint,[MBClassicInactive] bigint,[Journal] bigint,[SMTP] bigint,[IsMSP] int,[ActiveExchComplArchMB] bigint,[InActiveExchComplArchMB] bigint,[TotalOutlookProviderUsers] bigint,[SharepointO365] int,[OneDrive] int,[GoogleDrive] int,[GoogleMail] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; --------------------------------------------------------------------------
--------------------------------Exchange queries-----------------------
---------------------------------------------------------------------------
-- Total mailboxes being protected today with Classic and with V2 mailbox agent by customer.

-- CV_APPTYPE_EXCHANGE_ONEPASS      137
-- CV_APPTYPE_EXCHANGE_MAILBOX           54
-- CV_APPTYPE_MSEXCHANGE_DATABASE 53
-- Exchange Compliance Archiver  67

DECLARE @totalNoOfActiveMBInClassicAgent BIGINT = 0
DECLARE @totalNoOfMBInAgent BIGINT = 0 
DECLARE @NoOfOffice365MbIfAny BIGINT = 0
DECLARE @NoOfDeconfClassicAgent BIGINT = 0
DECLARE @NoOfJournalMBs BIGINT = 0
DECLARE @NoOfContenstoreMBs BIGINT = 0
DECLARE @CS_SPVERSION INT = 0
DECLARE @sqlCommand nvarchar(1000) = ''''
DECLARE @OnPremMBProtected BIGINT = 0
DECLARE @O365MBProtected BIGINT = 0
DECLARE @totalNoOfActiveMBInComplArchiver BIGINT = 0
DECLARE @NoOfDeconfiguredMBInComplArchiver BIGINT = 0

IF OBJECT_ID (''tempdb.dbo.#tmpMbTable'') IS NOT NULL   DROP TABLE #tmpMbTable

CREATE TABLE #tmpMbTable(mbcount BIGINT, mailboxType INT );

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);


--Filter out UNINSTALLED | DELETED | HIDDEN subclients
INSERT INTO   #tmpApplication 
SELECT A.id,A.clientId, A.instance, A.appTypeId, PRop.attrVal 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 = 53 OR A.appTypeId = 54 OR A.appTypeId = 137 OR A.appTypeId = 67) 
                     AND subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND subclientStatus & 0x04 != 0x04 /*DELETED*/ AND subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/

SELECT @CS_SPVERSION = MAX(HighestSP) FROM simInstalledPackages S JOIN APP_Client (nolock) C  ON S.clientID=C.id AND C.releaseID >=16 AND C.ID = 2  
       

---------------------Exchange V1-------------------------------------------
--Get total exchange mailboxes protected by classic agent
SET @totalNoOfActiveMBInClassicAgent = (SELECT count(distinct(attrName)) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0 
                                                              AND componentNameId in 
                                                              ( SELECT id from #tmpApplication  WHERE appTypeId = 54) 
                                                        )-- exchange mb property

--Count deconfigured mailboxes if any 
SET @NoOfDeconfClassicAgent = (SELECT count(distinct(attrName)) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0 
                                                              AND componentNameId in 
                                                              ( SELECT id from App_Application (nolock) WHERE appTypeId = 54 AND (subclientStatus & 0x02 = 0x02 /*UNINSTALLED*/ OR subclientStatus & 0x04 = 0x04 /*DELETED*/ )) 
                                                        )-- exchange mb property

SET @NoOfOffice365MbIfAny = (
                 SELECT count(SP.id) FROM App_Client (nolock) AC
              INNER JOIN #tmpApplication  APP ON APP.clientId = AC.id
              INNER JOIN APP_SubClientProp (NOLOCK) SP ON SP.componentNameId = APP.id AND SP.modified = 0 and SP.attrTYpe = 111 --exchange mailbox association
              INNER JOIN APP_InstanceProp (NOLOCK) AIP ON AIP.componentNameId = App.instance AND  AIP.attrName=''Exchange Online Enabled'' and AIP.attrVal = ''1'' and AIP.modified=0
              )

-- dont count office365 mailboxes for on-premises
SET @totalNoOfActiveMBInClassicAgent = @totalNoOfActiveMBInClassicAgent - @NoOfOffice365MbIfAny

-----------------Get total no of Compliance Archiver mailboxes protected 
SET @totalNoOfActiveMBInComplArchiver = (SELECT count(distinct(attrVal)) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0 
                                                              AND componentNameId in 
                                                              ( SELECT id from #tmpApplication  WHERE appTypeId = 67) 
                                                        )-- exchange mb property

--Count deconfigured Compliance Archiver mailboxes if any 
SET @NoOfDeconfiguredMBInComplArchiver = (SELECT count(distinct(attrVal)) FROM APP_SUBCLIENTPROP (nolock) WHERE attrType= 111 and modified = 0 
                                                              AND componentNameId in 
                                                              ( SELECT id from App_Application (nolock) WHERE appTypeId = 67 AND (subclientStatus & 0x02 = 0x02 /*UNINSTALLED*/ OR subclientStatus & 0x04 = 0x04 /*DELETED*/ )) 
                                                        )-- exchange mb property

--------------------------Exchange V2------------------------------------
-- For total mailbox count only consider active usermailboxes not room-mailboxes, equipment mailboxes. For sp14 total is recipient type and below sp14 total is all active users]

IF(@CS_SPVERSION < 14)
BEGIN
INSERT INTO #tmpMbTable 
SELECT count(DISTINCT(smtpAdrress)),mailBoxType  /*below SP14 */
       FROM App_EmailConfigPolicyAssoc (nolock) 
       WHERE modified = 0 and mailBoxType IS NOT NULL AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137) 
       GROUP BY mailBoxType 
END
ELSE
BEGIN
       /*Sp14 onwards. Not all setups will be on sp14 using dynamic sql query to avoid compile time checks*/
       
       SET @sqlCommand = ''INSERT INTO #tmpMbTable SELECT  count(DISTINCT(smtpAdrress)),mailBoxType FROM App_EmailConfigPolicyAssoc (nolock) 
       WHERE modified = 0 AND ((msExchRecipientTypeDetails IN (NULL, 1) AND mailboxType = 1) OR mailboxType = 2 OR mailboxType = 3)
       AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137) 
       GROUP BY mailBoxType''

       EXEC sp_executesql @sqlCommand 
END    

--Get total exchange mailbox protected by new agent
SET @totalNoOfMBInAgent =  ISNULL((SELECT mbcount from #tmpMbTable WHERE mailboxType = 1), 0)                --user mailbox
SET @NoOfJournalMBs        =      ISNULL((SELECT mbcount from #tmpMbTable WHERE mailboxType = 2),      0)                     -- journal mailbox
SET @NoOfContenstoreMBs =  ISNULL((SELECT TOP 1 mbcount from #tmpMbTable WHERE mailboxType = 3), 0)   --contenstore mailbox

IF(@CS_SPVERSION < 14)
BEGIN
       -- On-premises mailboxes
       SELECT @OnPremMBProtected = count(DISTINCT(smtpAdrress))/*below SP14 */
              FROM App_EmailConfigPolicyAssoc (nolock) 
              WHERE modified = 0 and mailBoxType=1 AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137 AND exchangeEnvironmentType IN (NULL, 1, 2)) 

       -- Office-365 mailboxes
       SELECT @O365MBProtected = COUNT(DISTINCT(smtpAdrress))
              FROM App_EmailConfigPolicyAssoc (nolock) 
              WHERE modified = 0 and mailBoxType=1 AND subClientId IN (SELECT id FROM #tmpApplication  WHERE appTypeId = 137 AND exchangeEnvironmentType IN (3, 4)) 

END 
ELSE   /*Sp14 onwards*/
BEGIN
              -- On-premises mailboxes
       SET @sqlCommand = ''SELECT @OnPremMBProtected = 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)''
       EXEC sp_executesql @sqlCommand , N''@OnPremMBProtected INT OUTPUT'', @OnPremMBProtected = @OnPremMBProtected OUTPUT

       -- Office-365 mailboxes
       SET @sqlCommand =''SELECT @O365MBProtected = 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)''
       EXEC sp_executesql @sqlCommand , N''@O365MBProtected INT OUTPUT'', @O365MBProtected = @O365MBProtected OUTPUT
END




--------------------------------------------------------------------------
--------------------------------Sharepoint\CloudApps queries--------------
---------------------------------------------------------------------------

DECLARE @oneDriveCount INT = 0
DECLARE @GDriveCount INT = 0
DECLARE @gmailCount INT = 0
DECLARE @NumberOfSharepointObjects INT = 0
IF object_id(''tempdb.dbo.#tmpO365SharePointClients'') IS NOT null DROP TABLE #tmpO365SharePointClients
 
 -- Count Office 365 SharePoint
IF(@CS_SPVERSION < 14)
BEGIN
SET @NumberOfSharepointObjects = ISNULL((SELECT  count( DISTINCT P.attrName )
FROM APP_BackupSetProp P (READUNCOMMITTED)
INNER JOIN APP_Application APP (READUNCOMMITTED) 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), 0) 
END
ELSE
BEGIN
CREATE TABLE #tmpO365SharePointClients (clientid INT)
EXEC LicGetO365Sharepoint ''#tmpO365SharePointClients''
SET @NumberOfSharepointObjects = ISNULL((SELECT  count( DISTINCT P.attrName )
	FROM APP_BackupSetProp P (READUNCOMMITTED)
	INNER JOIN APP_Application APP (READUNCOMMITTED) 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*/
	INNER JOIN #tmpO365SharePointClients C ON C.clientid = APP.clientId
	WHERE P.attrType = 137 AND P.modified = 0), 0)
END


--Total users for Gmail gdrive & oneDrive 							
	IF object_id(''tempdb.dbo.#tmpmailBoxCountTable'') is not null 
         DROP TABLE #tmpmailBoxCountTable 
     CREATE TABLE #tmpmailBoxCountTable (clientId INT, appTypeId INT, instanceId INT, SubClientId INT, mailbox NVARCHAR(1024), InstanceType INT)

;WITH Gmail_CTE (clientId,appTypeId,instance,subClientId,instanceVal)
     AS
     (
         SELECT APP.clientId, APP.appTypeId, APP.instance, APP.id, Prop.attrVal
         FROM APP_Application APP (READUNCOMMITTED)
         JOIN APP_InstanceProp Prop (READUNCOMMITTED) ON APP.instance = Prop.componentNameId AND App.subclientStatus & 0x02 != 0x02 /*UNINSTALLED*/ AND App.subclientStatus & 0x04 != 0x04 /*DELETED*/ AND App.subclientStatus & 0x00020 != 0x00020 /*HIDDEN*/
             AND attrName = ''Cloud Apps Instance Type'' AND LEN(attrVal) > 0  AND attrVal in (''1'',''2'',''7'')    
     )	 

	 INSERT INTO #tmpmailBoxCountTable
     SELECT DISTINCT clientId,appTypeId,instance,subClientId,SP.attrName, instanceVal
     FROM Gmail_CTE
     JOIN APP_SubClientProp SP (READUNCOMMITTED) ON SP.componentNameId = subClientId AND SP.modified=0 AND SP.attrType = 134 --PROPERTY_CLOUD_APPS
         AND LEN(SP.attrName) > 0
         AND (CASE WHEN @CS_SPVERSION > 12 AND instanceVal = ''7'' and SP.attrVal like ''%::oq'' THEN 1 		 
	           WHEN @CS_SPVERSION <= 12 AND instanceVal = ''7'' THEN 1
                   WHEN instanceVal <> ''7'' THEN 1  
                   ELSE 0 END) = 1
				   				   
	SET @oneDriveCount	= ISNULL((select count(distinct mailbox) from #tmpmailBoxCountTable where InstanceType = 7), 0)
	SET @gDriveCount	= ISNULL((select count(distinct mailbox) from #tmpmailBoxCountTable where InstanceType = 2), 0)
	SET @gmailCount	= ISNULL((select count(distinct mailbox) from #tmpmailBoxCountTable where InstanceType = 1), 0)


DECLARE @isMSP INT = 0
     SELECT 
        @isMSP = CAST(value AS INT)
     FROM GXGlobalParam WITH(NOLOCK)
     WHERE
         name = N''IsMSPCommcell''

--count of distinct users logged on to outlook provider.
DECLARE @outlookProviderUsersCount BIGINT = 0
SELECT @outlookProviderUsersCount = count(distinct b.login) From UMSessionAudit a (nolock) join umusers b (nolock) on a.userid = b.id WHERE consoleType = 6

--------------------------------------------results----------------------
--licensing info
select 
--Exchange info
	@totalNoOfMBInAgent AS V2MailboxTotal, 
	@OnPremMBProtected as V2MailboxOnPremises,  
	@O365MBProtected as V2MailboxO365,
	@totalNoOfActiveMBInClassicAgent AS MBClassicActive, 
	@NoOfOffice365MbIfAny AS MBO365Classic, 
	@NoOfDeconfClassicAgent as MBClassicInactive, 
	@NoOfJournalMBs as Journal, 
	@NoOfContenstoreMBs AS SMTP, 
	@isMSP as IsMSP, 
	@totalNoOfActiveMBInComplArchiver AS ActiveExchComplArchMB,
	@NoOfDeconfiguredMBInComplArchiver as InActiveExchComplArchMB,
	@outlookProviderUsersCount as TotalOutlookProviderUsers, 
--Sharepoint\CloudApps info
	@NumberOfSharepointObjects as SharepointO365,
	@oneDriveCount as OneDrive, 
	@GDriveCount as GoogleDrive, 
	@gmailCount as GoogleMail

IF OBJECT_ID (''tempdb.dbo.#tmpMbTable'') IS NOT NULL  DROP TABLE #tmpMbTable
IF OBJECT_ID (''tempdb.dbo.#tmpApplication '') IS NOT NULL   DROP TABLE #tmpApplication 
IF object_id(''tempdb.dbo.#tmpmailBoxCountTable'') is not null	 DROP TABLE #tmpmailBoxCountTable
IF object_id(''tempdb.dbo.#tmpO365SharePointClients'') IS NOT null DROP TABLE #tmpO365SharePointClients ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [V2MailboxTotal] AS '@V2MailboxTotal',[V2MailboxOnPremises] AS '@V2MailboxOnPremises',[V2MailboxO365] AS '@V2MailboxO365',[MBClassicActive] AS '@MBClassicActive',[MBO365Classic] AS '@MBO365Classic',[MBClassicInactive] AS '@MBClassicInactive',[Journal] AS '@Journal',[SMTP] AS '@SMTP',[IsMSP] AS '@IsMSP',[ActiveExchComplArchMB] AS '@ActiveExchComplArchMB',[InActiveExchComplArchMB] AS '@InActiveExchComplArchMB',[TotalOutlookProviderUsers] AS '@TotalOutlookProviderUsers',[SharepointO365] AS '@SharepointO365',[OneDrive] AS '@OneDrive',[GoogleDrive] AS '@GoogleDrive',[GoogleMail] AS '@GoogleMail' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10220'), 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'))