DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10155
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[DatasourceName] nvarchar(max),[DatasourceCount] int,[SyncDataSourceCount] int,[TotalCount] bigint,[TotalSizeInBytes] bigint,[MaxTotalSizeInBytes] bigint,[MaxTotalDocs] bigint,[FilerSourceCount] int,[FilerSizeAnalyzed] bigint
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; DECLARE @SQLSTR NVARCHAR(MAX)
SET @SQLSTR = '';with CTE(datasourceId,statusInfo) as 
(
SELECT T.datasourceId, CONVERT(XML,T.PropertyValue)
FROM SEDataSourceProperty as T WITH (NOLOCK) 
INNER JOIN SEProperty AS P ON T.PropertyId = P.PropertyId AND P.PropertyName = ''''statusinfo''''
WHERE T.PropertyValue is not null AND T.PropertyValue != ''''''''
),
CTE2 AS(
SELECT CTE.datasourceId,
ISNULL(m.c.value(''''@TotalDocs'''',''''BIGINT''''),0) as TotalCount,
ISNULL(m.c.value(''''@TotalSizeInBytes'''',''''BIGINT''''),0) as TotalSizeInBytes,
ISNULL(m.c.value(''''@LastSyncTime'''',''''BIGINT''''),0) as LastSyncTime,
ISNULL(m.c.value(''''@MaxTotalDocs'''',''''BIGINT''''),0) as MaxTotalDocs,
ISNULL(m.c.value(''''@LastModified'''',''''BIGINT''''),0) as LastModified, 
ISNULL(m.c.value(''''@MaxTotalSizeInBytes'''',''''BIGINT''''),0) as MaxTotalSizeInBytes,
ISNULL(m.c.value(''''@MaxTotalSyncTime'''',''''BIGINT''''),0) as MaxTotalSyncTime,
ISNULL(m.c.value(''''@MaxSizeSyncTime'''',''''BIGINT''''),0) as MaxSizeSyncTime,
ISNULL(m.c.value(''''(TypeStatusInfo/FileInfo/@SourceCount)[1]'''',''''INT''''),0) as FSSourceCount,
ISNULL(m.c.value(''''(TypeStatusInfo/FileInfo/@SizeAnalyzed)[1]'''',''''BIGINT''''),0) as FSSizeAnalyzedSourceCount
FROM CTE outer apply CTE.statusInfo.nodes(''''/StatusInfo'''') as m(c)
)
SELECT UPPER(DST.TypeDisplayName) DatasourceName , COUNT(SE.DataSourceId) DatasourceCount, COUNT(CTE2.datasourceId) SyncDataSourceCount, 
ISNULL(SUM(CTE2.TotalCount),0) TotalCount, ISNULL(SUM(CTE2.TotalSizeInBytes),0) TotalSizeInBytes, 
ISNULL(SUM(CTE2.MaxTotalSizeInBytes),0) MaxTotalSizeInBytes, ISNULL(SUM(CTE2.MaxTotalDocs),0) MaxTotalDocs, 
ISNULL(SUM(CTE2.FSSourceCount),0) AS FilerSourceCount, ISNULL(SUM(CTE2.FSSizeAnalyzedSourceCount),0) AS FilerSizeAnalyzed
FROM SEDataSource AS SE WITH (NOLOCK) 
INNER JOIN SEDataSourceType AS DST ON SE.DataSourceType = DST.TypeId
LEFT JOIN CTE2 ON CTE2.datasourceId = SE.DataSourceId
GROUP BY DST.TypeDisplayName
HAVING COUNT(CTE2.datasourceId) > 0
ORDER BY TotalCount DESC''

DECLARE @relId INT
SELECT @relId = MAX(id) FROM simAllGalaxyRel 
IF (@relId >= 16) --V1 and above
EXEC sp_executesql @SQLSTR
--ELSE 
--SELECT ''Not Available'' AS DatasourceName,0 AS DatasourceCount,0 AS SyncDataSourceCount,0 AS TotalCount,0 AS TotalSizeInBytes,0 AS MaxTotalSizeInBytes,0 AS MaxTotalDocs,0 AS FilerSourceCount,0 AS FilerSizeAnalyzed ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [DatasourceName] AS '@DatasourceName',[DatasourceCount] AS '@DatasourceCount',[SyncDataSourceCount] AS '@SyncDataSourceCount',[TotalCount] AS '@TotalCount',[TotalSizeInBytes] AS '@TotalSizeInBytes',[MaxTotalSizeInBytes] AS '@MaxTotalSizeInBytes',[MaxTotalDocs] AS '@MaxTotalDocs',[FilerSourceCount] AS '@FilerSourceCount',[FilerSizeAnalyzed] AS '@FilerSizeAnalyzed' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10155'), 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'))