|
|
| Definitions More Info. | | Definition ID | 744 | | Title | SQL | | Category | SCRIPT | | Definition | DBA_DiskCapacityControler FOR MAIL | | Definition Description | USE [dpAudit]
GO
/****** Object: StoredProcedure [dbo].[DBA_DiskCapacityControler] Script Date: 5.9.2016 15:40:37 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DBA_DiskCapacityControler] AS
BEGIN
SET NOCOUNT ON
DECLARE @IP VARCHAR(100),
@svrName VARCHAR(255),
@sql VARCHAR(400),
@tableHTML NVARCHAR(MAX),
@subject1 VARCHAR(200),
@recipients CHAR(100),
@subject CHAR(250),
@body CHAR (250)
--by default it will take the current server name, we can then set the server name as well
SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"'
-- Mail Konusunu Set etme
SELECT @IP = ( SELECT TOP(1) local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL)
SELECT @subject1 = @IP + '_' + @svrName + ' SQL WARNING!!!'
--creating a temporary table
DECLARE @output TABLE (line VARCHAR(255))
INSERT @output EXEC xp_cmdshell @sql
--PRINT (@sql)
DECLARE @DISKS TABLE( id INT IDENTITY ,
[DiskName] VARCHAR(10) ,
[Capacity(MB)] bigint ,
[FreeSpace(MB)] bigint ,
[Label] VARCHAR(100) )
INSERT INTO @DISKS
SELECT rtrim(ltrim(SUBSTRING(line, 1, CHARINDEX('|', line) -1))) AS drivename ,
round(CAST(rtrim(ltrim(SUBSTRING(line, CHARINDEX('|', line)+ 1, (CHARINDEX('%', line) -1)-CHARINDEX('|', line)) )) AS Float), 0) AS 'capacity(MB)' ,
round(CAST(rtrim(ltrim(SUBSTRING(line, CHARINDEX('%', line)+ 1, (CHARINDEX('&', line) -1)-CHARINDEX('%', line)) )) AS Float), 0) AS 'freespace(MB)' ,
rtrim(ltrim(SUBSTRING(line, CHARINDEX('&', line)+ 1, (CHARINDEX('*', line) -1)-CHARINDEX('&', line)) )) AS 'Label'
FROM @output
WHERE line LIKE '[A-Z][:]%'
ORDER BY drivename
-- Check TempTable IF EXISTS
IF OBJECT_ID('tempdb..#MonitorDiskCapacity') IS NOT NULL DROP TABLE #MonitorDiskCapacity
SELECT [DiskName],
[Capacity(MB)],
[FreeSpace(MB)],
[Label],
[Capacity(MB)] / 1024 AS [Capacity(GB)],
[FreeSpace(MB)] / 1024 AS [FreeSpace(GB)] ,
round( [FreeSpace(MB)] * 100 / [Capacity(MB)], 2) AS [% Free]
INTO #MonitorDiskCapacity
FROM @DISKS
-- Log Tablosuna Insert Etme
INSERT INTO dpAudit.dbo.MonitorDiskCapacity ([DiskName],
[Capacity(MB)],
[FreeSpace(MB)],
[Label],
[Capacity(GB)],
[FreeSpace(GB)],
[% Free])
SELECT [DiskName],
[Capacity(MB)],
[FreeSpace(MB)],
[Label],
[Capacity(MB)] / 1024 AS [Capacity(GB)],
[FreeSpace(MB)] / 1024 AS [FreeSpace(GB)],
round( [FreeSpace(MB)] * 100 / [Capacity(MB)], 2) AS [% Free]
FROM #MonitorDiskCapacity
WHERE ([% Free] <= 10)
BEGIN
SELECT
@tableHTML = N'' + @IP + '_' + @svrName + ' DISK UYARILARI' + N'' + N'| DiskName | ' + N'Capacity(MB) | ' + N'FreeSpace(MB) | ' + N'Label | ' + N'Capacity(GB) | ' + N'FreeSpace(GB) | ' + N'% Free | ' + N'LogDate | ' + CAST ( (
SELECT td = [DiskName],'',
td = CAST([Capacity(MB)] AS char(10))+ 'MB','',
td = CAST([FreeSpace(MB)] AS char(10))+ 'MB','',
td = [Label],'',
td = CAST([Capacity(GB)] AS char(5))+ 'GB','',
td = CAST([FreeSpace(GB)] AS char(5))+ 'GB','',
td = CAST([% Free] AS char(5))+ 'GB','',
td = [LogDate],''
FROM [MonitorDiskCapacity]
WHERE ([% Free] <= 10)
AND [IsMailed] = 0 FOR XML PATH('tr'),TYPE) AS NVARCHAR(MAX)) + N'
|---|
' + N'
' + N'
|
|
|
|
|
|
|
