USE [Admin]
GO

/****** Object:  Table [dbo].[DiskSpaceAlertThreshold]    Script Date: 09/23/2016 18:46:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId] [int] IDENTITY(1, 1) NOT NULL
	,[AlertClass] [varchar](25) NULL
	,[ThresholdValue] [int] NULL
	,PRIMARY KEY CLUSTERED ([DiskSpaceAlertThresholdId] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[DiskSpaceAlertThreshold] ON

INSERT [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId]
	,[AlertClass]
	,[ThresholdValue]
	)
VALUES (
	1
	,N'Red'
	,10
	)

INSERT [dbo].[DiskSpaceAlertThreshold] (
	[DiskSpaceAlertThresholdId]
	,[AlertClass]
	,[ThresholdValue]
	)
VALUES (
	2
	,N'Yellow'
	,20
	)

SET IDENTITY_INSERT [dbo].[DiskSpaceAlertThreshold] OFF
/****** Object:  Table [dbo].[StaffEmailList]    Script Date: 09/23/2016 18:46:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[StaffEmailList] (
	[StaffEmailListId] [int] IDENTITY(1, 1) NOT NULL
	,[FirstName] [varchar](255) NULL
	,[LastName] [varchar](255) NULL
	,[EmailAddress] [varchar](255) NULL
	,[Active] [bit] NULL
	,PRIMARY KEY CLUSTERED ([StaffEmailListId] ASC) WITH (
		PAD_INDEX = OFF
		,STATISTICS_NORECOMPUTE = OFF
		,IGNORE_DUP_KEY = OFF
		,ALLOW_ROW_LOCKS = ON
		,ALLOW_PAGE_LOCKS = ON
		) ON [PRIMARY]
	) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[StaffEmailList] ON

INSERT [dbo].[StaffEmailList] (
	[StaffEmailListId]
	,[FirstName]
	,[LastName]
	,[EmailAddress]
	,[Active]
	)
VALUES (
	1
	,N'Clark'
	,N'Kent'
	,N'cKent@thedailyplanet.com'
	,1
	)

SET IDENTITY_INSERT [dbo].[StaffEmailList] OFF
/****** Object:  StoredProcedure [dbo].[LowDiskSpaceAlert]    Script Date: 09/23/2016 18:46:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LowDiskSpaceAlert]
AS
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#DiskSpaceStats') IS NOT NULL
	DROP TABLE #DiskSpaceStats

IF OBJECT_ID('tempdb..#DriveAlert') IS NOT NULL
	DROP TABLE #DriveAlert

DECLARE @profileName AS VARCHAR(128)
DECLARE @thresholdRed AS INT
DECLARE @thresholdYellow AS INT
DECLARE @drive AS VARCHAR(5)
DECLARE @sql AS VARCHAR(MAX)
DECLARE @i AS INT
DECLARE @j AS INT

/*Change the name for your SQL Server email profile*/
SET @profileName = 'SQL Report'

CREATE TABLE #DriveAlert (
	AlertEntry VARCHAR(25)
	,AlertValue VARCHAR(25)
	)

SET @thresholdRed = (
		SELECT ThresholdValue
		FROM [Admin].[dbo].[DiskSpaceAlertThreshold]
		WHERE AlertClass = 'Red'
		)
SET @thresholdYellow = (
		SELECT ThresholdValue
		FROM [Admin].[dbo].[DiskSpaceAlertThreshold]
		WHERE AlertClass = 'Yellow'
		)

SELECT ROW_NUMBER() OVER (
		ORDER BY AvailableDriveSpacePercentage ASC
		) AS RowNo
	,ObservationDT
	,Drive
	,AvailableDriveSpacePercentage
	,AvailableDriveSpaceGB
	,UsedDriveSpaceGB
	,TotalDriveSpaceGB
INTO #DiskSpaceStats
FROM (
	SELECT DISTINCT GETDATE() AS ObservationDT
		,dovs.volume_mount_point AS Drive
		,CAST(((dovs.available_bytes / 1073741824.0) / (dovs.total_bytes / 1073741824.0)) * 100 AS NUMERIC(8, 2)) AS AvailableDriveSpacePercentage
		,CAST((dovs.available_bytes / 1073741824.0) AS NUMERIC(8, 3)) AvailableDriveSpaceGB
		,CAST(((dovs.total_bytes - dovs.available_bytes) / 1073741824.0) AS NUMERIC(8, 3)) AS UsedDriveSpaceGB
		,CAST((dovs.total_bytes / 1073741824.0) AS NUMERIC(8, 3)) TotalDriveSpaceGB
	FROM sys.master_files AS mf
	CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) AS dovs
	) AS r

SET @i = 1
SET @j = (
		SELECT MAX(RowNo)
		FROM #DiskSpaceStats
		)

WHILE @i <= @j
BEGIN
	SET @drive = (
			SELECT Drive
			FROM #DiskSpaceStats
			WHERE RowNo = @i
			)
	SET @sql = 'DECLARE @alert AS VARCHAR(6)
IF ' + CONVERT(VARCHAR(3), @thresholdYellow) + ' > (
		SELECT AvailableDriveSpacePercentage
		FROM #DiskSpaceStats
		WHERE Drive = ''' + @drive + '''
		)
BEGIN
SET @alert = ''YELLOW''
END
IF ' + CONVERT(VARCHAR(3), @thresholdRed) + ' > (
		SELECT AvailableDriveSpacePercentage
		FROM #DiskSpaceStats
		WHERE Drive = ''' + @drive + '''
		)
