Monitore o Event Viewer combinando o SQL + Powershell + Database Mail!

Olá pessoal tudo certo? Espero que sim!

Recentemente tenho me aventurado com algumas queries SQL Server combinadas com o Powershell e o resultado tem sido bem interessante. Vou compartilhar com vocês uma procedure que desenvolvi que realiza uma varredura do log de sistema do Event Viewer e envia um e-mail automaticamente para o time de DBA’s caso as condições dentro do IF/ELSE sejam atendidas. Este script pode ser uma saída simples de monitoração caso o local em que você trabalhe não possua softwares que realizem este trabalho.

Para que esta procedure funcione corretamente é necessário:

Definir uma base de dados onde ela será criada.
Powershell v.2.0 >
Diretório: C:\Temp (ou qualquer outro em que deseje colocar o arquivo csv que o Powershell irá gerar).
Database Mail do SQL Server devidamente configurado (Veja o link http://sqlmagu.blogspot.com.br/2015/06/problema-database-mail-x-smtp.html).
Permissões de BULK INSERT para a conta que rodará a procedure.
‘show advanced options’ habilitado, esta opção pode ser acessada via “sp_configure”.
‘xp_cmdshell’ habilitado no SQL Server para rodar o comando do Powershell, igual ao item anterior, esta opção pode ser acessada via “sp_configure”.

Como é feita a varredura do Event Viewer com o Powershell?

A coleta da informação é feita através da cmdlet Get-Eventlog, a qual tem a função especifica de fazer a leitura de diversos logs existentes no Windows (https://technet.microsoft.com/en-us/library/hh849834.aspx).

Condicionais da procedure:

Qual período de busca no Event Viewer?
Em dias.
Quais eventos devem ser procurados no Event Viewer?
Já deixei alguns pré-definidos no código.
Para qual ou quais e-mails serão enviados os alertas?
Definido na criação da procedure.

Abaixo esta o código e um exemplo do e-mail de alerta:

USE [MAGUDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*****************************************************************************************************

Monitoração dos erros do Event Viewer (SYSTEM) com o Powershell.
Criação: 17/08/2015
Atualização: 23/09/2015
Autor: André César Rodrigues

@Dias = Deve-se informar a quantidade de dias no passado que o PowerShell deverá ler do EventViewer.
@emails = Deve-se definir a lista de destinatários.

Exemplo de execução: exec stp_Verifica_EventViewer 1

******************************************************************************************************/

CREATE PROCEDURE [dbo].[stp_Verifica_EventViewer] (@Dias INT, @emails NVARCHAR(MAX)='testmail@sqlmagu.com.br')
AS
BEGIN

PRINT 'Dias configurados para busca: ' + convert(varchar(10),@Dias)

SET NOCOUNT ON

--Declaração das váriaveis...

DECLARE @eventlist NVARCHAR(MAX)
DECLARE @cmd VARCHAR(8000)

--Cria tabela temporária que irá armazenar os eventos...

CREATE TABLE #ErrosEvtVwr
(
ID_Evento NVARCHAR(max) null,
Ocorreu_em NVARCHAR(max) null,
Mensagem NVARCHAR(max) null
)

--Abaixo está a lista de eventos a serem buscados no Event Viewer, estes devem estar com "" pois serão processados pelo Powershell...

SET @eventlist = '"55","1001","1006","1037","1038","1069","1074","1119","1127","1135","1146","1168","1172","1177","1183","1196","1205","1230","1564","6005","6006","6009","7024","7031","7034"'

PRINT 'Eventos que foram buscados: ' + @eventlist

--Abaixo atribuo a váriavel @cmd o comando do PowerShell, notem que no meio do comando se espera o valor da
--váriavel @Dias, ou seja, para executar a procedure você deve informar a quantidade de dias passados para o EventViewer ser lido...

SELECT @cmd = 'powershell -command "$eventLog = Get-EventLog -Log System -After (Get-Date).AddDays(-' + convert(varchar(10),@Dias) + ') | Select-Object EventID, TimeGenerated,Message;$list = @(' + @eventlist + ');$events = @();foreach($event in $list){$events += $eventLog | Where {$_.EventID -eq $event}}$events | Export-CSV c:\temp\erros.csv -noTypeInformation -ErrorAction SilentlyContinue'

--Execução do cmd preparado acima...

EXEC xp_cmdshell @cmd,no_output

--Insert na tabela baseado no csv gerado pelo PS...

BEGIN TRY
    BULK
          INSERT #ErrosEvtVwr
          FROM 'c:\temp\erros.csv' --Diretório de sua preferência...
          WITH
          (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '\n'
          )
END TRY
BEGIN CATCH
    BULK
          INSERT #ErrosEvtVwr
          FROM 'c:\temp\erros.csv' --Diretório de sua preferência...
          WITH
          (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '''+CHAR(10)+''' --Proteção contra um erro ao realizar o Bulk de um arquivo CSV...
          )
END CATCH;

--Se houver eventos (contagem de linhas >0), prepara e envia o e-mail...

IF (Select count(1) from #ErrosEvtVwr) >0
BEGIN

DECLARE @subject NVARCHAR(MAX)
DECLARE @servername NVARCHAR(150)

SET @servername = @@SERVERNAME
SET @subject = 'Erros registrados no EventViewer (SYSTEM) - Instância: ' + @servername

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Erros registrados no EventViewer (SYSTEM) - Instância: ' + @servername + '</H1>' +
          N'<table border="1">' +

    CAST ( ( SELECT td = ID_Evento, '',
                                                td = Ocorreu_em, '',
                                                td = Mensagem, ''
              FROM #ErrosEvtVwr
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'</table>';

          EXEC msdb.dbo.sp_send_dbmail @recipients=@emails,
    @subject = @subject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
PRINT 'E-mail enviado para: ' + @emails
END
ELSE -- Se não houver eventos, nenhum e-mail é enviado...
PRINT 'Os eventos buscados não foram encontrados no Event Viewer de Sistema no período configurado!'

PRINT 'Fim da execução!'

--Drop da tabela temporária criada no começo do script...

DROP TABLE #ErrosEvtVwr

END
GO

Qualquer problema com o script por favor reportem e fiquem a vontade para mudarem o código =)

É isso pessoal! Um abraço e até a próxima!

2 comentários:

  1. hi
    I think there is a problem with the spaces.
    can you check it, or upload a script file?


    powershell -command "$EventLog = Get-EventLog-Log System-after(Get-Date).AddDays(-1) | Select-Object EventID, TimeGenerated, Message, $list = @("55","1001","1006","1037","1038","1069","1074","1119","1127","1135","1146","1168","1172","1177","1183","1196","1205","1230","1564","6005","6006","6009","7024","7031","7034"); $events = @();. foreach ($event in $list) {$events += $EventLog | Where {$_eq $EventID event }} $events | Export-CSV c:\temp\erros.csv -noTypeInformation -ErrorAction SilentlyContinue

    ResponderExcluir
  2. Hello Sharon! I tested the script and it works well! If you want the sql file just send me an email =)

    Sorry about the late in the answer!

    Regards,

    André

    ResponderExcluir