uspEmailDriveStatus: Monitoring disk only

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.uspEmailDriveStatus') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.uspEmailDriveStatus
GO

--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: May 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailDriveStatus
as
begin
set nocount on;

declare
@body varchar(max),
@text varchar(max),
@SuccessCount int,
@FailedCount int,
@NotRunCount int,
@EmailProfile dbo.EmailProfile,
@EmailFrom dbo.EmailFrom,
@EmailTo dbo.EmailTo,
@EmailCc dbo.EmailCc,
@RedAlertPercent int = 5,
@YellowAlertPercent int = 10,
@EmailCode dbo.EmailCode = 'Drive_Report',
@Date date = GETDATE(),
@Subject varchar(max)

if dbo.fnRequireEmail(@Date, @EmailCode) = 0
begin
return
end

select
@EmailProfile = EmailProfile,
@EmailFrom = EmailFrom,
@EmailTo = EmailTo,
@EmailCc = EmailCc
from dbo.SettingEmail
where EmailCode = @EmailCode

if @@ROWCOUNT = 0
begin
return
end;

create table #disk (
Drive varchar(10),
MBFree FLOAT,
MBTotal FLOAT,
DriveName varchar(100))
INSERT INTO #disk(Drive, MBFree)
EXEC MASTER..xp_fixeddrives

update #disk set
DriveName = t.LogicalName,
MBFree = t.FreeSpaceInMB,
MBTotal = t.TotalSpaceInMB
from #disk di,
(
SELECT DISTINCT
dovs.logical_volume_name AS LogicalName,
left(dovs.volume_mount_point, 1) AS Drive,
cast(dovs.available_bytes / 1048576.0 as numeric(36, 2)) AS FreeSpaceInMB,
cast(dovs.total_bytes / 1048576.0 as numeric(36, 2)) AS TotalSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
) t
where di.Drive = t.Drive

declare @diskInfo table (
Drive varchar(10),
DriveName varchar(100),
FreeSpaceInGB FLOAT,
TotalSpaceInGB FLOAT,
PercentLeft float,
Critical varchar(10))
insert into @diskInfo (Drive, DriveName, FreeSpaceInGB, TotalSpaceInGB, PercentLeft, Critical)
select
t.Drive,
t.DriveName,
t.FreeSpaceInGB,
t.TotalSpaceInGB,
t.PercentLeft,
Critical = case when t.PercentLeft < 10 then '***' else '' end
from
(
select
Drive,
DriveName = ISNULL(DriveName, ''),
cast(MBFree / 1024 as numeric(36, 2)) AS FreeSpaceInGB,
cast(MBTotal / 1024.0 as numeric(36, 0)) AS TotalSpaceInGB,
cast(MBFree * 100.0 / MBTotal as numeric(36, 3)) AS PercentLeft
from #disk
WHERE MBTotal IS not null
) t
drop table #disk

set @Subject = 'Server Disk Report for ' + convert(varchar(10), @Date, 120)

--select
-- [@bgcolor] = CASE when PercentLeft < 5 then '#FF0000' --red/#E41B17
-- when PercentLeft < 10 then '#FFFF00' --yellow/#EAC117
--   ELSE '#00FF00' --green/#4AA02C
-- END,
-- td = Drive, '',
-- td = DriveName, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'center',
-- td = Critical
--from @diskInfo
--order by CASE when PercentLeft < 5 then 1
-- when PercentLeft < 10 then 2
-- ELSE 3
-- END, Drive

  set @text =
CAST( (
select
[@bgcolor] = CASE when PercentLeft < 10 then '#FF0000' --red/#E41B17
when PercentLeft < 15 then '#FFFF00' --yellow/#EAC117
  ELSE '#00FF00' --green/#4AA02C
END,
color = CASE when PercentLeft < 5 then 'white' else 'black' END,
td = Drive, '',
td = DriveName, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(TotalSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(FreeSpaceInGB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = CAST(CAST(PercentLeft AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'center',
td = Critical
from @diskInfo
order by CASE when PercentLeft < 10 then 1
when PercentLeft < 15 then 2
ELSE 3
END, Drive
for xml path( 'tr' ), type
) as varchar(max) )

set @body = 'The following summary displays space usage on Phone Channel Data Warehouse Server (i.e.: CRSDPSSCA1IDCS\PC):

'
+ ''
+ ' white < 10%  
' + 'black < 15%  
' + ' OK 
' + '

' + ''
+ '' +
+ ' DriveDrive NameTotal Space
(GB)Free Space
(GB)Free Space
(%)Critical
(MB) '
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
set @body = REPLACE(@body, 'white', '')
set @body = REPLACE(@body, 'black', '')
set @body = REPLACE(@body, 'close', '
')
--print @body
exec uspSendEmail
@EmailCode = @EmailCode,
@EmailProfile = @EmailProfile,
@EmailFrom = @EmailFrom,
@EmailTo = @EmailTo,
@EmailCc = @EmailCc,
@Subject = @Subject,
@body = @body
end
--------------------------------------------------------------------------------
GO

--exec uspEmailDriveStatus
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob