

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Sp/RptScalereportCommcellStatusSurvey.sp] ---------- 

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/CloudDB/Sp/RptScalereportCommcellStatusSurvey.sp,v $ $Id: RptScalereportCommcellStatusSurvey.sp,v 1.15.48.12.4.1 2021/06/29 20:35:08 dkraplanee Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='RptScalereportCommcellStatusSurvey')
BEGIN
	print '>>> Drop Stored Procedure: RptScalereportCommcellStatusSurvey <<<'
	drop procedure RptScalereportCommcellStatusSurvey
END
IF EXISTS (select * from GxQscripts where name='RptScalereportCommcellStatusSurvey')
	delete from GxQscripts where name = 'RptScalereportCommcellStatusSurvey'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptScalereportCommcellStatusSurvey')
	delete from GXDBVersions where aliasname = 'RptScalereportCommcellStatusSurvey'
GO
print '... Creating Procedure: RptScalereportCommcellStatusSurvey'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptScalereportCommcellStatusSurvey
  @i_UserId INT = NULL,
  @i_GroupId INT = NULL,
  @blaggregate INT = 0,
  @blLatestSurvey INT = 1,
  @i_CommServUniqueID INT = 0,
  @i_LogDateStart DATETIME = NULL,
  @i_LogDateEND DATETIME = NUll,
  @i_GroupType VARCHAR(128) = 'HourlyFormat'
AS
DECLARE @warningThreshold INT, @criticalThreshold INT
DECLARE @strGood		VARCHAR(10) = '1_Good'
DECLARE @strwarning		VARCHAR(10) = '3_Warning'
DECLARE @strCritical	VARCHAR(10) = '4_Critical'
IF (@blaggregate = 1 AND @i_CommServUniqueID = 0)
	RETURN
SET NOCOUNT ON
DECLARE @i_QueryId BIGINT = 7
DECLARE @surveyResultId bigint
set @surveyResultId =	(select SurverResultId from dbo.cf_SurveyResultFuncV1(@i_QueryId, @i_CommServUniqueID, @blLatestSurvey, @i_LogDateStart, @i_LogDateEND,@i_UserId,@i_GroupId) )
--Health Queries affecting the client scale limit
DECLARE @tblClientScaleHealthQueries TABLE(HealthQueryId INT)
--Scale Params affecting the client scale limit
DECLARE @tblClientScaleParams TABLE(ScaleParamId INT)
INSERT INTO @tblClientScaleHealthQueries
SELECT QueryId FROM dbo.RptGetMetricsQueryFlags(0) WHERE QueryEnabled = 1 AND (QueryFlags & 4) != 0 AND QueryId IN (27, 16, 17, 24, 64, 96, 65, 156)
INSERT INTO @tblClientScaleParams
SELECT ParamId FROM cf_SurveyHealthParam WITH(NOLOCK) WHERE QueryId = 7 AND ParamId IN (701, 703, 723, 724)
IF ( @i_GroupType = 'HourlyFormat' )
BEGIN
		select GroupName as 'Group<DONOTSHOW>',
			   ParameterName as 'Parameter',
			   status as 'Status<SCALE_STATUS>',
			   case when LastHour is NULL then outcome
					else  cast(LastHour  as varchar(15)) + ' ' + unit
					end as 'Last 1 hour',
			   case when Last24Hour is NULL then remarks
					else   cast( Last24Hour  as varchar(15)) + ' ' + unit
				END as 'Last 24 hours<SCALE_REMARKS_LINK>',
				ParamId AS 'ParamId<DONOTSHOW>'
		FROM dbo.RptGetScaleHealthFunc(@i_UserId,@i_GroupId,@blaggregate,@blLatestSurvey,@i_CommServUniqueID, @i_LogDateStart, @i_LogDateEND,@i_GroupType,0)
		order by ParamId
