25 Outubro 2019

Alarmistica via SSIS

 

Introdução

 

Este módulo reutilizável e transversal permite gerir e executar “Alertas”.

Mas… o que é um Alerta???

Um “Alerta” está relacionado com a Monitorização de uma situação em específico que, caso se verifique, implicará o despoletamento de um alerta sob variadas formas sendo a mais usual, um EMAIL.

No desenho desta solução foi tida a preocupação de ser um módulo:

1) Transversal

2) Reutilizável

3) Flexível em termos de configuração para qualquer tipo de aplicação

 

…Um caso possível de alerta, pedido pela equipa de negócio permite validar, após o processo de carregamento de informação, se uma dada lista de agentes está mapeada numa outra base de dados: caso não esteja, implicará o envio de um alerta sob a forma de email para um destinatário…

 

Arquitetura de Componentes

 

 

 

 

 

 

A arquitetura que foi desenhada e montada pode vislumbrar-se na imagem seguinte (implementado via ferramenta SQL Server Integration Services):

 

 

[1] Componentes

Os componentes que definem o serviço deste módulo correspondem a pacotes de SQL Server Integration Services (SSIS), organizados na secção [1a]

 

Devem ser instalados na pasta do catálogo SSIS (Project Deployment Model) “R-eusable F-ramework C-omponent”:  esta pasta agrupa todos os componentes que sejam Reutilizáveis (Framework RFC) evitando, assim, a duplicação de funcionalidades pelos variados projetos só trazendo benefícios!

 

SSIS Catalog com RFC Folder

Exemplo de mais soluções RFC

[1a] 

Os componentes que definem o sistema “Core” deste serviço de Monitorização e Alertas são:

1) RFC_AlertsListener

2) RFC_AlertsDispatcher

Por ordem:

(i) Gere o processo de “trigger” do alerta: pode ser acionado em dois modos: ou M-anualmente (designado também de “On Demand“) por invocação direta ou A-utomaticamente, acionado por SQL Agent Job (configurado com um Escalonamento Temporal);

(ii) Trata qualquer alerta configurado, invocando para isso o pacote “PlugIn” devidamente configurado e que tem definidas as regras de negócio de validação e despoletamento

 

Para além destes pacotes “Core” definem-se também os pacotes do tipo “PlugIn”, onde cada um implementa um conjunto de regras de negócio específicas; neste momento, até à data atual (outros poderão e devem vir a ser implementados), o único pacote “PlugIn” configurado tem o seguinte nome e funcionalidade:

RFC_AlertsDispatcherPlugin_CheckDBandSendEmail – este pacote em termos de regras de negócio e workflow implementa o seguinte:

(i) Executar um script TSQL configurado e registado numa dada tabela de configuração (este TSQL possui as regras de negócio propriamente ditas, é ele que vai determinar se um dado alerta é despoletado ou não);

(ii) Usar o valor retornado (string devidamente formatada em JSON) pela execução desse script TSQL e, se diferente de vazio, passá-lo ao componente RFC que permite enviar email (RFC_SendEmail) => ou seja o despoletamento de alerta!

Os outros componentes assinalados na imagem são reutilizáveis (RFC), nomeadamente:

(i) RFC_SaveCentralLog – permite registar log centralizado (todas as aplicações podem registar num único log mensagens de logging em vez de cada aplicativo ter o seu próprio log.…)

(ii) RFC_SendEmail – permite enviar email (recebe uma string formatada em JSON, devidamente estruturada, que regista o tipo de emails a enviar), 1 ou mais

(iii) Outros componentes do tipo RFC poderão, e devem, ser adicionados!

 

[1b]

Estes componentes SQL Agent Jobs configurados recorrendo a convenções de nomes adequadas (ver secção de instalação/configuração), estão associados à invocação automática (o processo de trigger) do alerta, onde o pacote “core” RFC_AlertsListener será invocado.

Em baixo um exemplo:

 

[2] Servidor SMTP

 

 

 

 

 

Servidor SMTP que permite o envio de emails (ou Relay). Será configurado numa dada tabela de configuração.

 

Instalação e Configuração

 

Base de Dados (Modelo de Dados)

 

 

 

 

Views (Vistas SQL):

[View_RFC_SearchAlertsInformation]