BEGIN
SET @alert = ''RED''
END
IF @alert IS NOT NULL
BEGIN
	SELECT AlertEntry
	,AlertValue
	FROM (
	SELECT ''Alert Class: '' AS AlertEntry
	,@alert AS AlertValue 
	UNION ALL
	SELECT ''DateTime: '' AS AlertEntry
	,CONVERT(VARCHAR(30), ObservationDT)  AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Drive: '' AS AlertEntry
	,Drive AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + 
		'''
	UNION ALL
	SELECT ''Percentage Available: '' AS AlertEntry
	,CONVERT(VARCHAR(10), AvailableDriveSpacePercentage) + ''%'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Available Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), AvailableDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Used Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), UsedDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL
	SELECT ''Total Space: '' AS AlertEntry
	,CONVERT(VARCHAR(10), TotalDriveSpaceGB) + '' GB'' AS AlertValue
	FROM #DiskSpaceStats
	WHERE Drive = ''' + @drive + '''
	UNION ALL 
	SELECT '' '' AS AlertEntry
	,'' '' AS AlertValue
	) AS alert
END
'

	INSERT INTO #DriveAlert
	EXEC (@sql)

	SET @i = @i + 1
END

IF EXISTS (
		SELECT *
		FROM #DriveAlert
		)
BEGIN
	DECLARE @emailList AS VARCHAR(MAX)
	DECLARE @subjectMsg AS VARCHAR(255)
	DECLARE @tableHTML NVARCHAR(MAX)

	SET @emailList = (
			SELECT STUFF((
						SELECT '; ' + EmailAddress
						FROM [Admin].[dbo].[StaffEmailList]
						WHERE Active = 1
						FOR XML PATH('')
						), 1, 1, '') AS EmailAddress
			)
	SET @subjectMsg = (
			SELECT @@SERVERNAME
			) + ' Low Disk Space Alert'
	SET @tableHTML = N'<style>
	.tableFormat {
		width:80%;
		border:1px solid #C0C0C0;
		border-collapse:collapse;
		padding:5px;
	}
	.tableFormat th {
		border:1px solid #C0C0C0;
		padding:5px;
		background:#F0F0F0;
	}
	.tableFormat td {
		border:1px solid #C0C0C0;
		text-align:right;
		padding:5px;
	}
</style>' + N'<H1></H1>' + N'<table class="tableFormat" align="center">' + N'<tr><th>Description</th><th>Value</th></tr>' + CAST((
				SELECT td = AlertEntry
					,''
					,td = AlertValue
					,''
				FROM #DriveAlert
				FOR XML PATH('tr')
					,TYPE
				) AS NVARCHAR(MAX)) + N'</table>';

	DROP TABLE #DiskSpaceStats

	SET @tableHTML = REPLACE(@tableHTML, '<td> </td>', '<td bgcolor="#F0F0F0"> </td>')
	SET @tableHTML = REPLACE(@tableHTML, '<td>RED</td>', '<td bgcolor="red"><b>RED</b></td>')
	SET @tableHTML = REPLACE(@tableHTML, '<td>YELLOW</td>', '<td bgcolor="yellow"><b>YELLOW</b></td>')

	EXEC msdb.dbo.sp_send_dbmail @profile_name = @profileName
		,@recipients = @emailList
		,@body = @tableHTML
		,@subject = @subjectMsg
		,@importance = 'High'
		,@body_format = 'HTML';
END
GO

USE [msdb]
GO

/****** Object:  Job [LowDiskSpaceAlert]    Script Date: 09/23/2016 19:03:39 ******/
BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/23/2016 19:03:39 ******/
IF NOT EXISTS (
		SELECT NAME
		FROM msdb.dbo.syscategories
		WHERE NAME = N'[Uncategorized (Local)]'
			AND category_class = 1
		)
BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
		,@type = N'LOCAL'
		,@name = N'[Uncategorized (Local)]'

	IF (
			@@ERROR <> 0
			OR @ReturnCode <> 0
			)
		GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'LowDiskSpaceAlert'
	,@enabled = 1
	,@notify_level_eventlog = 0
	,@notify_level_email = 0
	,@notify_level_netsend = 0
	,@notify_level_page = 0
	,@delete_level = 0
	,@description = N'No description available.'
	,@category_name = N'[Uncategorized (Local)]'
	,@owner_login_name = N'PL\admin.ph'
	,@job_id = @jobId OUTPUT

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

/****** Object:  Step [RunLowDiskSpaceAlertSP]    Script Date: 09/23/2016 19:03:40 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
	,@step_name = N'RunLowDiskSpaceAlertSP'
	,@step_id = 1
	,@cmdexec_success_code = 0
	,@on_success_action = 1
	,@on_success_step_id = 0
	,@on_fail_action = 2
	,@on_fail_step_id = 0
	,@retry_attempts = 0
	,@retry_interval = 0
	,@os_run_priority = 0
	,@subsystem = N'TSQL'
	,@command = N'[dbo].[LowDiskSpaceAlert]'
	,@database_name = N'Admin'
	,@flags = 0

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
	,@start_step_id = 1

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
	,@name = N'LowDiskSpaceAlertSchedule'
	,@enabled = 1
	,@freq_type = 4
	,@freq_interval = 1
	,@freq_subday_type = 4
	,@freq_subday_interval = 10
	,@freq_relative_interval = 0
	,@freq_recurrence_factor = 0
	,@active_start_date = 20160824
	,@active_end_date = 99991231
	,@active_start_time = 0
	,@active_end_time = 235959
	,@schedule_uid = N'a8831f35-e7a6-4880-bc2d-4d48aff82ff6'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
	,@server_name = N'(local)'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
	ROLLBACK TRANSACTION

EndSave:
GO