DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10254
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[TenantCount] int,[EmailDomainCount] int,[GroupCount] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SET NOCOUNT ON
DECLARE @EmailDomain INT 
DECLARE @TenantCount INT
DECLARE @GroupCount INT =5
DECLARE @STRSQL NVARCHAR(max)=''SELECT @TenantCount=COUNT(*) FROM UMDSProviders where serviceType =5''

DECLARE @majorVer int
DECLARE @CSVersionInfo FLOAT
SELECT TOP 1 @CSVersionInfo = CAST(release AS FLOAT) FROM simAllGalaxyRel WITH (NOLOCK) ORDER BY id DESC
SELECT @majorVer = MAX(HighestSP) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2  

exec sp_executesql @STRSQL, N''@TenantCount int out'', @TenantCount out
 

IF OBJECT_ID(''tempdb.dbo.#Filter'') is NOT NULL DROP TABLE #Filter
CREATE TABLE #Filter (EmailStr VARCHAR(100))
 INSERT #Filter VALUES (''Test''),(''dummy''),(''disabled''),(''hotmail''),(''outlook''),(''gmail''),(''yahoo''),(''aol''),(''comcast''),(''verizon'')
SELECT @EmailDomain=COUNT(Domain) FROM 
(
SELECT RIGHT(Email, LEN(Email)-CHARINDEX(''@'', email)) Domain,
       COUNT(Email) EmailCount
FROM umusers
WHERE LEN(Email) > 0 and enabled=1
GROUP BY RIGHT(Email, LEN(Email)-CHARINDEX(''@'', email))
HAVING COUNT(Email) >5
) Emails LEFT JOIN #Filter F ON Domain LIKE ''%''+EmailStr+''%''
WHERE F.EmailStr is  NULL 

IF (@CSVersionInfo < 11) or ( @CSVersionInfo=11 and  @majorVer <=6)
SELECT @STRSQL =''SELECT @GroupCount=COUNT(G.name) FROM UMGROUPS G INNER JOIN UMUserGroup UG On Ug.groupId =g.id 
				     INNER JOIN UMUsers U On U.id =ug.userId 
					WHERE U.enabled =1 AND G.Name NOT IN (''''MASTER'''',''''CV_Restricted_Visibility'''',''''View All'''') GROUP BY G.Name Having Count(*) > 5 ''
				 
ELSE
SELECT @STRSQL =''SELECT @GroupCount=COUNT(G.name) FROM UMGROUPS G INNER JOIN UMUserGroup UG On Ug.groupId =g.id 
				     INNER JOIN UMUsers U On U.id =ug.userId 
					WHERE U.enabled =1 AND G.Name NOT IN (''''MASTER'''',''''CV_Restricted_Visibility'''',''''View All'''') and g.UMDSProviderid=0 GROUP BY G.Name Having Count(*) > 5 ''
				
 
exec sp_executesql @STRSQL, N''@GroupCount int out'', @GroupCount out

SELECT ISNULL(@TenantCount,0) TenantCount,ISNULL(@EmailDomain,0) EmailDomainCount,ISNULL(@GroupCount,0) GroupCount ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [TenantCount] AS '@TenantCount',[EmailDomainCount] AS '@EmailDomainCount',[GroupCount] AS '@GroupCount' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10254'), 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'))