DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10314
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[DLOClients] int,[DLOUserCount] int,[SyncUserCount] int,[DLPUserCount] int,[PrivacyUserCount] int,[ExplorerPluginUserCount] int,[User Centric Count] int
)


IF @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SELECT
         (SELECT COUNT(*) FROM APP_Client WHERE status & 4096 = 4096) AS ''DLOClients''
        ,(SELECT COUNT(a.userid) from
                     (SELECT DISTINCT userId ''userid'' from UMUserandGroupEntityAssociation where entityType = 2009 and objectId IN (SELECT id FROM APP_Client WHERE status & 4096 = 4096)  and userid <> 0
                     union
                     SELECT DISTINCT UG.userId ''userid'' from UMUserandGroupEntityAssociation UM INNER JOIN UMUserGroup UG on UM.groupId = UG.groupId where entityType = 2009 and objectId IN (SELECT id FROM APP_Client WHERE status & 4096 = 4096) and UM.userid = 0 and UM.providerId = 0
                     union
                     SELECT DISTINCT UG.userId ''userid'' from UMUserandGroupEntityAssociation UM INNER JOIN UMDSUserGroup UG on UM.groupId = UG.groupId where entityType = 2009 and objectId IN (SELECT id FROM APP_Client WHERE status & 4096 = 4096) and UM.userid = 0 and UM.providerId <> 0
                     union
                     SELECT DISTINCT UG.userId ''userid'' from UMUserandGroupEntityAssociation UM INNER JOIN UMDSGroupMaps UGM on UM.groupId = UGM.umgroupId INNER JOIN UMDSUserGroup UG ON UGM.umDSgroupId = UG.groupId  where entityType = 2009 and objectId IN (SELECT id FROM APP_Client WHERE status & 4096 = 4096) and UM.userid = 0 and UM.providerId = 0
                     ) a) AS ''DLOUserCount''
        ,(SELECT COUNT(DISTINCT ownerId) ''sync folders'' FROM
        App_SyncCloudFolder WITH (NOLOCK)
        WHERE flag&4 = 0 AND flag&8 = 0 AND flag&0x10 = 0 AND flag&0x20 = 0) AS ''SyncUserCount''
        ,(SELECT COUNT(a.userid) from
                     (SELECT DISTINCT userId ''userid'' from UMUserandGroupEntityAssociation where entityType = 2009 and objectId IN (SELECT DISTINCT DLPClients.id
                     FROM
                     (
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientGroupAssoc Assoc WITH(NOLOCK) ON cli.id = Assoc.clientId
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE Assoc.clientGroupId IN (SELECT groupId
                                                                                   FROM DlpGroupProp WITH(NOLOCK)
                                                                                  WHERE groupId IN (SELECT groupId FROM DlpGroupProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                                                  AND attrName = ''dlpContents'' AND REPLACE(attrVal, '';'', '''') <> '''')
                              AND prop.attrName = ''enableDLP'' AND attrVal = ''0''
                              AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                              UNION
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE prop.componentNameId IN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                       AND prop.attrName = ''dlpContents'' AND REPLACE(prop.attrVal, '';'', '''') <> ''''
                                                       AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                     ) AS DLPClients
                     )  and userid <> 0
              UNION
                     SELECT DISTINCT UG.userId ''userid'' FROM UMUserandGroupEntityAssociation UM INNER JOIN UMUserGroup UG ON UM.groupId = UG.groupId WHERE entityType = 2009 AND objectId IN (SELECT DISTINCT DLPClients.id
                     FROM
                     (
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientGroupAssoc Assoc WITH(NOLOCK) ON cli.id = Assoc.clientId
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE Assoc.clientGroupId IN (SELECT groupId
                                                                                   FROM DlpGroupProp WITH(NOLOCK)
                                                                                  WHERE groupId IN (SELECT groupId FROM DlpGroupProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                                                   AND attrName = ''dlpContents'' AND REPLACE(attrVal, '';'', '''') <> '''')
                              AND prop.attrName = ''enableDLP'' AND attrVal = ''0''
                              AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                              UNION
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE prop.componentNameId IN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                       AND prop.attrName = ''dlpContents'' AND REPLACE(prop.attrVal, '';'', '''') <> ''''
                                                       AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                     ) AS DLPClients
                     ) AND UM.userid = 0 AND UM.providerId = 0
              UNION
                     SELECT DISTINCT UG.userId ''userid'' from UMUserANDGroupEntityAssociation UM INNER JOIN UMDSUserGroup UG on UM.groupId = UG.groupId where entityType = 2009 AND objectId IN (SELECT DISTINCT DLPClients.id
                     FROM
                     (
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientGroupAssoc Assoc WITH(NOLOCK) ON cli.id = Assoc.clientId
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE Assoc.clientGroupId IN (SELECT groupId
                                                                                   FROM DlpGroupProp WITH(NOLOCK)
                                                                                  WHERE groupId IN (SELECT groupId FROM DlpGroupProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                                                  AND attrName = ''dlpContents'' AND REPLACE(attrVal, '';'', '''') <> '''')
                              AND prop.attrName = ''enableDLP'' AND attrVal = ''0''
                              AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                            
                              UNION
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE prop.componentNameId IN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                       AND prop.attrName = ''dlpContents'' AND REPLACE(prop.attrVal, '';'', '''') <> ''''
                                                       AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                     ) AS DLPClients
                     ) AND UM.userid = 0 AND UM.providerId <> 0
              UNION
                     SELECT DISTINCT UG.userId ''userid'' from UMUserANDGroupEntityAssociation UM INNER JOIN UMDSGroupMaps UGM on UM.groupId = UGM.umgroupId INNER JOIN UMDSUserGroup UG ON UGM.umDSgroupId = UG.groupId  where entityType = 2009 AND objectId IN
                     (SELECT DISTINCT DLPClients.id
                     FROM
                     (
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientGroupAssoc Assoc WITH(NOLOCK) ON cli.id = Assoc.clientId
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE Assoc.clientGroupId IN (SELECT groupId
                                                                                   FROM DlpGroupProp WITH(NOLOCK)
                                                                                  WHERE groupId IN (SELECT groupId FROM DlpGroupProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                                                  AND attrName = ''dlpContents'' AND REPLACE(attrVal, '';'', '''') <> '''')
                              AND prop.attrName = ''enableDLP'' AND attrVal = ''0''
                              AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                            
                              UNION
                              SELECT cli.id
                              FROM APP_Client cli WITH(NOLOCK)
                              INNER JOIN APP_ClientProp prop WITH(NOLOCK) ON cli.id = prop.componentNameId
                              WHERE prop.componentNameId IN (SELECT componentNameId FROM APP_ClientProp WITH(NOLOCK) WHERE attrname = ''enableDLP'' AND attrVal = ''1'')
                                                       AND prop.attrName = ''dlpContents'' AND REPLACE(prop.attrVal, '';'', '''') <> ''''
                                                       AND (cli.status & 0xEFFF) = 0 AND cli.status != 0
                     ) AS DLPClients
                     ) AND UM.userid = 0 AND UM.providerId = 0
                     ) a) AS ''DLPUserCount''
              ,(SELECT Count(*) FROM APP_ClientProp WHERE attrName = ''Enable Data Security'' AND attrVal = ''1'' AND modified = 0) AS ''PrivacyUserCount''
              ,(SELECT COUNT(a.userid) from
                     (SELECT DISTINCT userId ''userid'' from UMUserANDGroupEntityAssociation where entityType = 2009 AND objectId IN (SELECT DISTINCT componentNameId FROM APP_ClientProp WHERE attrName = ''FS Explorer Plugin Enabled Status'' AND attrVal = ''1'' AND modified = 0)  AND userid <> 0
                     UNION
                     SELECT DISTINCT UG.userId ''userid'' from UMUserANDGroupEntityAssociation UM INNER JOIN UMUserGroup UG on UM.groupId = UG.groupId where entityType = 2009 AND objectId IN (SELECT DISTINCT componentNameId FROM APP_ClientProp WHERE attrName = ''FS Explorer Plugin Enabled Status'' AND attrVal = ''1'' AND modified = 0) AND UM.userid = 0 AND UM.providerId = 0
                     UNION
                     SELECT DISTINCT UG.userId ''userid'' from UMUserANDGroupEntityAssociation UM INNER JOIN UMDSUserGroup UG on UM.groupId = UG.groupId where entityType = 2009 AND objectId IN (SELECT DISTINCT componentNameId FROM APP_ClientProp WHERE attrName = ''FS Explorer Plugin Enabled Status'' AND attrVal = ''1'' AND modified = 0) AND UM.userid = 0 AND UM.providerId <> 0
                     UNION
                     SELECT DISTINCT UG.userId ''userid'' from UMUserANDGroupEntityAssociation UM INNER JOIN UMDSGroupMaps UGM on UM.groupId = UGM.umgroupId INNER JOIN UMDSUserGroup UG ON UGM.umDSgroupId = UG.groupId  where entityType = 2009 AND objectId IN (SELECT DISTINCT componentNameId FROM APP_ClientProp WHERE attrName = ''FS Explorer Plugin Enabled Status'' AND attrVal = ''1'' AND modified = 0) AND UM.userid = 0 AND UM.providerId = 0
                     ) a) AS ''ExplorerPluginUserCount'',
(select count(componentnameid) from APP_ClientProp where (attrname = ''User Centric Client'' and attrVal = 1 and modified =0)) AS ''User Centric Count'' ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [DLOClients] AS '@DLOClients',[DLOUserCount] AS '@DLOUserCount',[SyncUserCount] AS '@SyncUserCount',[DLPUserCount] AS '@DLPUserCount',[PrivacyUserCount] AS '@PrivacyUserCount',[ExplorerPluginUserCount] AS '@ExplorerPluginUserCount',[User Centric Count] AS '@Col_822351006' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10314'), 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'))