Store Procedure untuk report informasi hardisk dari suatu server, report by Email - SQL Server

USE [DATABASENYA]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SendEmailHardiskSize]     
as     
begin        
 SET NOCOUNT ON;     
   
     
DECLARE @XML1 NVARCHAR(MAX)   
DECLARE @XML2 NVARCHAR(MAX)   
 
DECLARE @BODY NVARCHAR(MAX)
DECLARE @TableBODY NVARCHAR(MAX)

DECLARE @IP_Addr VARCHAR(25)
DECLARE @intFlag INT
DECLARE @Count INT
DECLARE @Warna INT
SET @intFlag = 1


DECLARE @TABLE1 TABLE (     
    [IP] varchar(20)     
    ,[Host Name] varchar(30)
    ,[Drive] varchar(30)
    ,[Type] varchar(30)
    ,[Label] varchar(30)
    ,[Format] varchar(30)
    ,[Total Size] varchar(30)
    ,[Free Size] varchar(30)
    ,[Free %] varchar(30)
    ,[Use Size] varchar(30)
    ,[Use %] varchar(30)
    ,[BgRow] INT 
    ) 

select @Count=COUNT(IP_Addr) from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
    where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
        and [typeOf] <> 'CDRom'

    DECLARE SENDHDD CURSOR FOR
        select distinct IP_Addr from SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
            where CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                and [typeOf] <> 'CDRom'
            order by IP_Addr;
    OPEN SENDHDD
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @Warna = (@Count-@intFlag) % 2
       
        INSERT INTO @TABLE1   
            select  IP_Addr IP,
            Hostname [Host Name],
            drive [Drive],
            typeOf [Type],
            labels [Label],
            formats [Format],
            totalSize [Total Size],
            freeSize [Free Size],
            freePercent [Free %],
            useSize [Use Size],
            usePercent [Use %],
            @Warna
            from
                SERVER_A@DB_SERVER_A.DB_SERVER_A.dbo.HDD_Inf
                    WHERE IP_Addr=@IP_Addr
                    AND CONVERT(varchar,tgl,112) = CONVERT(varchar,GETDATE(),112)
                    AND [typeOf] <> 'CDRom'                   
        SET @intFlag = @intFlag + 1
    FETCH NEXT FROM SENDHDD INTO @IP_Addr
    END
    CLOSE SENDHDD
        DEALLOCATE SENDHDD
       
SET @XML1 =
        CAST ((SELECT
                case when [BgRow]=0 then '#90EE90' else '#FFDAB9' end AS [@bgcolor],
                td = [IP],'',
                td =  [Host Name],'',
                td =  [Drive],'',
                td =  [Type],'',
                td =  [Label],'',
                td =  [Format],'',
                td =  [Total Size],'',
                td =  [Free Size],'',
                td =  [Free %],'',
                td =  [USE Size],'',
                td =  [Use %],''
                    FROM (select [IP],[Host Name],[Drive],[Type]
                            ,case when [Label] = '' then '-' else [Label] end [Label]
                            ,case when [Format] = '' then '-' else [Format] end [Format]
                            ,case when [Total Size] = '' then '-' else [Total Size] end [Total Size]
                            ,case when [Free Size] = '' then '-' else [Free Size] end [Free Size]  
                            ,case when [Free %] = '' then '-' else [Free %] end [Free %]
                            ,case when [USE Size] = '' then '-' else [USE Size] end [USE Size]
                            ,case when [Use %] = '' then '-' else [Use %] end [Use %]
                            ,[BgRow]
                            from @TABLE1
                            )t FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX))

---to search where low space
SET @XML2 = CAST(( select  [IP] AS 'td'     
                    ,'',[Host Name] AS 'td'     
                    ,'',[Drive] AS 'td'
                    ,'',[Type] AS 'td'
                    ,'',case when [Label] = '' then '-' else [Label] end AS 'td'
                    ,'',case when [Format] = '' then '-' else [Format] end AS 'td'
                    ,'',case when [Total Size] = '' then '-' else [Total Size] end AS 'td' 
                    ,'',case when [Free Size] = '' then '-' else [Free Size] end AS 'td' 
                    ,'',case when [Free %] = '' then '-' else [Free %] end AS 'td' 
                    ,'',case when [USE Size] = '' then '-' else [USE Size] end AS 'td'
                    ,'',case when [Use %] = '' then '-' else [Use %] end AS 'td'
                                       
from @TABLE1 
where
Label not like '%VM%'
      AND Label not like '%VIRTUAL%'
      and cast(REPLACE([Free %],'%','') as int) <=10
                               
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))     
   
---to create row of table low space
set @TableBODY = '
Data Hardisk yang kurang dari 10%
<br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>' +  @XML2 +
'</table><br>'


declare @Urgent varchar(5)
if @TableBODY is not null
begin
    set @Urgent= 'High'
end
else set @Urgent= 'Low'

 if @TableBODY  is null
 set @TableBODY='
 Data Hardisk yang kurang dari 10%
 <br> <table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
 </tr>
 <tr>     
    <th>-</th>
    <th>-</th> 
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
    <th>-</th>
 </tr>
 ' +
'</table><br>'      
     
SET @BODY ='<html><body>     
<style>body,th,td {font-family :verdana; font-size:8pt;}     
    </style>  
Berikut ini adalah data HDD Space Report.<br>
Status HDD Storage Space digunakan untuk memonitoring dan memeriksa sisa Hardisk <br>
pada server Toko dan Server HO. Jika Space drive C: atau D: Jumlah Free space nya <br>
kurang dari 10% maka perlu dilakukan pengecekan hardisk agar space-nya cukup.<br>      
Periode : '+     
CONVERT(VARCHAR(11),GETDATE(),106)+     
 '<br>     
<br>
<br>
'
+ @TableBODY +
'  
<br>
Data Hardisk Size Keseluruhan
<br>
<table border = 1>      
<tr>     
    <th>Site Id</th>
    <th>Site Name</th> 
    <th>Status</th>
    <th>Type</th>
    <th>Label</th>
    <th>Format</th>
    <th>Total Space</th>
    <th>Free Space</th>
    <th>Free %</th>
    <th>Use Space</th>
    <th>Use %</th>
   
</tr>'     
SET @BODY = @BODY + @XML1 +'</table>'+     
 '<br><br>     
Email ini dikirimkan oleh System.<br><br>     
Procedure Name : DATABASENYA.[dbo].[SendEmailHardiskSize]     
</body></html>';   
     
--SEND EMAIL     
  EXEC msdb.dbo.sp_send_dbmail     
  @profile_name = 'Report Services',     
  @body = @body,     
  @body_format ='HTML',
  @importance =@Urgent,
  @recipients = 'noname@gmail.com',          
  @subject = 'Report Monitoring Hardisk Size ' ;     
     
end  ;   

GO