END
ELSE IF ( @i_GroupType = 'DatabaseFormat')
BEGIN
	select GroupName as 'Group<DONOTSHOW>',
		   ParameterName as 'Parameter',
		   status as 'Status<SCALE_STATUS>',
		   case when ParamId = 728 then CONVERT(VARCHAR, dbo.getDateTime(cast(LastHour as int)), 120)
		   else
				(case when LastHour is NULL then outcome
				else cast( LastHour as varchar(15) ) + ' ' + unit
				end)
		   end
    	   as 'Outcome',
		   remarks as 'Remarks<SCALE_REMARKS_LINK>',
		   ParamId AS 'ParamId<DONOTSHOW>'
	FROM dbo.RptGetScaleHealthFunc(@i_UserId,@i_GroupId,@blaggregate,@blLatestSurvey,@i_CommServUniqueID, @i_LogDateStart, @i_LogDateEND,@i_GroupType,0)
		where GroupName is not null
		order by ParamId
END
ELSE IF (@i_GroupType = 'Client')
BEGIN
	DECLARE @tblScaleParamHealth TABLE(ParamId int, HealthStatus nvarchar(max))
	DECLARE @tblParametersTemp TABLE (Monitor NVARCHAR(MAX), Result NVARCHAR(MAX), Status NVARCHAR(MAX), MonitorId INT)
	INSERT INTO @tblParametersTemp
	EXEC RptGetServerHealth @i_CommservUniqueId, @i_UserId, @i_GroupId, default, 7
	INSERT INTO @tblScaleParamHealth
	SELECT MonitorId, Status
	FROM @tblParametersTemp HF
	INNER JOIN @tblClientScaleHealthQueries Q ON Q.HealthQueryId = HF.MonitorId
	INSERT INTO @tblScaleParamHealth
	SELECT ParamId, Status
	FROM dbo.RptGetScaleHealthFunc(@i_UserId, @i_GroupId, @blAggregate, @blLatestSurvey, @i_CommservUniqueId, @i_LogDateStart, @i_LogDateEnd, 'HourlyFormat', 0) SF
	INNER JOIN @tblClientScaleParams P ON P.ScaleParamId = SF.ParamId
	INSERT INTO @tblScaleParamHealth
	SELECT ParamId, Status
	FROM dbo.RptGetScaleHealthFunc(@i_UserId, @i_GroupId, @blAggregate, @blLatestSurvey, @i_CommservUniqueId, @i_LogDateStart, @i_LogDateEnd, 'DatabaseFormat', 0) SF
	INNER JOIN @tblClientScaleParams P ON P.ScaleParamId = SF.ParamId
	WHERE (SF.ParamId != 723 OR SF.ParameterName = 'Commserv Database Size')
	DECLARE @ClientScaleRemark NVARCHAR(MAX) = 'The Client Scale Limit can be increased.'
	IF EXISTS(SELECT TOP 1 * FROM @tblScaleParamHealth WHERE HealthStatus IN (@strwarning, @strCritical))
		SET @ClientScaleRemark = 'The Client Scale Limit cannot be increased. One or more parameters that affect the Client Scale Limit are in warning or critical state. <RECSLInt>Client Scale Limit Parameters</RECSLInt>'
	select GroupName as 'Group<DONOTSHOW>',
		   ParameterName as 'Parameter',
		   status as 'Status<SCALE_STATUS>',
		   case when LastHour is NULL then outcome
				else cast( LastHour as varchar(15) ) + ' ' + unit
				end  as 'Outcome',
		   @ClientScaleRemark as 'Remarks<SCALE_REMARKS_LINK>',
		   ParamId AS 'ParamId<DONOTSHOW>'
	FROM dbo.RptGetScaleHealthFunc(@i_UserId,@i_GroupId,@blaggregate,@blLatestSurvey,@i_CommServUniqueID, @i_LogDateStart, @i_LogDateEND,@i_GroupType,0)
		where GroupName is not null
