
DROP PROCEDURE dbo.DBAChecks_DiskDrives 
GO

CREATE PROC [dbo].[DBAChecks_DiskDrives](@PCTFreeWarningThreshold int,@PCTFreeCriticalThreshold int,@HTML varchar(max) out)
AS

/* 
	Returns HTML for "Disk Drives" section of DBA Checks Report
*/ 

DECLARE @MBytesToTB float,@MBytesToGB float
SELECT @MBytesToTB =  1048576,@MBytesToGB = 1024;

WITH DriveInfo AS (
	SELECT drive_mountpoint,
		CASE WHEN capacity_mb > @MBytesToTB THEN CAST(ROUND(capacity_mb /@MBytesToTB,1) as varchar) + ' TB'
			WHEN capacity_mb > @MBytesToGB THEN CAST(ROUND(capacity_mb /@MBytesToGB,1) as varchar) + ' GB'
			ELSE capacity_mb + ' MB' END as Size,

		CASE WHEN used_space_mb > @MBytesToTB THEN CAST(ROUND(used_space_mb /@MBytesToTB,1) as varchar) + ' TB'
			WHEN used_space_mb > @MBytesToGB THEN CAST(ROUND(used_space_mb /@MBytesToGB,1) as varchar) + ' GB'
			ELSE used_space_mb + ' MB' END as Used,
		
		CASE WHEN free_space_mb > @MBytesToTB THEN CAST(ROUND(free_space_mb /@MBytesToTB,1) as varchar) + ' TB'
			WHEN free_space_mb > @MBytesToGB THEN CAST(ROUND(free_space_mb /@MBytesToGB,1) as varchar) + ' GB'
			ELSE free_space_mb + ' MB' END as Free,
		ROUND(Percent_Free_Space,2) AS PercentFree
	FROM dbo.Get_DiskInfo()

)
SELECT @HTML = 
	'<h2>Disk Drives</h2>
	<table>' +
	(SELECT 'Drive' th,
			'Size' th,
			'Used' th,
			'Free' th,
			'Free %' th
	FOR XML RAW('tr'),ELEMENTS) 
	+
	(SELECT drive_mountpoint td,
			Size td,
			Used td,
			Free td,
			CAST(CASE WHEN PercentFree < @PCTFreeCriticalThreshold THEN '<div class="Critical">' + CAST(PercentFree as varchar) + '</div>'
			WHEN PercentFree < @PCTFreeWarningThreshold THEN '<div class="Warning">' + CAST(PercentFree as varchar) + '</div>'
			ELSE '<div class="Healthy">' + CAST(PercentFree as varchar) + '</div>' END as XML) td
	FROM DriveInfo 
	FOR XML RAW('tr'),ELEMENTS XSINIL)
	+ '</table>'
GO

