CREATE procedure Alerta_Servidor_Cluster_Down
AS
-- Executar uma vez por minuto
declare @subject varchar(500),@Fl_Tipo tinyint
DECLARE @Reportdate DATETIME
DECLARE @AlertaLogHeader VARCHAR(MAX)
DECLARE @AlertaLogTable VARCHAR(MAX)
DECLARE @emptybody2 VARCHAR(MAX)
DECLARE @importance as varchar(6)
DECLARE @EmailBody VARCHAR(MAX)
if exists (
select *
from Sys.dm_os_cluster_nodes
where status_description <> 'up'
)
begin
select @Fl_Tipo = Fl_Tipo
from Alerta
where Id_Alerta = (select MAX(Id_Alerta) from Alerta where Nm_Alerta = 'Alerta_Servidor_Cluster_Down' )
if isnull(@Fl_Tipo,0) = 0 -- só manda alerta 1 vez
begin
SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)
SET @AlertaLogHeader=''
SET @AlertaLogHeader=@AlertaLogHeader+'
Status servidores do Cluster
'
SET @AlertaLogHeader=@AlertaLogHeader+''
SET @AlertaLogTable= cast( (
SELECT td = NodeName +
'
'+ Status_Description+
' | '+ cast(is_current_owner as varchar)+
+ ' | '
FROM (
select NodeName,Status_Description,is_current_owner
from Sys.dm_os_cluster_nodes
) as d order by NodeName
FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) )
SET @AlertaLogTable = REPLACE( replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) , ' | ', ' | ')
SET @AlertaLogTable= ''
+ '
Servidor |
Status |
Servidor Ativo |
'
+ replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' )
+ ' '
SET @emptybody2=''
SET @emptybody2 = ''
+
'
|
'
+ REPLACE( REPLACE( isnull(@emptybody2,''), '<', '<' ), '>', '>' )
+ ' '
/**************************************************************
Sending Email
***************************************************************/
SET @importance ='High'
SET @subject = 'ALERTA: Existe um servidor do cluster que não está UP na instância: ' + @@servername
SELECT @EmailBody =
@AlertaLogHeader + @emptybody2 + @AlertaLogTable+@emptybody2
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'seu profile',
@recipients = 'seu e-mail',
@subject = @subject ,
@body = @EmailBody ,
@body_format = 'HTML' ,
@importance=@importance
insert into Alerta (Nm_Alerta,Ds_Mensagem,Fl_Tipo)
select 'Alerta_Servidor_Cluster_Down',@subject, 1
end
end
else
begin
select @Fl_Tipo = Fl_Tipo
from Alerta
where Id_Alerta = (select MAX(Id_Alerta) from Alerta where Nm_Alerta = 'Alerta_Servidor_Cluster_Down' )
if @Fl_Tipo = 1
begin
SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)
SET @AlertaLogHeader=''
SET @AlertaLogHeader=@AlertaLogHeader+' Status servidores do Cluster '
SET @AlertaLogHeader=@AlertaLogHeader+''
SET @AlertaLogTable= cast( (
SELECT td = NodeName +
' | '+ Status_Description+
' | '+ cast(is_current_owner as varchar)+
+ ' | '
FROM (
select NodeName,Status_Description,is_current_owner
from Sys.dm_os_cluster_nodes
) as d order by NodeName
FOR XML PATH( 'tr' ), type ) AS VARCHAR(MAX) )
SET @AlertaLogTable = REPLACE( replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' ) , ' | ', ' | ')
SET @AlertaLogTable= ''
+ '
Servidor |
Status |
Servidor Ativo |
'
+ replace( replace( @AlertaLogTable, '<', '<' ), '>', '>' )
+ ' '
SET @emptybody2=''
SET @emptybody2 = ''
+
'
|
'
+ REPLACE( REPLACE( isnull(@emptybody2,''), '<', '<' ), '>', '>' )
+ ' '
SET @importance ='High'
SET @subject = 'CLEAR: Todos os servidores do cluster estão UP na instância:' + @@servername
DECLARE @recipientsList varchar(8000)
SELECT @EmailBody =
@AlertaLogHeader + @emptybody2 + @AlertaLogTable+@emptybody2 -- espaço em disco
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'seu profile',
@recipients = 'seu e-mail',
@subject = @subject ,
@body = @EmailBody ,
@body_format = 'HTML' ,
@importance=@importance
insert into Alerta (Nm_Alerta,Ds_Mensagem,Fl_Tipo)
select 'Alerta_Status_Membros_Cluster',@subject, 0
end
end |