[SQL Server] Envoyer par mail le rapport des espaces disques pour les serveurs SQL Server liés
---- =============================================
---- Author: Zouhaier KHARROUBI
---- Create date: 30 Mai 2016
---- Description: Envoyer par mail le rapport des espaces disques pour les serveurs SQL Server liés
---- =============================================
CREATE PROCEDURE [dbo].[usp_EspaceDisqueRapport]
@SERVEURS NVARCHAR(500)=NULL
,@Envoyer_Mail_Rapport BIT=0
--WITH EXECUTE AS OWNER -- Cette option est utlisée seulement en cas d'utilisation de OPENROWSET
AS
SET NOCOUNT ON
SET FMTONLY OFF;
DECLARE @Nom_Serveur VARCHAR(25)
, @REQUETE NVARCHAR(600)
, @tableHTML NVARCHAR(MAX)
, @Nbre_Serveur INT
, @Num_Serveur INT
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#ESPACE_DISQUE_DISPONIBLE]'))
BEGIN
DROP TABLE #ESPACE_DISQUE_DISPONIBLE
END
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#TABLE_TEMP]'))
BEGIN
DROP TABLE #TABLE_TEMP
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#NOM_VOLUME]'))
BEGIN
DROP TABLE #NOM_VOLUME
END
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#OUT_VOLUME]'))
BEGIN
DROP TABLE #OUT_VOLUME
END
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Liste_Serveurs]'))
BEGIN
DROP TABLE #Liste_Serveurs
END
CREATE TABLE #ESPACE_DISQUE_DISPONIBLE
(
SERVER_NAME NVARCHAR(35),
LECTEUR CHAR(1),
ESPACE_DISPONIBLE_GO FLOAT, --espace récupérée en GO
NOM_VOLUME VARCHAR (100)
)
CREATE TABLE #TABLE_TEMP
(
LECTEUR CHAR(1),
ESPACE_DISPONIBLE_GO FLOAT --espace récupérée en GO
)
CREATE TABLE #NOM_VOLUME
(
Caption CHAR(1),
VolumeName VARCHAR (100) NULL
)
CREATE TABLE #OUT_VOLUME
(
NUM_LIG INT IDENTITY(1,1),
LIGNE VARCHAR (MAX) NULL
)
CREATE TABLE #Liste_Serveurs
(
Num_Serveur INT IDENTITY(1,1),
Nom_Serveur NVARCHAR(150)
)
IF @SERVEURS IS NOT NULL
BEGIN
SELECT @SERVEURS = @SERVEURS + ','
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT * FROM dbo.ufn_CompilerParametreChaineSSRS(@SERVEURS)
END
ELSE
BEGIN
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT '[' + REPLACE(REPLACE(data_source,'[',''),']','') + ']' Nom_Serveur from sys.servers
WHERE product='SQL Server'
AND data_source NOT IN('SERVEUR_TEST1','ERVEUR_TEST2','ERVEUR_TEST2')
END
SELECT @Nbre_Serveur = COUNT(*) FROM #Liste_Serveurs
SELECT @Num_Serveur = 1
WHILE @Num_Serveur <= @Nbre_Serveur
BEGIN
SELECT @Nom_Serveur = Nom_Serveur FROM #Liste_Serveurs WHERE Num_Serveur=@Num_Serveur
--recuperer les tailles des lecteurs disque
--Récupérer les espaces disques
--SET @REQUETE = N'INSERT INTO #TABLE_TEMP '
--SET @REQUETE = @REQUETE + N'SELECT * '
--SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Nom_Serveur + N';Trusted_Connection=yes;'','
--SET @REQUETE = @REQUETE + N'''SET FMTONLY OFF; EXEC master.dbo.xp_fixeddrives '')'
--EXEC (@REQUETE)
----Récupérer les noms des espaces disque
--SET @REQUETE = N'INSERT INTO #OUT_VOLUME '
--SET @REQUETE = @REQUETE + N'SELECT * '
--SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Nom_Serveur + N';Trusted_Connection=yes;'','
--SET @REQUETE = @REQUETE + N'''SET FMTONLY OFF; EXEC sys.xp_cmdshell ''''wmic logicaldisk get caption,volumename'''''')'
SET @REQUETE = N'INSERT INTO #TABLE_TEMP '
SET @REQUETE = @REQUETE + N'SELECT * '
SET @REQUETE = @REQUETE + N'FROM OPENQUERY(' + @Nom_Serveur + N','''
SET @REQUETE = @REQUETE + N'SET FMTONLY OFF; EXEC master.dbo.xp_fixeddrives '')'
EXEC (@REQUETE)
--Récupérer les noms des espaces disque
SET @REQUETE = N'INSERT INTO #OUT_VOLUME '
SET @REQUETE = @REQUETE + N'SELECT * '
SET @REQUETE = @REQUETE + N'FROM OPENQUERY(' + @Nom_Serveur + N','''
SET @REQUETE = @REQUETE + N'SET FMTONLY OFF; EXEC sys.xp_cmdshell ''''wmic logicaldisk get caption,volumename'''''')'
EXEC (@REQUETE)
---Récupérer le nom de volume
INSERT INTO #NOM_VOLUME(Caption,VolumeName)
SELECT RTRIM(LTRIM(SUBSTRING(Ligne,0,CHARINDEX(':',Ligne,0))))
,RTRIM(LTRIM(SUBSTRING(Ligne,CHARINDEX(':',Ligne,0)+1,LEN(Ligne)-CHARINDEX(':',Ligne,0)))) FROM #OUT_VOLUME
WHERE CHARINDEX(':',Ligne,0)>0 AND CHARINDEX(':',Ligne,0) IS NOT NULL
INSERT INTO #ESPACE_DISQUE_DISPONIBLE
SELECT DISTINCT @Nom_Serveur,T2.Caption , T1.ESPACE_DISPONIBLE_GO, T2.VolumeName FROM #TABLE_TEMP T1
JOIN #NOM_VOLUME T2 ON RTRIM(LTRIM(T2.Caption))=RTRIM(LTRIM(T1.LECTEUR))
TRUNCATE TABLE #TABLE_TEMP
TRUNCATE TABLE #NOM_VOLUME
TRUNCATE TABLE #OUT_VOLUME
SELECT @Num_Serveur = @Num_Serveur + 1
END
-- Envoi du rapport pour les espaces disques pour tous les serveurs SQLServer
SET @tableHTML = N'<H><b>Bonjour</b><BR></BR></H>' +
N'<H>Veuillez trouver ci-dessous le rapport des espaces disques pour tous les serveurs SQL Server : <BR></BR></H>'
DECLARE liste_serveurs CURSOR FOR
SELECT DISTINCT Nom_Serveur FROM #Liste_Serveurs
OPEN liste_serveurs
FETCH NEXT FROM liste_serveurs INTO @Nom_Serveur
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tableHTML = @tableHTML + N'<H><b><BR><font color="blue" size="5">' + REPLACE(REPLACE(@Nom_Serveur,'[',''),']','') + ' : </font></BR></b></H>'
SET @tableHTML = @tableHTML + N'<table border="0" cellspacing="1" cellpadding="1">' +
N'<tr><th bgcolor="#4340A4"><font color="white">N°</font></th><th bgcolor="#4340A4"><font color="white">Lecteur</font></th><th bgcolor="#4340A4"><font color="white">Espace Disponible</font></th><th bgcolor="#4340A4"><font color="white">Nom Volume</font></th>'
SET @tableHTML = @tableHTML +
CAST(( SELECT [td/@style] =CASE WHEN ESPACE_DISPONIBLE_GO/1024 < 4 THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,
td = ROW_NUMBER() OVER(ORDER BY SERVER_NAME,LECTEUR ASC) , '',
[td/@style] =CASE WHEN ESPACE_DISPONIBLE_GO/1024 < 4 THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,
td = LECTEUR, '',
[td/@style] =CASE WHEN ESPACE_DISPONIBLE_GO/1024 < 4 THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,
td = CAST(ESPACE_DISPONIBLE_GO/1024 AS NUMERIC(24,2)), '',
[td/@style] =CASE WHEN ESPACE_DISPONIBLE_GO/1024 < 4 THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,
td = NOM_VOLUME FROM #ESPACE_DISQUE_DISPONIBLE WHERE SERVER_NAME=@Nom_Serveur
ORDER BY SERVER_NAME,LECTEUR ASC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
SET @tableHTML = @tableHTML + N'</table>'
FETCH NEXT FROM liste_serveurs INTO @Nom_Serveur
END
IF @Envoyer_Mail_Rapport=1
BEGIN
SET @tableHTML = @tableHTML +
N'</table>' +
N'<H><BR></BR></H>' +
N'<H>Merci d''analyser et résoudre le problème des espaces disques.<BR></BR></H>' +
N'<H>Cordialement,<BR></BR></H>' +
N'<H>Service Informatique</H>' ;
EXEC msdb..sp_send_dbmail@profile_name='MektabaSqlServerProfilMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@subject='Espaces disques serveurs SQL Server', @body= @tableHTML, @body_format = 'HTML' ;
DROP TABLE #ESPACE_DISQUE_DISPONIBLE
END
ELSE
BEGIN
SELECT SERVER_NAME,LECTEUR,CAST(ESPACE_DISPONIBLE_GO/1024 AS NUMERIC(24,2)) AS ESPACE_DISPONIBLE_GO,NOM_VOLUME
FROM #ESPACE_DISQUE_DISPONIBLE ORDER BY SERVER_NAME,LECTEUR ASC
END
CLOSE liste_serveurs
DEALLOCATE liste_serveurs