--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 in GB DECLARE @cmd NVARCHAR(4000 ) DECLARE @HTML NVARCHAR (MAX) DECLARE @CriticalGB INT DECLARE @WarningGB INT DECLARE @ResultSetThreshold INT DECLARE @email_recipients VARCHAR (MAX) SET @ResultSetThreshold = 50 --GB Threshold for being returned in the resultset SET @CriticalGB = 10 -- Change the Critical GB threshold Here SET @WarningGB = 20 -- Change the Warning GB threshold Here. SET @email_recipients = 'pkelley@sentryone.com' --Change email recipients here -- Header can modified below. Main header can be found inbetween the tag. Column headers are at the very bottom of this section. SET @HTML = ' DiskSpace Report
Disk Space Report - Free Space in GB
' + '' + 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 = CASE WHEN CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 )) < @CriticalGB --Critical GB Free space threshold THEN 'RED5555' + CAST( CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 ))AS VARCHAR(30 )) WHEN CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 )) < @WarningGB --Critical GB Free space threshold THEN 'YELLOW5555' + CAST (CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 ))AS VARCHAR(30 )) ELSE CAST(CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 ))AS VARCHAR(30 )) END, '' , td = CAST(( ( FreeSpace * 1.00 ) / size ) --This Case handled highlighting thresholds * 100 AS NUMERIC( 36, 2 )) --Critical highlight Threshold 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 CAST (( ( [FreeSpace] / 1024.00 ) / 1024.00 )/ 1024.00 AS DECIMAL( 36, 2 )) <= @ResultSetThreshold --AND [WmiDeviceID] NOT LIKE '%C%' --Drive Letter Exclusion --AND HostName NOT LIKE '%%' --Server Name Exclusion ORDER BY [FreeSpace] ASC FOR XML PATH ('tr') ) AS NVARCHAR( MAX)) + '
ServerName DriveLetter DriveLabel TotalCapacityGB FreeSpaceGB FreeSpace %
' + '
' SET @HTML = REPLACE( @HTML, '
Warning less than ' --Warning Legend + CAST (@WarningGB AS NVARCHAR( 10)) + 'GB free space Critical less than ' -- Critical Legend + CAST (@CriticalGB AS NVARCHAR( 10)) + 'GB 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'