[SQL Server] Envoyer Mail ou SMS aux membre d'un groupe AD
CREATE PROCEDURE [dbo].[usp_EnvoieMailSMS]
@NOM_PROFIL SYSNAME,
@NOM_GROUP_DESTINATAIRE AS VARCHAR(MAX),
@OBJET_MAIL AS NVARCHAR(255),
@CONTENU_MAIL AS NVARCHAR(MAX)
AS
/*
Auteur : KHARROUBI Zouhaier
Date Modification : 14 Juin 2010
Objectif : Cette procédure permet l'envois des E-mails ou SMS vers un groupe d'utilisateur
Exemple : usp_EnvoisMailSMS 'EnvoiSMS','SMSGroupeAD_1','test envoi SMS', 'Bonjour, voici un test envoi'
usp_EnvoisMailSMS 'EnvoiSMS','SMSGroupeAD_2','test envoi SMS', 'Bonjour, voici un test envoi'
usp_EnvoisMailSMS 'EnvoiSMS','Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.','test envoi SMS', 'Bonjour, voici un test envoi'
*/
DECLARE @PathAD AS VARCHAR(MAX)
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @SQL_FINAL AS VARCHAR(MAX)
DECLARE @NUM_PORT_ADR_MAIL AS CHAR(25)
DECLARE @LIST_DESTINATAIRE AS VARCHAR(MAX)
DECLARE @SEPARATEUR AS CHAR(25)
DECLARE @DEBUT_ADRESSE AS CHAR(4)
DECLARE @POSITION_AT AS TINYINT
SET @LIST_DESTINATAIRE=''
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#NUM_PORTABLE_ADR_MAIL]'))
DROP TABLE #NUM_PORTABLE_ADR_MAIL
CREATE TABLE #NUM_PORTABLE_ADR_MAIL
(
NUM_PORT_ADR_MAIL CHAR(36)
,userAccountControl BIGINT
)
--Creation/Alimentation Table #ListActiveDirectoryGroups.
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListActiveDirectoryGroups]'))
DROP TABLE #ListActiveDirectoryGroups
CREATETABLE #ListActiveDirectoryGroups
(
[NomGroup] [nchar](25) NULL,
[ADsPath] [nvarchar](350) NULL
)
SET @POSITION_AT = CHARINDEX('@',@NOM_GROUP_DESTINATAIRE,0)
IF @POSITION_AT=0
--Le destinataire est un groupe ActiveDirectory
BEGIN
SET @SQL ='INSERT INTO #ListActiveDirectoryGroups (NomGroup,ADsPath) VALUES ('''+ @NOM_GROUP_DESTINATAIRE +''''
SET @SQL = @SQL + ',''LDAP://cn=' + @NOM_GROUP_DESTINATAIRE + ',cn=Users,dc=corp,dc=kharroubi,dc=com'')'
EXECUTE (@SQL )
SELECT
@PathAD = REPLACE(ADsPath, 'LDAP://', '')
FROM
#ListActiveDirectoryGroups
WHERE
NomGroup =@NOM_GROUP_DESTINATAIRE
IF @NOM_PROFIL = 'EnvoiSMS'
BEGIN
--Envoyer des SMS
IF @NOM_GROUP_DESTINATAIRE='SMSGroupeAD_1'
BEGIN
--Pour les membres de ce groupe remplacer + par 00
SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT REPLACE(USR.MOBILE,''+'',''00''),USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '
END
ELSE
BEGIN
SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT USR.MOBILE,USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '
END
SET @SEPARATEUR = '@kharroubi.activmail.net;'
SET @DEBUT_ADRESSE = 'SMS.'
END
ELSE
BEGIN
--Envoyer des Mails
SET @SQL = 'INSERT INTO #NUM_PORTABLE_ADR_MAIL SELECT USR.MAIL,USR.userAccountControl FROM OpenQuery(SERVEUR_AD, '
SET @SEPARATEUR = ';'
SET @DEBUT_ADRESSE = ''
END
SET @SQL_FINAL = @SQL +'''SELECT sAMAccountName, Adspath '
SET @SQL_FINAL = @SQL_FINAL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '
SET @SQL_FINAL = @SQL_FINAL +'where memberOf = '''''+ @PathAD +''''''') AD '
SET @SQL_FINAL = @SQL_FINAL + 'INNER JOIN ( SELECT ADsPath AS ADsPath,'
SET @SQL_FINAL = @SQL_FINAL + 'title AS TITRE, givenName AS PRENOM,'
SET @SQL_FINAL = @SQL_FINAL + 'sn AS NOM, displayName,'
SET @SQL_FINAL = @SQL_FINAL + 'sAMAccountName AS LOGIN, telephoneNumber AS TELEPHONE,'
SET @SQL_FINAL = @SQL_FINAL + 'facsimileTelephoneNumber AS FAX, mobile AS MOBILE, mail AS MAIL,userAccountControl '
SET @SQL_FINAL = @SQL_FINAL + 'FROM OpenQuery(SERVEUR_AD,'
SET @SQL_FINAL = @SQL_FINAL +'''SELECT ADsPath, title, displayName, sAMAccountName,'
SET @SQL_FINAL = @SQL_FINAL +'givenName, telephoneNumber, facsimileTelephoneNumber, sn, mobile, mail,userAccountControl '
SET @SQL_FINAL = @SQL_FINAL +'FROM ''''LDAP://DC=corp,DC=kharroubi,DC=com'''' '
SET @SQL_FINAL = @SQL_FINAL +'where objectClass = ''''User'''' AND objectCategory = ''''Person'''''')) USR'
SET @SQL_FINAL = @SQL_FINAL +' ON AD.Adspath = USR.ADsPath'
EXECUTE (@SQL_FINAL)
--supprimer les NUM_PORTABLE_ADR_MAIL des comptes désactivés
DELETE FROM #NUM_PORTABLE_ADR_MAIL WHERE userAccountControl & 2 = 2
SELECT @LIST_DESTINATAIRE = RTRIM(LTRIM(@LIST_DESTINATAIRE)) + RTRIM(LTRIM(@DEBUT_ADRESSE)) + RTRIM(LTRIM(NUM_PORT_ADR_MAIL)) + RTRIM(LTRIM(@SEPARATEUR))
FROM(
SELECT DISTINCT * FROM #NUM_PORTABLE_ADR_MAIL
) T
WHERE NUM_PORT_ADR_MAIL IS NOT NULL
SELECT @LIST_DESTINATAIRE=SUBSTRING(@LIST_DESTINATAIRE,1,LEN(@LIST_DESTINATAIRE)-1) END
ELSE
--Le destinataire est une liste des adresses ou des numéro
BEGIN
SELECT @LIST_DESTINATAIRE=@NOM_GROUP_DESTINATAIRE
END
DROP TABLE #NUM_PORTABLE_ADR_MAIL
DROP TABLE #ListActiveDirectoryGroups
EXEC msdb..sp_send_dbmail@profile_name=@NOM_PROFIL, @recipients=@LIST_DESTINATAIRE, @subject=@OBJET_MAIL,@body=@CONTENU_MAIL