A ser usada pelos aplicativos-cliente para consultar o estado de um alerta em específico.

[View_RFC_CentralLog]

A ser usada pelos aplicativos-cliente para consultar mensagens de log.

Tabelas:

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsSchedulingType]
Define os tipos de escalonamento possíveis: M-Manual (invocação direta), A-Automático (por SQL Agent Job).

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsType]
Define os tipos de alerta possíveis: R-Reutilizável, C-Custom (específico).

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsExecutionState]
Define os tipos de estado possíveis para um alerta.

 

 

CREATE TABLE [dbo].[RFC_Alerts]
Define um alerta propriamente dito.

 

Por ordem:

[RFC_AlertsId] [int] NOT NULL => CHAVE PRIMÁRIA
[AlertUserFriendlyCode] [varchar](50) NOT NULL, => CHAVE DE NEGÓCIO (mais “user-friendly”)
[FlagActive] [bit] NOT NULL, => indica se o alerta está ativo ou não
[Priority] [tinyint] NOT NULL, => prioridade do alerta (para já não é usado)
[Category] [varchar](100) NOT NULL, => categoria do alerta (para agrupamento lógico)
[Title] [varchar](100) NOT NULL, => título do alerta
[Description] [varchar](256) NOT NULL, => descrição completa do alerta
[SchedulingType] [char](1) NOT NULL, => tipo de escalonamento (Manual ou Automático)
[JSONSchedulingSettings] [varchar](512) NULL, => ignorar
[SSISExecutorName] [varchar](256) NOT NULL, =>  nome do pacote “PlugIn” que vai tratar as regras de negócio do alerta
[ExtraParamsForSSISExecutorName] [varchar](8000) NULL, => campo de texto formato livre a ser passado ao pacote “PlugIn”
[BusinessDBConnectionNameForSSISExecutorName] [varchar](50) NULL, => conexão a base de dados para execução de TSQL (pode ser usado para o campo «ExtraParamsForSSISExecutorName»)
[Type] [char](1) NOT NULL, => tipo de alerta : Custom ou Reutilzável
[ChannelDistributionType] [varchar](50) NOT NULL, => Tipo Canal Distribuição (Email ou outros…)
[ExtraParamsChannelDistribution] [varchar](8000) NULL, => campo de texto formato livre complementar ao canal de distribuição
[NextExecutionDate] [datetime] NULL, => variável de controlo: ignorar
[BeginExecutionDate] [datetime] NULL, => variável de controlo: data de inicio de execução
[EndExecutionDate] [datetime] NULL, => variável de controlo:  data de fim de execução
[ExecutionStateCode] [tinyint] NULL, => código de estado do alerta
[ExecutionStateDescription] [varchar](2048) NULL, => descrição complementar ao estado corrente ao alerta
[LogFilterDetailBelowLevel] [int] NOT NULL, => nível de detalhe de log a ser usado para este alerta
[CountSuccessNumberTrigger] [int] NULL, =>  variável de controlo: contagem de “trigger”
[CountSuccessNumberAlertsVerified] [int] NULL, =>  variável de controlo: contagem de despoletamentos

CREATE TABLE [dbo].[RFC_SaveCentralLog]

Tabela de log aplicacional.

CREATE TABLE [dbo].[ConceptDictionary_RFC_AlertsChannelDistributionType]

Tipos de canais de distribuição; para já só EMAIL (meramente informativo).

CREATE TABLE [dbo].[ConceptDictionary_RFC_EmailProfilePriority]

Define a prioridade de um email: urgente, normal, baixa…

CREATE TABLE [dbo].[ConceptDictionary_RFC_SaveCentralLogType]

Tipo de log: Informativo, Aviso ou Erro.

CREATE TABLE [dbo].[RFC_BusinessDBConnectionName]

Conexões de bases de dados de negócio.

CREATE TABLE [dbo].[RFC_ComponentResultExecutionCache]

Gravar e partilha de estados de execução entre componentes.

CREATE TABLE [dbo].[RFC_EmailConnectionProfile]

Perfis de conexão SMTP.

CREATE TABLE [dbo].[RFC_EmailProfile]

Perfis de Email.

CREATE TABLE [knowhow].[RFC_ComponentIndex]