END
ELSE IF (@i_GroupType = 'ClientScaleParams')
BEGIN
	DECLARE @tblParameters TABLE (ParameterName NVARCHAR(MAX), HealthResult NVARCHAR(MAX), HealthStatus NVARCHAR(MAX), ParamId INT)
	DECLARE @tblParametersTempTbl TABLE (Monitor NVARCHAR(MAX), Result NVARCHAR(MAX), Status NVARCHAR(MAX), MonitorId INT)
	INSERT INTO @tblParametersTempTbl
	EXEC RptGetServerHealth @i_CommservUniqueId,@i_UserId,@i_GroupId,default,7
	INSERT INTO @tblParameters
	SELECT Monitor, Result, Status, MonitorId
	FROM @tblParametersTempTbl HF
	INNER JOIN @tblClientScaleHealthQueries Q ON Q.HealthQueryId = HF.MonitorId
	INSERT INTO @tblParameters
	SELECT ParameterName,
		CASE WHEN Last24Hour IS NULL THEN outcome ELSE  'In Last 24 Hours: ' + CAST(LastHour  as VARCHAR(15)) + ' ' + unit END,
		Status, ParamId
	FROM dbo.RptGetScaleHealthFunc(@i_UserId, @i_GroupId, @blAggregate, @blLatestSurvey, @i_CommservUniqueId, @i_LogDateStart, @i_LogDateEnd, 'HourlyFormat', 0) SF
	INNER JOIN @tblClientScaleParams P ON P.ScaleParamId = SF.ParamId
	ORDER BY SF.Id
	INSERT INTO @tblParameters
	SELECT ParameterName,
		CASE WHEN LastHour IS NULL THEN remarks ELSE CAST( LastHour as VARCHAR(15) ) + ' ' + unit END,
		Status, ParamId
	FROM dbo.RptGetScaleHealthFunc(@i_UserId, @i_GroupId, @blAggregate, @blLatestSurvey, @i_CommservUniqueId, @i_LogDateStart, @i_LogDateEnd, 'DatabaseFormat', 0) SF
	INNER JOIN @tblClientScaleParams P ON P.ScaleParamId = SF.ParamId
	WHERE (SF.ParamId != 723 OR SF.ParameterName = 'Commserv Database Size')
	ORDER BY SF.Id
	--As far as scalability is concerned, info = good.
	UPDATE @tblParameters
	SET HealthStatus = @strGood
	WHERE HealthStatus NOT IN (@strCritical, @strWarning, @strGood)
	SELECT 'Client Scale Limit Parameters' AS 'Group<DONOTSHOW>',
		 ParameterName AS Parameter,
		HealthStatus  as 'Status<SCALE_STATUS>',
		HealthResult as Outcome,
		ParamId AS 'ParamId<DONOTSHOW>'
	FROM @tblParameters
END
ELSE IF ( @i_GroupType = 'Top 10 Largest Tables by Size' or @i_GroupType = 'Top 10 Largest Tables by Row Count' )
BEGIN
declare @dbprop varchar(256)
if (@i_GroupType = 'Top 10 Largest Tables by Size')
	set @dbprop = 'Largest Tables by Size'
else
	set @dbprop = 'Largest Tables by Rows'
