uspEmailStorageStatus

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

--------------------------------------------------------------------------------
-- =============================================
-- Author: Simon You
-- Create date: Feb 2018
-- Description:
-- =============================================
create procedure dbo.uspEmailStorageStatus(@Date date)
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 = 'Space_Report',
@Subject varchar(max) = 'Disk Space Report'

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;

declare @tDb table(
DBName DBName,
Drive Drive,
DBSizeMB INT,
DriveTotalMB INT,
DriveFreeMB INT,
DailyDBUsedKB INT,
DailyDriveUsedKB INT,
DailyDBUsedKB30 INT,
DailyDriveUsedKB30 INT,
DaysLast int,
PercentAlert int --0: OK, 1: Yellow, 2: Red
);

with cte_Detail (DBName, Drive, DBSizeMB, UnallocatedSpaceMB, ReservedKB, DataKB, IndexSizeKB, UnusedKB, DriveTotalMB, DriveFreeMB,
LastRunDate, LastUsedKB, [Days], LastRunDate30, LastUsedKB30, Days30) as
(
select
db.DBName,
dr.Drive,
dbDtl.DBSizeMB,
dbDtl.UnallocatedSpaceMB,
dbDtl.ReservedKB,
dbDtl.DataKB,
dbDtl.IndexSizeKB,
dbDtl.UnusedKB,
dr.TotalSpaceInMB,
drDtl.FreeSpaceInMB,
hist.RunDate,
hist.UsedKB,
DATEDIFF(dd, hist.RunDate, @Date),
hist30.RunDate,
hist30.UsedKB,
DATEDIFF(dd, hist30.RunDate, @Date)
FROM dbo.StoHistoryHeader hdr
JOIN dbo.StoHistoryDbDetail dbDtl on hdr.StoHistoryHeaderId = dbDtl.StoHistoryHeaderId
JOIN dbo.StoDb db on dbDtl.StoDbId = db.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDriveDbMap map on db.StoDbId = map.StoDbId and db.IsActive = 1
LEFT JOIN dbo.StoDrive dr on map.StoDriveId = dr.StoDriveId
LEFT JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId
and dr.StoDriveId = drDtl.StoDriveId
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
order by hdr2.RunDate
) hist
CROSS APPLY (select top 1
hdr2.RunDate,
UsedKB = dbDtl2.DataKB + dbDtl2.IndexSizeKB
from dbo.StoHistoryDbDetail dbDtl2
join dbo.StoHistoryHeader hdr2 on dbDtl2.StoHistoryHeaderId = hdr2.StoHistoryHeaderId
where dbDtl2.StoDbId = dbDtl.StoDbId
and hdr2.RunDate >= DATEADD(mm, -1, @Date)
order by hdr2.RunDate
) hist30
where CAST(hdr.RunDate AS DATE) = @Date
), cte_DB (DBName, Drive, DBSizeMB, UsedKB, LastDate, LastUsedKB, DriveTotalMB, DriveFreeMB, DailyDbUsedKB, DailyDbUsedKB30) as
(
select
cte.DBName,
cte.Drive,
cte.DBSizeMB,
CurrentSize = cte.DataKB + cte.IndexSizeKB,
cte.LastRunDate,
cte.LastUsedKB,
cte.DriveTotalMB,
cte.DriveFreeMB,
DailyDbUsedKB = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB) / [Days]
ELSE 0 END,
DailyDbUsedKB30 = CASE WHEN (cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30 > 0
THEN ((cte.DataKB + cte.IndexSizeKB) - cte.LastUsedKB30) / [Days30]
ELSE 0 END
from cte_Detail cte
), cte_Drive (Drive, DriveTotalMB, DriveFreeMB, DailyDriveUsedKB, DailyDriveUsedKB30, DaysLast) as
(
select
Drive,
MAX(DriveTotalMB),
MAX(DriveFreeMB),
SUM(DailyDbUsedKB),
SUM(DailyDbUsedKB30),
DaysLast = CASE WHEN SUM(DailyDbUsedKB30 * 30.0 / 1000.0) < 1.0 THEN NULL
ELSE MAX(DriveFreeMB) / SUM(DailyDbUsedKB30 / 1000.0) END
from cte_DB
group by Drive
)
insert into @tDb (DBName, Drive, DBSizeMB, DriveTotalMB, DriveFreeMB, DailyDBUsedKB, DailyDriveUsedKB, DailyDBUsedKB30, DailyDriveUsedKB30, DaysLast, PercentAlert)
select
db.DBName,
db.Drive,
db.DBSizeMB,
db.DriveTotalMB,
db.DriveFreeMB,
db.DailyDbUsedKB,
dr.DailyDriveUsedKB,
db.DailyDBUsedKB30,
dr.DailyDriveUsedKB30,
dr.DaysLast,
PercentAlert = case when dr.DriveTotalMB = 0 then 0
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @YellowAlertPercent then 1
when dr.DriveFreeMB * 100.0 / dr.DriveTotalMB < @RedAlertPercent then 2
else 0 end
from cte_DB db
join cte_Drive dr on db.Drive = dr.Drive


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