Índice de componentes RFC implementados até ao momento.

Stored Procedures:

CREATE PROCEDURE [dbo].[Usp_RFC_GetComponentResultExecution]

Obtém o resultado de execução de um dado componente (desde que esse componente registe o seu estado de execução…).

CREATE PROCEDURE [dbo].[Usp_RFC_SaveCentralLog]

Permite registar log aplicacional.

CREATE PROCEDURE [dbo].[Usp_RFC_SaveComponentResultExecution]
SSIS Catalog

Grava o resultado de uma execução.

 

Sistema de Ficheiros (temporários)

 

 

 

 

 

 

 

Ficheiros temporários podem ser criados logo é preciso definir uma área de FileSystem onde possam ser gravados este tipo de ficheiros.

Integration Services SSIS Catalog

Em baixo visualiza-se:

1) Pasta no catálogo SSIS onde devem ser instalados os Projectos RFC

2) O nome do Ambiente configurado

3) As Conexões às bases de dados são configuradas através destas Variáveis de Ambiente

 

Serviço de Monitorização de Alertas (Monitor & Health)

 

 

Um exemplo de aplicação

 

Requisitos de Negócio:

(…) Após o carregamento de um ficheiro de agentes na base de dados ODS_XXXX, validar os agentes que não estejam mapeados com a tabela Matriz de produção: caso haja pelo menos 1 agente não mapeados deve ser enviado um email com o formato em baixo para o destinatário adminxxxx@domain.pt com CC para nome_ficticio@domain.pt

Formato email:

Assunto: ALERTAS – lista de utilizadores não mapeados na matriz

Corpo: (html)

[|[param_cumprimento]|],
<br><br>
Anexa-se a este email a lista de agentes que não estão mapeados na matriz actual, especificando:<br><br>
<ul>
<li>Código de Agente</li>
<li>Descrição de Actividade</li>
<li>Mês na Matriz de não-mapeamento</li>
</ul>

<p>
<font color=”blue”><b>Poderá efectuar o upload da Matriz actualizada no seguinte endereço: <a href=”https://domain.pt/PortalS3/”>clique aqui</a></font></b>
<p>
<u>Nota: não responda a este email por favor, será ignorado.</u>

Onde [|[param_cumprimento]|] deve ser substituído em “RunTime” por “Boa noite ou Bom dia ou Boa tarde” dependendo da hora de execução naquele momento.

1 – Configurar o Alerta

 

Como o objetivo primário é reutilizar para satisfazer estes requisitos de negócio podemos usar o componente “PlugIn” descrito acima:

(…)

RFC_AlertsDispatcherPlugin_CheckDBandSendEmail – este pacote em termos de regras de negócio e workflow implementa o seguinte:

(i) Executar um script TSQL configurado (este TSQL possui as regras de negócio propriamente ditas, é ele que vai determinar se um dado alerta é despoletado ou não);

(ii) Usar o valor retornado (string devidamente formatada em JSON) pela execução desse script TSQL e, se diferente de vazio, passá-lo ao componente RFC que permite enviar email (RFC_SendEmail) => despoletamento de alerta!

Sendo assim, basta configurar o script TSQL que valida estas mesmas regras de negócio.

Como? Exemplo possível:

DECLARE @val Varchar(4000);
DECLARE @cumprimento Varchar(50);
DECLARE @Total int
DECLARE @SQLString NVARCHAR(4000)
DECLARE @LogStep varchar(50)

BEGIN TRY
IF EXISTS
(
              SELECT [CodigoAgente],[DescricaoActividade],[DataFicheiro] FROM [dbo].[View_RFC_AgentesNaoMapeadosMatriz]
)
— alarm on !
BEGIN
DECLARE
@OutputFile NVARCHAR(255),
@FilePath NVARCHAR(255),
@FormatFile NVARCHAR(255),
@bcpCommand NVARCHAR(4000)

DECLARE @FlagError bit
DECLARE @ErrorDesc nvarchar(1024)

DECLARE @BcpOutput table(line varchar(256))

— bcp params: export query data to file

