--The below code is a template for what could be thrown in a job and/or an Execute SQL to show disks with low % free space DECLARE @cmd NVARCHAR(4000 ) DECLARE @HTML NVARCHAR (MAX) DECLARE @CriticalPercent INT DECLARE @WarningPercent INT DECLARE @ResultSetThreshold INT DECLARE @email_recipients VARCHAR (MAX) SET @ResultSetThreshold = 20 -- % Free Threshold for being returned in the resultset SET @CriticalPercent = 10 -- Change the Critical Percent Here SET @WarningPercent = 15 -- Change the Warning Percent Here. SET @email_recipients = 'pkelley@sentryone.com' --Change email recipients here -- Header can modified below. Shown inbetween the tag. Column headers are at the very bottom of this section. SET @HTML = ' DiskSpace Report
Disk Space Report - % Free Space
' + '' + CAST (( SELECT td = b .HostName , '' , td = [WmiDeviceID] , '' , td = CASE WHEN [VolumeName] IS NULL THEN '' ELSE [VolumeName] END , '' , td = CAST (( ( [Size] / 1024.00 ) / 1024.00 ) / 1024.00 AS DECIMAL (36,2)) , '' , td = CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 )) , '' , td = CASE WHEN CAST(( ( FreeSpace * 1.00 ) / size ) --This Case handled highlighting thresholds * 100 AS NUMERIC( 36, 2 )) <= @CriticalPercent --Critical highlight Threshold THEN 'RED5555' + CAST (CAST(( ( FreeSpace * 1.00 )/ size ) * 100 AS NUMERIC(36 ,2)) AS VARCHAR(30 )) WHEN CAST (( ( FreeSpace * 1.00 ) / size ) * 100 AS NUMERIC( 36, 2 )) <= @WarningPercent --Warning Highlight Threshold THEN 'YELLOW5555' + CAST (CAST(( ( FreeSpace * 1.00 )/ size ) * 100 AS NUMERIC(36 ,2)) AS VARCHAR(30 )) ELSE CAST (CAST(( ( FreeSpace * 1.00 ) / size ) * 100 AS NUMERIC( 36, 2 )) AS VARCHAR(30 )) END FROM [SentryOne].[dbo]. [PerformanceAnalysisDeviceLogicalDisk] a LEFT JOIN [SentryOne].[dbo] .[Device] b ON a.DeviceID = b.ID --Check the PerformanceAnalysisDeviceLogicalDisk and Device table for additional filter options, like drive letter or target exclusions. WHERE WmiDeviceID NOT LIKE '%\\%' and size > 0 AND FreeSpace * 100.00 / size <= @ResultSetThreshold --AND [WmiDeviceID] NOT LIKE '%C%' --Drive Letter Exclusion --AND HostName NOT LIKE '%%' --Server Name Exclusion ORDER BY ( FreeSpace * 1.00 ) / size ASC FOR XML PATH ('tr') ) AS NVARCHAR( MAX)) + '
ServerName DriveLetter DriveLabel TotalCapacityGB FreeSpaceGB FreeSpace %
' + '
' SET @HTML = REPLACE( @HTML, '
Warning less than ' --Warning Legend + CAST (@WarningPercent AS NVARCHAR( 10)) + '% free space Critical less than ' -- Critical Legend + CAST (@CriticalPercent AS NVARCHAR( 10)) + '% free space
', '') SET @HTML = REPLACE( @HTML, 'RED5555', '') --Critical Highlighting Settings SET @HTML = REPLACE( @HTML, 'YELLOW5555', '') --Warning Highlighting Settings EXEC msdb.. sp_sentry_dbmail_20 --@profile_name = 'SqlSentry' , @recipients = @email_recipients, @subject = 'Disk Space Report', @body = @HTML, @body_format = 'HTML', @execute_query_database = 'msdb'