--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
--   ELSE '#800080' --coffee/#4AA02C
-- END,
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = dbo.fnDaysLast(DaysLast), ''
--from
--(
-- select
-- Drive,
-- DriveTotalMB = MAX(DriveTotalMB),
-- DriveFreeMB = MAX(DriveFreeMB),
-- DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
-- DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
-- DaysLast = MAX(DaysLast)
-- from @tDb
-- group by Drive
--) t
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, Drive

  set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
  ELSE '#2ECC40' --coffee/#4AA02C/#800080
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveTotalMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DriveFreeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
FreePercent = CAST(CAST(DriveFreeMB * 100.0 / DriveTotalMB AS numeric(36, 1)) AS VARCHAR) + '%', '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDriveUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = dbo.fnDaysLast(DaysLast), ''
from
(
select
Drive,
DriveTotalMB = MAX(DriveTotalMB),
DriveFreeMB = MAX(DriveFreeMB),
DailyDriveUsedKB = MAX(DailyDriveUsedKB ),
DailyDriveUsedKB30 = MAX(DailyDriveUsedKB30),
DaysLast = MAX(DaysLast),
PercentAlert = MAX(PercentAlert)
from @tDb
group by Drive
UNION ALL
select
StoDrive.Drive,
DriveTotalMB = CAST(StoDrive.TotalSpaceInMB AS INT),
DriveFreeMB = CAST(drDtl.FreeSpaceInMB AS INT),
DailyDriveUsedKB = NULL,
DailyDriveUsedKB30 = NULL,
DaysLast = NULL,
PercentAlert = case when StoDrive.TotalSpaceInMB = 0 then 0
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @YellowAlertPercent then 1
when drDtl.FreeSpaceInMB * 100.0 / StoDrive.TotalSpaceInMB < @RedAlertPercent then 2
else 0 end
from dbo.StoDrive
JOIN dbo.StoHistoryHeader hdr on CAST(hdr.RunDate AS DATE) = @Date
JOIN dbo.StoHistoryDriveDetail drDtl on hdr.StoHistoryHeaderId = drDtl.StoHistoryHeaderId and StoDrive.StoDriveId = drDtl.StoDriveId
where StoDrive.Drive not in (select Drive from @tDb)
and StoDrive.IsActive = 1
) t
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 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 < 30 days or 5% 
' + 'black < 90 days or 10% 
' + 'black No usage 
' + ' OK 
' + '

' + ''
+ '' +
+ ' DriveTotal Space
(MB)Free Space
(MB)Free Space
(%)Overall Monthly
(MB)Last Month
(MB)Estimated Days Last '
+ replace( replace( @text, '<', '<' ), '>', '>' )
+ '
'
--select
-- [@bgcolor] = CASE when DaysLast < 30 then '#FF0000' --red/#E41B17
-- when DaysLast < 90 then '#FFFF00' --yellow/#EAC117
-- when DaysLast >= 90 then '#00FF00' --green/#4AA02C
--   ELSE '#800080' --green/#4AA02C
-- END,
-- td = DBName, '',
-- td = Drive, '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- [td/@align] = 'right',
-- td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000.0 AS MONEY), 1), '.00' ,''), '',
-- color = CASE when DaysLast >= 90 then 'close' else 'close' END
--from @tDb
--order by CASE when DaysLast < 30 then 1
-- when DaysLast < 90 then 2
-- ELSE 3
-- END, DBName

  set @text =
CAST( (
select
[@bgcolor] = CASE when DaysLast < 30 or PercentAlert = 2 then '#FF0000' --red/#E41B17
when DaysLast < 90 or PercentAlert = 1 then '#FFFF00' --yellow/#EAC117
when DaysLast >= 90 then '#00FF00' --green/#4AA02C
  ELSE '#2ECC40' --green/#4AA02C
END,
color = CASE when DaysLast < 30 then 'white' else 'black' END,
td = DBName, '',
td = Drive, '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DBSizeMB AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
[td/@align] = 'right',
td = REPLACE(CONVERT(varchar, CAST(DailyDBUsedKB30 * 30 / 1000 AS MONEY), 1), '.00' ,''), '',
color = CASE when DaysLast >= 90 then 'close' else 'close' END
from @tDb
order by CASE when DaysLast < 30 or PercentAlert = 2 then 1
when DaysLast < 90 or PercentAlert = 1 then 2
ELSE 3
END, DBName
for xml path( 'tr' ), type
) as varchar(max) )

set @body = @body + '

The following summary displays disk usage for all databases:

'
+ ''
+ '' +
+ ' DB NameDriveDB Size
(MB)'
+ 'Overall Monthly
(MB)Last Month
(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 uspEmailStorageStatus '2018-04-03'
/*
declare @date date = '2018-02-05'
exec uspEmailStorageStatus @date
*/
--select * from dbo.CtsJob