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!

SQL Saturday SP #424

Olá pessoal tudo certo? Espero que sim!

Bom como a maioria da galera que trabalha com o SQL Server deve saber, neste sábado rolou o SQL Saturday em SP e mais uma vez foi um evento de altissímo nível com diversos participantes do Brasil todo.

A dica que eu deixo é: PARTICIPEM! Vale muito a pena você passar um sábado todo mergulhado em palestras sobre o produto no qual você trabalha no dia a dia!

Abaixo está a lista de palestras que acompanhei e uma breve descrição:

Fabiano Amorim - Advanced Execution Plan

Como sempre o Fabiano mostrando o "0 e 1" do CE! O bacana além da palestra foi também ter ganho ao final um livro do próprio após fazer algumas perguntas :) (foto).

Marcelo Fernandes / Alex Silva - Entendendo o Clusterlog

Nesta palestra o Marcelo e Alex mostraram como você pode ir além em um troubleshooting de um ambiente Cluster através do seu log. Como um "+" na apresentação o Marcelo explicou sobre o backup do Cluster, como realiza-lo e o porque da sua importância.

Thiago Alencar / Liliam - SQL Server 2014, entendendo o novo CE, cálculo de estatisticas.

Nesta palestra o Thiago e a Liliam explicaram como passou a ser o cálculo das estatisticas com o CE do SQL Server 2014.

Ricardo Leka / Lucas - Vamos falar sobre TempDB

Nesta palestra o Leka e o Lucas falaram sobre a TempDB, muita coisa interna de como está base de sistema funciona e porque ela é vital para o SQL Server se manter funcionando.

Marcos Freccia - Criando um baseline do seu ambiente completo utilizando SSIS e SSRS.

Nesta palestra o Freccia mostrou como coletar dados de seu ambiente através de pacotes SSIS e de como montar reports com os mesmos.

Luan Moreno - Hekaton v.2 e ColumnStore Index v.3

Nesta palestra o Luan falou sobre as principais novidades do Hekaton e ColumnStore Index no SQL Server 2014 e 2016.

Todo material das palestras vai estar disponível para download no site do evento:

 
É isso pessoal! Até a próxima! Abraço!