SET @SQLString = N’SELECT [CodigoAgente],[DescricaoActividade],[DataFicheiro] FROM [dbo].[View_RFC_AgentesNaoMapeadosMatriz]’
SET @bcpCommand = ‘bcp “‘ + @SQLString + ‘” queryout ‘
SET @FilePath = ‘C:\temp\RFC_AlertSystem\’
SET @OutputFile = ‘RFC_Alerts_’ + cast(NEWID() as varchar(36)) + ‘.csv’
SET @FormatFile = ‘-c -t; -T’
SET @bcpCommand = @bcpCommand + ‘”‘ + @FilePath + @OutputFile + ‘” ‘ + @FormatFile + ‘ -S’+ @@servername

declare @retValue int

set @LogStep = ’02-exporting data to file’

insert into @BcpOutput — get bcp execution detail
exec @retValue = master..xp_cmdshell @bcpCommand

IF (@retValue <>0)
— bcp error !
select top 1 @FlagError = 1, @ErrorDesc = line from @BcpOutput where line like ‘Error = %’
ELSE
— all ok, yes!
select @FlagError = 0, @ErrorDesc = ”

IF (@FlagError <>0)
RAISERROR (@ErrorDesc, 16, 1) — raise error
ELSE
BEGIN
— set JSON fields to “SendEmail” component

set @LogStep = ’03-setting JSON return info’

— set RFC “param_cumprimento”
IF (DATEPART(HOUR, GETDATE()) between 18 and 0)
SET @cumprimento=’Boa noite’
ELSE
IF (DATEPART(HOUR, GETDATE()) > 12 and DATEPART(HOUR, GETDATE()) < 18)
SET @cumprimento=’Boa tarde’
ELSE
SET @cumprimento=’Bom dia’

set @val =
‘[
{
“RTP”:[
{
“name”:”param_cumprimento”,
“value”:”‘ + @cumprimento + ‘”
},
{
“name”:”param_ficheiro_caminho”,
“value”:”‘ + replace(@FilePath + @OutputFile,’\’,’\\’) + ‘”
}
],
“PerfilEmail_id”:1
}
]

select @val — alarm!
END
END
ELSE
select ” — no alarm: empty string
END TRY
BEGIN CATCH
— control errors to create custome errors

DECLARE @fullmessage varchar(1024)

set @fullmessage = @LogStep + ‘ : ‘ + ERROR_MESSAGE()

RAISERROR (@fullmessage, 16, 1) — error
END CATCH

 

NOTAS IMPORTANTES:

  • A vermelho pode-se destacar quando o alerta deve ser gerado e como tal é retornado um JSON devidamente estruturado com o email a ser enviado a ser enviado!

Destaca-se nesse JSON os parâmetros assinalados a negrito que serão usados na definição do email dinamicamente; destaca-se também o perfil de email a ser usado dado por «Perfilemail_id : 1», neste caso o perfil com id=1 configurado na tabela como veremos mais abaixo…

  • A verde destaca-se a não existência de alarme logo retorna-se string vazia ” “

 

2 – Configurar o Perfil de Email

 

 

3 – Configurar Ativação (“Trigger”) se modo for :

 

 

a) AUTOMÁTICO:

Scheduling Type=’A’

É preciso agora criar o SQL Agent Job que irá funcionar como “trigger” e definir um Escalonamento Temporal (exemplo: executar diariamente, sempre às 14h)

Ver acima neste artigo a secção de criação do Job respetivo.

 

b) MANUAL (ONDEMAND):

Scheduling Type=’M’

Aqui será invocado diretamente através SQL Server Integration Services, com a passagem de parâmetros adequada.

Exemplo:

 

Via Stored Procedure:

DECLARE @return_value int,
@return_state_code int,
@return_state_desc varchar(1024)

EXEC @return_value = [dbo].[Usp_RFC_TriggerAlertManualy]
@param_alert_id = ?,
@param_LoggroupId = ?,
@return_state_code = @return_state_code OUTPUT,
@return_state_desc = @return_state_desc OUTPUT

SELECT @return_state_code as N’@return_state_code’,
@return_state_desc as N’@return_state_desc’

Obviamente, muitos detalhes foram filtrados neste artigo, mas se estiver interessado em entender melhor esse assunto, entre em contato comigo.

Obrigado!

 

 
    Pedro Mesquita         
   Senior Consultant

 

Blog