declare @tableData xml = (
select cast(csInfo.Unit as XML)
FROM cf_SurveyScaleReportInfo AS csInfo WITH (NOLOCK)
		WHERE  csInfo.SurveyResultId = @surveyResultid
		and csInfo.Property = @dbprop
)
declare @excludedtableData xml = (
select cast(csInfo.Unit as XML)
FROM cf_SurveyScaleReportInfo AS csInfo WITH (NOLOCK)
		WHERE  csInfo.SurveyResultId = @surveyResultid
		and csInfo.Property = 'Excluded Tables Rows and Size info'
)
if (@i_GroupType = 'Top 10 Largest Tables by Size')
begin
	declare @warningSizeMB DECIMAL(32,2),@criticalSizeMB DECIMAL(32,2), @bigTableWarngSizeMB DECIMAL(32,2) = 5.0*1024, @bigTableCritclSizeMB DECIMAL(32,2) = 10.0*1024
	select @warningSizeMB = warningThreshold,@criticalSizeMB = criticalThreshold FROM cf_surveyHealthParam WHERE paramId = 724
	select
	'Top 10 Largest Tables by Size' AS 'Group',
	T.n.value('@TableName', 'varchar(100)') as 'Name',
	cast(T.n.value('@DataSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Data Size<SIZEMB>',
	cast(T.n.value('@IndexSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Index Size<SIZEMB>',
	T.n.value('@TotalRows', 'bigint') as 'Total Row Count',
	cast(T.n.value('@ReservedSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Reserved Size<SIZEMB><HIDDEN>',
	cast(T.n.value('@TotalSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Total Size<SIZEMB><HIDDEN>',
	CASE WHEN (T.n.value('@DataSizeinKB', 'bigint')/1024.0) < @warningSizeMB THEN @strGood
		 WHEN (T.n.value('@DataSizeinKB', 'bigint')/1024.0) BETWEEN @warningSizeMB AND @criticalSizeMB THEN @strwarning
		 ELSE @strCritical END 'Status<HEALTH_STATUS>'
	FROM @tableData.nodes('/TableData') AS T(n)
	UNION ALL
	SELECT 'Big Tables' AS 'Group',
	T.n.value('@TableName', 'varchar(100)') as 'Name',
	cast(T.n.value('@DataSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Data Size<SIZEMB>',
	cast(T.n.value('@IndexSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Index Size<SIZEMB>',
	T.n.value('@TotalRows', 'bigint') as 'Total Row Count',
	cast(T.n.value('@ReservedSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Reserved Size<SIZEMB><HIDDEN>',
	cast(T.n.value('@TotalSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Total Size<SIZEMB><HIDDEN>',
	CASE WHEN (T.n.value('@DataSizeinKB', 'bigint')/1024.0) < @bigTableWarngSizeMB THEN @strGood
		 WHEN (T.n.value('@DataSizeinKB', 'bigint')/1024.0) BETWEEN @bigTableWarngSizeMB AND @bigTableCritclSizeMB THEN @strwarning
		 ELSE @strCritical END 'Status<HEALTH_STATUS>'
	FROM @excludedtableData.nodes('/TableData') AS T(n)
	order by [Group] desc,[Data Size<SIZEMB>] desc
end
else
begin
	declare @warningRowCnt BIGINT,@criticalRowCnt BIGINT, @bigTableWarngRowCnt BIGINT = 10000000, @bigTableCritclRowCnt BIGINT = 20000000
	select @warningRowCnt = warningThreshold,@criticalRowCnt = criticalThreshold FROM cf_surveyHealthParam WHERE paramId = 725
	select
	'Top 10 Largest Tables by Row Count' AS 'Group',
	T.n.value('@TableName', 'varchar(100)') as 'Name',
	cast(T.n.value('@DataSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Data Size<SIZEMB>',
	cast(T.n.value('@IndexSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Index Size<SIZEMB>',
	T.n.value('@TotalRows', 'bigint') as 'Total Row Count',
	cast(T.n.value('@ReservedSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Reserved Size<SIZEMB><HIDDEN>',
	cast(T.n.value('@TotalSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Total Size<SIZEMB><HIDDEN>',
	CASE WHEN T.n.value('@TotalRows', 'bigint') < @warningRowCnt THEN @strGood
		 WHEN T.n.value('@TotalRows', 'bigint') BETWEEN @warningRowCnt AND @criticalRowCnt THEN @strwarning
		 ELSE @strCritical END 'Status<HEALTH_STATUS>'
	FROM @tableData.nodes('/TableData') AS T(n)
	UNION ALL
	SELECT 'Big Tables' AS 'Group',
	T.n.value('@TableName', 'varchar(100)') as 'Name',
	cast(T.n.value('@DataSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Data Size<SIZEMB>',
	cast(T.n.value('@IndexSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Index Size<SIZEMB>',
	T.n.value('@TotalRows', 'bigint') as 'Total Row Count',
	cast(T.n.value('@ReservedSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Reserved Size<SIZEMB><HIDDEN>',
	cast(T.n.value('@TotalSizeinKB', 'bigint')/1024.0 as decimal(10,2)) as 'Total Size<SIZEMB><HIDDEN>',
	CASE WHEN T.n.value('@TotalRows', 'bigint') < @bigTableWarngRowCnt THEN @strGood
		 WHEN T.n.value('@TotalRows', 'bigint') BETWEEN @bigTableWarngRowCnt AND @bigTableCritclRowCnt THEN @strwarning
		 ELSE @strCritical END 'Status<HEALTH_STATUS>'
	FROM @excludedtableData.nodes('/TableData') AS T(n)
	order by [Group] desc,[Total Row Count] desc
end
END
ELSE IF ( @i_GroupType = 'Job Load in last 24 hours'  )
BEGIN
set @dbprop = 'Hourly Job Load'
declare @jobLoadData xml = (
select cast(csInfo.Unit as XML)
FROM cf_SurveyScaleReportInfo AS csInfo WITH (NOLOCK)
		WHERE  csInfo.SurveyResultId = @surveyResultid
		and csInfo.Property = @dbprop
)
select
T.n.value('@Hour', 'varchar(100)') as 'As Of',
T.n.value('@Running', 'int') as 'Running',
T.n.value('@Pending', 'int') as 'Pending',
T.n.value('@Waiting', 'int') as 'Waiting',
T.n.value('@Suspended', 'int') as 'Suspended'
FROM @jobLoadData.nodes('/HourlyJobData') AS T(n)
END
ELSE IF ( @i_GroupType = 'Commserv Database Size' )
BEGIN
	IF OBJECT_ID('tempdb..#CommservDBSize') IS NOT NULL DROP TABLE #CommservDBSize
	CREATE TABLE #CommservDBSize(
		[Logical Name] NVARCHAR(MAX),
		Path NVARCHAR(MAX),
		[File Type] NVARCHAR(MAX),
		[Initial Size] DECIMAL(10,2),
		[Used Size] DECIMAL(10,2),
		[Free Size] DECIMAL(10,2),
		[Free Percentage] NVARCHAR(20),
		[Maxsize] NVARCHAR(512),
		[AutogrowthEnabled] NVARCHAR(20),
		[AutogrowthValue] INT,
		[Autogrowth] NVARCHAR(20),
		[Remarks] NVARCHAR(1024)
	)
	DECLARE @CommservDatabaseSize XML
	SET @CommservDatabaseSize =
	(
		SELECT CAST(Unit AS XML)
		FROM cf_SurveyScaleReportInfo AS csInfo WITH (NOLOCK)
			WHERE  csInfo.SurveyResultId = @surveyResultid
			and csInfo.Property = @i_GroupType
	)
	INSERT INTO #CommservDBSize
	SELECT
		RowData.d.value('@name', 'NVARCHAR(MAX)'),
		RowData.d.value('@Physical_Path', 'NVARCHAR(MAX)'),
		RowData.d.value('@fileType', 'INT'),
		CAST(RowData.d.value('@size', 'BIGINT')/1024.0 AS DECIMAL(10,2)),
		CAST(RowData.d.value('@usedMB', 'BIGINT')/1024.0 AS DECIMAL(10,2)),
		CAST(RowData.d.value('@freeMB', 'BIGINT')/1024.0 AS DECIMAL(10,2)),
		RowData.d.value('@pctFree', 'NVARCHAR(MAX)')+'%',
		RowData.d.value('@maxSize', 'NVARCHAR(MAX)'),
		RowData.d.value('@isPercentGrowth', 'NVARCHAR(MAX)'),
		RowData.d.value('@growthSize', 'BIGINT'),
		RowData.d.value('@growthSize', 'NVARCHAR(MAX)'), ''
	FROM @CommservDatabaseSize.nodes('/CommservDatabaseSize') RowData(d)
	DECLARE @WarningSizeThreshold INT = (select WarningThreshold from cf_SurveyHealthParam WITH (NOLOCK) WHERE QueryId=7 and ParamId = 729)
	DECLARE @CriticalSizeThreshold INT = (select CriticalThreshold from cf_SurveyHealthParam WITH (NOLOCK) WHERE QueryId=7 and ParamId = 729)
	DECLARE @MaxLogSize INT = (SELECT MAX([Initial Size]) FROM #CommservDBSize WHERE [File Type] = 1)
	DECLARE @MaxDataSize INT = (SELECT MAX([Initial Size]) FROM #CommservDBSize WHERE [File Type] <> 1)
	--Add Remark to log file
	IF @MaxLogSize >= @WarningSizeThreshold
	BEGIN
		IF @MaxLogSize >= @CriticalSizeThreshold
		BEGIN
			UPDATE #CommservDBSize
			SET Remarks = 'Contact Support to investigate the log file growth.'
			WHERE [File Type] = 1 AND AutogrowthEnabled = 1 AND AutogrowthValue > 5
		END
		ELSE
		BEGIN
			UPDATE #CommservDBSize
			SET Remarks = 'Contact Support to investigate the log file growth.'
			WHERE [File Type] = 1 AND AutogrowthEnabled = 1 AND AutogrowthValue > 10
		END
	END
	--Add Remark to data file
	IF @MaxDataSize >= @WarningSizeThreshold
	BEGIN
		IF @MaxDataSize >= @CriticalSizeThreshold
		BEGIN
			UPDATE #CommservDBSize
			SET Remarks += 'Decrease to 5% Autogrowth.'
			WHERE AutogrowthEnabled = 1 AND AutogrowthValue > 5
		END
		ELSE
		BEGIN
			UPDATE #CommservDBSize
			SET Remarks += 'Decrease to 10% Autogrowth.'
			WHERE AutogrowthEnabled = 1 AND AutogrowthValue > 10
		END
	END
	--Update Autogrowth value to display
	UPDATE #CommservDBSize
	SET Autogrowth = 'By ' + CASE WHEN AutogrowthEnabled = 1 THEN Autogrowth + ' Percent' ELSE dbo.FormatBytes(AutogrowthValue, 'MB') END
	SELECT [Logical Name],
		   Path,
		   [Initial Size] AS [Initial Size<SIZEMB>],
		   [Used Size] AS [Used Size<SIZEMB><HIDDEN>],
		   [Free Size] AS [Free Size<SIZEMB>],
		   [Free Percentage] AS [Available Size %],
		   Maxsize,
		   Autogrowth,
		   Remarks
	FROM #CommservDBSize
	IF OBJECT_ID('tempdb..#CommservDBSize') IS NOT NULL DROP TABLE #CommservDBSize
END
ELSE IF (@i_GroupType = (SELECT ParameterName FROM cf_SurveyHealthParam WITH(NOLOCK) WHERE ParamId = 727))
BEGIN
SELECT @CriticalThreshold = criticalThreshold, @WarningThreshold = warningThreshold FROM cf_SurveyHealthParam WITH(NOLOCK) WHERE ParamId = 727
DECLARE @DriveUsageXml XML
SET @DriveUsageXml =
(
	SELECT CAST(Unit AS XML)
	FROM cf_SurveyScaleReportInfo AS csInfo WITH (NOLOCK)
		WHERE  csInfo.SurveyResultId = @surveyResultid
		and csInfo.Property = @i_GroupType
)
DECLARE @tblDriveUsage TABLE (LogicalDriveName NVARCHAR(MAX), Drive NVARCHAR(MAX), TotalSpaceMB BIGINT, FreeSpaceMB BIGINT, Databases NVARCHAR(MAX), PercentFreeSpace DECIMAL(5,2))
INSERT INTO @tblDriveUsage (LogicalDriveName, Drive, TotalSpaceMB, FreeSpaceMB, Databases)
SELECT
	ISNULL(drive.d.value('@LogicalDriveName', 'NVARCHAR(MAX)'), ''),
	ISNULL(drive.d.value('@Drive', 'NVARCHAR(MAX)'), ''),
	ISNULL(drive.d.value('@TotalSpaceMB', 'BIGINT'), 0),
	ISNULL(drive.d.value('@FreeSpaceMB', 'BIGINT'), 0),
	ISNULL(drive.d.value('@Databases', 'NVARCHAR(MAX)'), '')
FROM @DriveUsageXml.nodes('/Drive') drive(d)
UPDATE @tblDriveUsage
SET LogicalDriveName = CASE LogicalDriveName WHEN '' THEN Drive ELSE LogicalDriveName END,
	PercentFreeSpace = CAST(FreeSpaceMB * 100.0 / TotalSpaceMB AS DECIMAL(5,2))
SELECT LogicalDriveName AS 'Logical Drive Name',
	Drive AS 'Drive',
	TotalSpaceMB AS 'Total Space<SIZEMB>',
	FreeSpaceMB AS 'Free Space<SIZEMB>',
	Databases AS 'Databases',
	PercentFreeSpace AS 'Free Space (%)',
	CASE WHEN PercentFreeSpace < @criticalThreshold THEN @strCritical
		WHEN PercentFreeSpace <= @warningThreshold THEN @strWarning
		ELSE @strGood
	END AS 'Status<HEALTH_STATUS>'
FROM @tblDriveUsage
ORDER BY LogicalDriveName
END
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'RptScalereportCommcellStatusSurvey')
	delete from GxQscripts where name = 'RptScalereportCommcellStatusSurvey'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptScalereportCommcellStatusSurvey')
	delete from GXDBVersions where aliasname = 'RptScalereportCommcellStatusSurvey'
GO

insert into GXDBVersions values(2, 'RptScalereportCommcellStatusSurvey',  'v1.15.48.12.4.1', 'RptScalereportCommcellStatusSurvey', 'v1.15.48.12.4.1')
GO

