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