[SEI][SPIKE] Análise da estrutura de Auditoria de Ações dos Usuários na Aplicação
CENÁRIO ATUAL
O Sistema Eletrônico de Informação é parte do projeto Governo Sem Papel, mantido pelo Estado de Rondônia, sendo utilizado para a geração e tramitação de processos entre secretarias. Economizando milhões em papel e impressão e acelerando o fluxo dos trâmites.
PROBLEMA
Com a ampla adoção pelas secretarias uma grande quantidade diária de dados é gerada. A versão utilizada possuí problemas conhecidos de performance e otimização e não pode ser atualizada devido a entraves burocráticos. O crescimento da utilização de recursos, principalmente de disco, gera dificuldades para a manutenção de logs, rotinas de backup, lentidão na aplicação e até mesmo alto custo para armazenamento.
OBJETIVO
Considerando a dificuldade da manutenção e sustentabilidade dos logs de auditoria gerados pela aplicação, este estudo tem como objetivo principal demonstrar como tornar sustentável o processo de auditoria no SEI, atendendo a estória de usuário registrada sob link https://app.pipefy.com/open-cards/458858849 .
RESULTADOS ESPERADOS
Para que seja possível atender ao objetivo principal, com este estudo espera-se:
R1) Identificar como estão organizados os registros de ações de usuário da aplicação;
R2) Propor abordagens que visam uma melhor organização destes registros;
R3) Fornecer subsídios para a continuidade do processo de manutenção dos registros de auditoria;
GLOSSÁRIO
A seguir, são descritos termos relacionados a este estudo e como eles podem ser definidos no escopo do mesmo, sendo desconsideradas definições não relacionadas.
- Log - registro de eventos de uma aplicação;
- Evento - considera-se evento toda a ação do usuário que demanda de um recurso do sistema;
- Recurso - uma funcionalidade da aplicação, como criar, editar, excluir, visualizar, copiar, mover, assinar, anexar, ... , algum dos objetos do sistema;
- Objeto - um item no qual o usuário pode executar uma ação a partir dos recursos fornecidos pela aplicação, como documentos, processos, blocos, etc;
- Transação - conjunto de instruções/operações que descrevem uma sequência que deve ser executada com sucesso em sua totalidade afim de completar uma tarefa lógica;
- Auditoria - processo de identificação de um conjunto de recursos utilizados por um usuário OU de um conjunto de usuários que demandaram recursos em determinado objeto;
- Expurgo - remover as informações mais antigas com o objetivo de obter melhor performance na aplicação;
PREMISSAS
P1) Com os entraves burocráticos, hoje não é possível realizar correção via software, sendo necessárias a utilização de rotinas de banco;
P2) Os dados de ações de usuário são inseridos na tabela [dbo].[infra_auditoria], no database da aplicação SEI;
P3) Devido ao volume gigantesco de ações diárias realizadas ao se levar em conta a operacionalização por todos os utilizadores, esta tabela excede os limites do sistema gerenciador de banco de dados, fazendo com que a aplicação caia caso não sejam realizadas as remoções periódicas dos registros;
P4) Por se tratarem de dados de auditoria, utilizados por órgãos fiscalizadores, estes dados não podem ser excluídos, sendo realizado o expurgo dos dados.
CONDUÇÃO DO ESTUDO
Durante a condução do estudo, alguns desafios foram identificados, sendo necessária a exploração e o entendimento de algumas estruturas auxiliares. Neste caso, considera-se como exploração apenas a análise das estruturas que atendem aos fins propostos pelo estudo. Os desafios identificados e conceitos de entendimento necessário, neste estudo, são considerados Pré-Exploratórios e os resultados da exploração são definidos como execução.
ANÁLISE PRÉ-EXPLORATÓRIA
A seguir são descritos os processos e informações obtidas necessárias para a efetivação deste estudo.
IDENTIFICAÇÃO DA ORGANIZAÇÃO DOS REGISTROS DE AUDITORIA
Como primeira etapa deste estudo, torna-se necessário o aprofundamento do conteúdo presente na tabela dbo.infra_auditoria. A consulta a seguir permite descrever a organização da tabela em questão.
SELECT
ORDINAL_POSITION as num,
CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA,'.',TABLE_NAME) as 'schema',
COLUMN_NAME as nome,
CONCAT(DATA_TYPE,' ', NULLIF(CHARACTER_MAXIMUM_LENGTH,-1)) as tipo
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like 'infra_auditoria';Como resultado desta consulta temos as seguintes informações:
|
num |
schema |
nome |
tipo |
|
1 |
sei.dbo.infra_auditoria |
id_infra_auditoria |
bigint |
|
2 |
sei.dbo.infra_auditoria |
recurso |
varchar 50 |
|
3 |
sei.dbo.infra_auditoria |
dth_acesso |
datetime |
|
4 |
sei.dbo.infra_auditoria |
ip |
varchar 39 |
|
5 |
sei.dbo.infra_auditoria |
id_usuario |
int |
|
6 |
sei.dbo.infra_auditoria |
sigla_usuario |
varchar 100 |
|
7 |
sei.dbo.infra_auditoria |
nome_usuario |
varchar 100 |
|
8 |
sei.dbo.infra_auditoria |
id_orgao_usuario |
int |
|
9 |
sei.dbo.infra_auditoria |
sigla_orgao_usuario |
varchar 30 |
|
10 |
sei.dbo.infra_auditoria |
id_usuario_emulador |
int |
|
11 |
sei.dbo.infra_auditoria |
sigla_usuario_emulador |
varchar 100 |
|
12 |
sei.dbo.infra_auditoria |
nome_usuario_emulador |
varchar 100 |
|
13 |
sei.dbo.infra_auditoria |
id_orgao_usuario_emulador |
int |
|
14 |
sei.dbo.infra_auditoria |
sigla_orgao_usuario_emulador |
varchar 30 |
|
15 |
sei.dbo.infra_auditoria |
id_unidade |
int |
|
16 |
sei.dbo.infra_auditoria |
sigla_unidade |
varchar 30 |
|
17 |
sei.dbo.infra_auditoria |
descricao_unidade |
varchar 250 |
|
18 |
sei.dbo.infra_auditoria |
id_orgao_unidade |
int |
|
19 |
sei.dbo.infra_auditoria |
sigla_orgao_unidade |
varchar 30 |
|
20 |
sei.dbo.infra_auditoria |
servidor |
varchar 250 |
|
21 |
sei.dbo.infra_auditoria |
user_agent |
varchar |
|
22 |
sei.dbo.infra_auditoria |
requisicao |
varchar |
|
23 |
sei.dbo.infra_auditoria |
operacao |
varchar |
Destacam-se em negrito as colunas mais utilizadas nos processos de auditoria, sendo descritas a seguir:
- id_infra_auditoria - identificador incremental utilizado como chave principal para localizar registros de ações de utilizadores no SEI;
- recurso - trata-se de um classificador que descreve qual ação foi realizada pelo usuário, conforme descrito no glossário;
- dth_acesso - data e hora da transação;
- requisicao - este campo armazena os detalhes da transação que descrevem a ação do usuário na aplicação. Associado ao campo recurso, é possível identificar em qual documento/processo/bloco foi realizada uma ação.
IDENTIFICAÇÃO DAS ROTINAS DE EXPURGO
Foi identificada a rotina que realiza a remoção periódica dos registros da tabela infra_auditoria, sendo os comandos demonstrados e explicados a seguir:
ALTER procedure [dbo].[expurgo_infra_auditoria] as
declare @maxid int
declare @minid int
begin;
set @maxid = (select max(id_infra_auditoria) from sei.dbo.infra_auditoria)
set @minid = (select min(id_infra_auditoria) from sei.dbo.infra_auditoria)
insert into [aud_infra].[dbo].[aud_expurgo]
select * from sei.dbo.infra_auditoria
where id_infra_auditoria < @maxid;
delete from sei.dbo.infra_auditoria
where id_infra_auditoria < @maxid;
END;Por se tratar de uma tabela com dados transacionais, as linhas 8 e 9, asseguram de que o expurgo será realizado no intervalo determinado, sem que ocorra a perda de informações durante o processo. Nas linhas 11 a 17, é possível analisar que a tarefa de expurgo consiste basicamente em três etapas:
- [L8] Selecionar o identificador de transação de usuário (id_infra_auditoria) mais recente (MAX);
- [L11~L13] Inserir todos os registros de transação até o identificador selecionado na tabela de expurgo;
- [L16~L17] Excluir os registros de transação até o identificador selecionado em 1.
É possível observar que esta rotina está sendo executada diariamente, sem histórico recente de qualquer tipo de alertas ou falhas. É interessante destacar que esta solução sana a premissa P3 e parcialmente a premissa P4, pois este volume de dados não mais impactará na aplicação, porém, a tabela [aud_infra].[dbo].[aud_expurgo] está setada para receber todos os dados de transações, sendo possível que a mesma sofra um crescimento considerável, tornando-a insustentável.
De posse dessas informações, o próximo desafio é identificar se existe uma rotina para que a tabela que receba os dados de expurgo não apresente os mesmos problemas da tabela infra_auditoria, da aplicação.
IDENTIFICAÇÃO DAS ROTINAS DE CONSOLIDAÇÃO DOS DADOS EXPURGADOS
Após analise, não foram identificadas rotinas definidas para consolidação dos dados expurgados, sendo este o primeiro encaminhamento deste estudos.
ANÁLISE EXPLORATÓRIA
A seguir são descritos os processos e demonstrados os resultados da análise exploratória deste estudo, sendo destacadas as descobertas e apontamentos.
IDENTIFICAÇÃO DAS TABELAS QUE POSSUEM LOGS DE AUDITORIA
A seguir são apresentados os nomes das tabelas, menores e maiores id_infra_auditoria e dth_acesso , além da contagem do número de linhas e tamanho em Mb dos recursos de armazenamento utilizados.
|
# |
TABLENAME |
MIN_ID |
MIN_DATE |
MAX_ID |
MAX_DATE |
REGISTROS |
|
1 |
audbkp_2 |
NULL |
NULL |
NULL |
NULL |
0 |
|
2 |
aud_expurgo_temp |
NULL |
NULL |
NULL |
NULL |
0 |
|
3 |
audbkp_1 |
1 |
2017-02-01 16:58:12.000 |
72072674 |
2019-01-22 12:47:52.000 |
71470183 |
|
4 |
FALTANTE |
-26564546 |
||||
|
5 |
audbkp_3 |
98637221 |
2019-05-09 12:37:38.000 |
100241685 |
2019-05-15 23:44:12.000 |
1595704 |
|
6 |
FALTANTE |
-3 |
||||
|
7 |
audbkp_4 |
100241689 |
2019-05-15 23:46:13.000 |
103260808 |
2019-05-28 10:55:36.000 |
3003604 |
|
8 |
aud24082019 |
103260809 |
2019-05-28 10:59:49.000 |
127682818 |
2019-08-24 00:59:34.000 |
24268639 |
|
9 |
aud_expurgo_5 |
127682819 |
2019-08-25 12:19:54.000 |
170524666 |
2020-01-25 02:02:24.000 |
42560678 |
|
10 |
aud_expurgo_6 |
170524667 |
2020-01-25 02:02:34.000 |
180281829 |
2020-03-03 01:17:15.000 |
9681663 |
|
11 |
aud_expurgo_7 |
180281830 |
2020-03-03 01:16:13.000 |
200231366 |
2020-05-13 01:15:50.000 |
19815502 |
|
12 |
aud_expurgo_8 |
200231367 |
2020-05-13 01:15:31.000 |
212622097 |
2020-06-29 01:15:24.000 |
12310593 |
|
13 |
aud_expurgo_12 |
212622098 |
2020-06-29 01:15:06.000 |
224473434 |
2020-08-07 01:15:06.000 |
11779194 |
|
14 |
aud_expurgo_9 |
224473435 |
2020-08-07 01:14:11.000 |
244825439 |
2020-10-12 21:08:43.000 |
34786771 |
|
15 |
aud_expurgo_10 |
244825440 |
2020-10-12 21:08:53.000 |
246620480 |
2020-10-17 11:07:18.000 |
1784529 |
|
16 |
aud_expurgo_11 |
246620481 |
2020-10-17 11:07:09.000 |
261836927 |
2020-12-03 09:45:08.000 |
15087784 |
|
17 |
aud_expurgo_aux_03-12-2020_09-12-2020 |
261836928 |
2020-12-03 09:44:58.000 |
263708273 |
2020-12-09 00:00:59.000 |
1827692 |
|
18 |
aud_expurgo |
263708274 |
2020-12-09 00:00:49.000 |
277586362 |
2021-01-29 00:00:46.000 |
13801364 |
|
19 |
aud_expurgo_aux_29-01-2021_29-01-2021 |
277586363 |
2021-01-29 00:00:04.000 |
277586363 |
2021-01-29 00:00:04.000 |
1 |
|
20 |
aud_29-01-2021_x_26-04-2021 |
277586364 |
2021-01-29 00:00:08.000 |
306716512 |
2021-04-26 12:28:12.000 |
28962560 |
|
21 |
FALTANTE |
-10023 |
||||
|
22 |
aud_expurgo_26-04-2021_28-10-2021 |
306726536 |
2021-04-26 13:19:27.000 |
378764156 |
2021-10-28 22:00:17.000 |
71621948 |
|
23 |
prod.aud_expurgo |
378764157 |
2021-10-28 21:59:59.000 |
379403627 |
2021-11-01 22:00:28.000 |
636165 |
É possível notar que os dados armazenados são compreendidos em intervalos e quantidades bem distintas, não existindo nenhum tipo de padrão de organização identificável. A seguir são apresentados os dados graficamente. Sendo considerados os menores e maiores id_infra_auditoria e a contagem do número de linhas. As diferenças entre as quantidades de registros (em laranja) e tamanho das tabelas (em cinza) são facilmente identificadas.
Na coluna 4, são destacados um conjunto de cerca de 27 milhões de registros faltantes. Estes não foram encontrado em nenhuma tabela e compreendem o período entre 22-01-2019 a 09-05-2019.
Ao apresentar as análises de como estão organizados os logs de auditoria de usuários, satisfaz-se o resultado esperado R1, sendo demonstrado a seguir a proposta de melhoria que atenda ao resultado esperado R2.
PROPOSTA DE MELHORIA
A seguir, é apresentada uma proposta de reorganização dos dados baseado no conceito de big data de recuperação de informação baseado na divisão de tabelas por período, conhecido como fragmentação.
FRAGMENTAÇÃO HORIZONTAL
A seguir, são apresentados os dados da proposta de fragmentação dos dados por mês, de acordo com a tabela, ano, mês ID, período e quantidade de registros.
|
# |
TABLENAME |
ANO |
MÊS |
MIN_ID |
MIN_DATE |
MAX_ID |
MAX_DATE |
QTD |
|
1 |
audbkp_1 |
2017 |
2 |
1 |
2017-02-01 16:58:12.000 |
310 |
2017-02-21 16:44:29.000 |
258 |
|
2 |
audbkp_1 |
2017 |
3 |
311 |
2017-03-06 13:25:40.000 |
9738 |
2017-03-31 20:20:28.000 |
9053 |
|
3 |
audbkp_1 |
2017 |
4 |
9739 |
2017-04-02 17:49:24.000 |
40893 |
2017-04-30 19:38:47.000 |
30540 |
|
4 |
audbkp_1 |
2017 |
5 |
40894 |
2017-05-01 10:24:22.000 |
85932 |
2017-05-31 23:00:11.000 |
44633 |
|
5 |
audbkp_1 |
2017 |
6 |
85933 |
2017-06-01 00:53:07.000 |
185283 |
2017-06-30 22:27:02.000 |
98321 |
|
6 |
audbkp_1 |
2017 |
7 |
185284 |
2017-07-01 08:07:22.000 |
451498 |
2017-07-31 22:00:30.000 |
263601 |
|
7 |
audbkp_1 |
2017 |
8 |
451499 |
2017-08-01 00:00:52.000 |
901671 |
2017-08-31 23:58:41.000 |
445962 |
|
8 |
audbkp_1 |
2017 |
9 |
901672 |
2017-09-01 00:02:40.000 |
1526414 |
2017-09-30 23:34:33.000 |
614789 |
|
9 |
audbkp_1 |
2017 |
10 |
1526415 |
2017-10-01 00:34:37.000 |
3013667 |
2017-10-31 23:59:58.000 |
1470900 |
|
10 |
audbkp_1 |
2017 |
11 |
3013668 |
2017-11-01 00:00:35.000 |
5297777 |
2017-11-30 23:59:10.000 |
2263988 |
|
11 |
audbkp_1 |
2017 |
12 |
5297778 |
2017-12-01 00:00:16.000 |
7791701 |
2017-12-31 23:59:34.000 |
2476198 |
|
12 |
audbkp_1 |
2018 |
1 |
7791702 |
2018-01-01 00:00:04.000 |
10418830 |
2018-01-31 23:59:56.000 |
2598649 |
|
13 |
audbkp_1 |
2018 |
2 |
10418831 |
2018-02-01 00:00:01.000 |
13591878 |
2018-02-28 23:59:58.000 |
3152946 |
|
14 |
audbkp_1 |
2018 |
3 |
13591879 |
2018-03-01 00:00:00.000 |
17879785 |
2018-03-31 23:59:17.000 |
4263677 |
|
15 |
audbkp_1 |
2018 |
4 |
17879786 |
2018-04-01 00:00:01.000 |
22752948 |
2018-04-30 23:59:55.000 |
4845369 |
|
16 |
audbkp_1 |
2018 |
5 |
22752949 |
2018-05-01 00:00:07.000 |
28166436 |
2018-05-31 23:59:56.000 |
5383811 |
|
17 |
audbkp_1 |
2018 |
6 |
28166437 |
2018-06-01 00:00:00.000 |
33142884 |
2018-06-30 23:59:56.000 |
4918840 |
|
18 |
audbkp_1 |
2018 |
7 |
33142885 |
2018-07-01 00:00:02.000 |
38747192 |
2018-07-31 23:59:56.000 |
5520638 |
|
19 |
audbkp_1 |
2018 |
8 |
38747193 |
2018-08-01 00:00:03.000 |
44973041 |
2018-08-31 23:59:49.000 |
6192222 |
|
20 |
audbkp_1 |
2018 |
9 |
44973042 |
2018-09-01 00:00:14.000 |
50465382 |
2018-09-30 23:59:53.000 |
5460475 |
|
21 |
audbkp_1 |
2018 |
10 |
50465383 |
2018-10-01 00:00:00.000 |
56718179 |
2018-10-31 23:59:58.000 |
6216373 |
|
22 |
audbkp_1 |
2018 |
11 |
56718180 |
2018-11-01 00:00:02.000 |
62698428 |
2018-11-30 23:59:55.000 |
5915910 |
|
23 |
audbkp_1 |
2018 |
12 |
62698429 |
2018-12-01 00:00:01.000 |
68385485 |
2018-12-31 23:59:37.000 |
5630483 |
|
24 |
audbkp_1 |
2019 |
1 |
68385486 |
2019-01-01 00:00:09.000 |
72072674 |
2019-01-22 12:47:52.000 |
3652547 |
|
25 |
audbkp_3 |
2019 |
5 |
98637221 |
2019-05-09 12:37:38.000 |
100241685 |
2019-05-15 23:44:12.000 |
1595704 |
|
26 |
audbkp_4 |
2019 |
5 |
100241689 |
2019-05-15 23:46:13.000 |
103260808 |
2019-05-28 10:55:36.000 |
3003604 |
|
27 |
aud24082019 |
2019 |
5 |
103260809 |
2019-05-28 10:59:49.000 |
104302655 |
2019-05-31 23:59:59.000 |
1036059 |
|
28 |
aud24082019 |
2019 |
6 |
104302656 |
2019-06-01 00:00:04.000 |
111364586 |
2019-06-30 23:59:52.000 |
7002694 |
|
29 |
aud24082019 |
2019 |
7 |
111364587 |
2019-07-01 00:00:06.000 |
120542249 |
2019-07-31 23:59:58.000 |
9125265 |
|
30 |
aud24082019 |
2019 |
8 |
120542233 |
2019-08-01 00:00:01.000 |
127682818 |
2019-08-24 00:59:34.000 |
7104621 |
|
31 |
aud_expurgo_5 |
2019 |
8 |
127682819 |
2019-08-25 12:19:54.000 |
129789315 |
2019-08-31 23:59:35.000 |
2085627 |
|
32 |
aud_expurgo_5 |
2019 |
9 |
129789316 |
2019-09-01 00:00:02.000 |
139203833 |
2019-09-30 23:59:50.000 |
9357333 |
|
33 |
aud_expurgo_5 |
2019 |
10 |
139203834 |
2019-10-01 00:00:04.000 |
148692982 |
2019-10-31 23:59:58.000 |
9420366 |
|
34 |
aud_expurgo_5 |
2019 |
11 |
148692952 |
2019-11-01 00:00:00.000 |
157583756 |
2019-11-30 23:59:29.000 |
8842115 |
|
35 |
aud_expurgo_5 |
2019 |
12 |
157583755 |
2019-12-01 00:00:01.000 |
164913245 |
2019-12-31 23:59:38.000 |
7288516 |
|
36 |
aud_expurgo_5 |
2020 |
1 |
164913246 |
2020-01-01 00:00:12.000 |
170524666 |
2020-01-25 02:02:24.000 |
5566721 |
|
37 |
aud_expurgo_6 |
2020 |
1 |
170524667 |
2020-01-25 02:02:34.000 |
172412259 |
2020-01-31 23:59:54.000 |
1876841 |
|
38 |
aud_expurgo_6 |
2020 |
2 |
172412237 |
2020-02-01 00:00:02.000 |
179853413 |
2020-02-29 23:59:48.000 |
7392258 |
|
39 |
aud_expurgo_6 |
2020 |
3 |
179853414 |
2020-03-01 00:00:13.000 |
180281829 |
2020-03-03 01:17:15.000 |
412564 |
|
40 |
aud_expurgo_7 |
2020 |
3 |
180281830 |
2020-03-03 01:16:13.000 |
189452876 |
2020-03-31 23:59:57.000 |
9105131 |
|
41 |
aud_expurgo_7 |
2020 |
4 |
189452872 |
2020-04-01 00:00:01.000 |
197296861 |
2020-04-30 23:59:57.000 |
7794914 |
|
42 |
aud_expurgo_7 |
2020 |
5 |
197296842 |
2020-05-01 00:00:01.000 |
200231366 |
2020-05-13 01:15:50.000 |
2915457 |
|
43 |
aud_expurgo_8 |
2020 |
5 |
200231367 |
2020-05-13 01:15:31.000 |
205183714 |
2020-05-31 23:59:59.000 |
4921176 |
|
44 |
aud_expurgo_8 |
2020 |
6 |
205183684 |
2020-06-01 00:00:01.000 |
212622097 |
2020-06-29 01:15:24.000 |
7389417 |
|
45 |
aud_expurgo_12 |
2020 |
6 |
212622098 |
2020-06-29 01:15:06.000 |
213468808 |
2020-06-30 23:59:57.000 |
841281 |
|
46 |
aud_expurgo_12 |
2020 |
7 |
213468788 |
2020-07-01 00:00:00.000 |
222584934 |
2020-07-31 23:59:59.000 |
9060922 |
|
47 |
aud_expurgo_12 |
2020 |
8 |
222584873 |
2020-08-01 00:00:00.000 |
224473434 |
2020-08-07 01:15:06.000 |
1876991 |
|
48 |
aud_expurgo_9 |
2020 |
8 |
224473435 |
2020-08-07 01:14:11.000 |
231974842 |
2020-08-31 23:59:59.000 |
14924284 |
|
49 |
aud_expurgo_9 |
2020 |
9 |
231974773 |
2020-09-01 00:00:00.000 |
241562865 |
2020-09-30 23:59:58.000 |
16621199 |
|
50 |
aud_expurgo_9 |
2020 |
10 |
241562851 |
2020-10-01 00:00:00.000 |
244825439 |
2020-10-12 21:08:43.000 |
3241288 |
|
51 |
aud_expurgo_10 |
2020 |
10 |
244825440 |
2020-10-12 21:08:53.000 |
246620480 |
2020-10-17 11:07:18.000 |
1784529 |
|
52 |
aud_expurgo_11 |
2020 |
10 |
246620481 |
2020-10-17 11:07:09.000 |
251051973 |
2020-10-31 23:59:59.000 |
4396666 |
|
53 |
aud_expurgo_11 |
2020 |
11 |
251051972 |
2020-11-01 00:00:00.000 |
260741415 |
2020-11-30 23:59:58.000 |
9601988 |
|
54 |
aud_expurgo_11 |
2020 |
12 |
260741416 |
2020-12-01 00:00:00.000 |
261836927 |
2020-12-03 09:45:08.000 |
1089130 |
|
55 |
aud_expurgo_aux_03-12-2020_09-12-2020 |
2020 |
12 |
261836928 |
2020-12-03 09:44:58.000 |
263708273 |
2020-12-09 00:00:59.000 |
1827692 |
|
56 |
aud_expurgo |
2020 |
12 |
263708274 |
2020-12-09 00:00:49.000 |
269792455 |
2020-12-31 23:59:56.000 |
6048921 |
|
57 |
aud_expurgo |
2021 |
1 |
269792456 |
2021-01-01 00:00:09.000 |
277586362 |
2021-01-29 00:00:46.000 |
7752443 |
|
58 |
aud_expurgo_aux_29-01-2021_29-01-2021 |
2021 |
1 |
277586363 |
2021-01-29 00:00:04.000 |
277586363 |
2021-01-29 00:00:04.000 |
1 |
|
59 |
aud_29-01-2021_x_26-04-2021 |
2021 |
1 |
277586364 |
2021-01-29 00:00:08.000 |
278000402 |
2021-01-31 23:59:59.000 |
411545 |
|
60 |
aud_29-01-2021_x_26-04-2021 |
2021 |
2 |
278000379 |
2021-02-01 00:00:02.000 |
287472797 |
2021-02-28 23:59:59.000 |
9412926 |
|
61 |
aud_29-01-2021_x_26-04-2021 |
2021 |
3 |
287472792 |
2021-03-01 00:00:00.000 |
298645677 |
2021-03-31 23:59:57.000 |
11112728 |
|
62 |
aud_29-01-2021_x_26-04-2021 |
2021 |
4 |
298645646 |
2021-04-01 00:00:00.000 |
306716512 |
2021-04-26 12:28:12.000 |
8025361 |
|
63 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
4 |
306726536 |
2021-04-26 13:19:27.000 |
308966052 |
2021-04-30 23:59:57.000 |
2228131 |
|
64 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
5 |
308966044 |
2021-05-01 00:00:00.000 |
319705205 |
2021-05-31 23:59:59.000 |
10684150 |
|
65 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
6 |
319705178 |
2021-06-01 00:00:00.000 |
331117433 |
2021-06-30 23:59:58.000 |
11343041 |
|
66 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
7 |
331117405 |
2021-07-01 00:00:00.000 |
342936141 |
2021-07-31 23:59:59.000 |
11756660 |
|
67 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
8 |
342936140 |
2021-08-01 00:00:00.000 |
355256047 |
2021-08-31 23:59:59.000 |
12242316 |
|
68 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
9 |
355256003 |
2021-09-01 00:00:00.000 |
367001294 |
2021-09-30 23:59:58.000 |
11664789 |
|
69 |
aud_expurgo_26-04-2021_28-10-2021 |
2021 |
10 |
367001295 |
2021-10-01 00:00:00.000 |
378764156 |
2021-10-28 22:00:17.000 |
11702861 |
|
70 |
prod.aud_expurgo |
2021 |
10 |
378764157 |
2021-10-28 21:59:59.000 |
379350703 |
2021-10-31 23:59:49.000 |
583551 |
|
71 |
prod.aud_expurgo |
2021 |
11 |
379350701 |
2021-11-01 00:00:02.000 |
379445775 |
2021-11-02 22:00:29.000 |
94565 |
Como representação dos resultados da tabela, temos o seguinte gráfico.
É possível notar um crescimento no número de registros conforme se intensificam a adoção de utilização da aplicação pelos órgãos e secretarias. Embora sejam identificados dois outliers em 48 e 49, a média dos registros por mês está entre 100 e 120 milhões de registros, um valor considerado aceitável para o processamento de consultas e realização de auditorias. Sendo assim, a solução apresentada, atende ao resultado esperado R2.
PLANO DE AÇÃO
Com o objetivo de atender o resultado esperado R3, a seguir são descritas as propostas de ação imediata e para estudos futuros.
PROPOSTA IMEDIATA
Como proposta de execução imediata, após avaliação dos resultados demonstrados acima, a equipe definiu pela organização dos dados a partir fragmentação horizontal mensal dos dados de auditoria. Além disso, é interessante a criação imediata de uma rotina para que as tabelas possam ser criadas e os dados carregados conforme o ano e mês, seguindo o modelo de script apresentado a seguir:
use infra_expurgo;
declare @sqlSelecionaMesAno nvarchar(max);
declare @sqlTabelaExiste nvarchar(max);
declare @sqlTabelaNova nvarchar(max);
declare @tabelaEntrada sysname;
set @tabelaEntrada = N'[IP_PRODUCAO].[aud_infra].[dbo].[infra_expurgo]';
DROP TABLE IF EXISTS #ano_mes;
create table #ano_mes(
ano int,
mes int,
nomeTabelaSaida varchar(40)
);
set @sqlSelecionaMesAno = '
select distinct
year(dth_acesso) as ano,
month(dth_acesso) AS mes,
'''' as nomeTabelaSaida
from '+@tabelaEntrada+'';
INSERT INTO #ANO_MES EXECUTE sp_executesql @sqlSelecionaMesAno;
update #ano_mes
set nomeTabelaSaida = CONCAT('aud_',ano,'-',RIGHT( '0'+ Convert(varchar, mes), 2));
select * from #ano_mes
order by nomeTabelaSaida;
declare ano_mes_cursor CURSOR FOR
SELECT * from #ano_mes
order by nomeTabelaSaida;
declare @cur_ano int, @cur_mes int, @cur_tabelaSaida varchar(40);
OPEN ano_mes_cursor;
FETCH NEXT FROM ano_mes_cursor
into @cur_ano, @cur_mes, @cur_tabelaSaida;
SELECT @sqlTabelaNova;
SELECT @sqlTabelaExiste;
WHILE @@FETCH_STATUS = 0
BEGIN
IF OBJECT_ID (@cur_tabelaSaida, N'U') IS NOT NULL
BEGIN
SET @sqlTabelaExiste = N'
INSERT INTO ['+@cur_tabelaSaida+']
(id_infra_auditoria,recurso,dth_acesso,ip,id_usuario,sigla_usuario,nome_usuario,
id_orgao_usuario,sigla_orgao_usuario,id_usuario_emulador,sigla_usuario_emulador,
nome_usuario_emulador,id_orgao_usuario_emulador,sigla_orgao_usuario_emulador,
id_unidade,sigla_unidade,descricao_unidade,id_orgao_unidade,sigla_orgao_unidade,
servidor,user_agent,requisicao,operacao)
SELECT id_infra_auditoria,recurso,dth_acesso,ip,id_usuario,sigla_usuario,nome_usuario,
id_orgao_usuario,sigla_orgao_usuario,id_usuario_emulador,sigla_usuario_emulador,
nome_usuario_emulador,id_orgao_usuario_emulador,sigla_orgao_usuario_emulador,
id_unidade,sigla_unidade,descricao_unidade,id_orgao_unidade,sigla_orgao_unidade,
servidor,user_agent,requisicao,operacao
FROM '+@tabelaEntrada+'
WHERE
YEAR(dth_acesso) ='+CONVERT(varchar(4), @cur_ano)+'
AND MONTH(dth_acesso) = '+CONVERT(varchar(2), @cur_mes)+'
';
EXECUTE sp_executesql @sqlTabelaExiste;
END
ELSE
BEGIN
SET @sqlTabelaNova = N'
SELECT * INTO ['+@cur_tabelaSaida+']
FROM '+@tabelaEntrada+'
WHERE
YEAR(dth_acesso) ='+CONVERT(varchar(4), @cur_ano)+'
AND MONTH(dth_acesso) = '+CONVERT(varchar(2), @cur_mes);
EXECUTE sp_executesql @sqlTabelaNova;
END
FETCH NEXT FROM ano_mes_cursor
into @cur_ano, @cur_mes, @cur_tabelaSaida;
END
CLOSE ano_mes_cursor;
DEALLOCATE ano_mes_cursor; PROPOSTAESTUDOS FUTURAFUTUROS
Com o intuito de aumentar a sustentabilidade dos relatórios, a equipe identificou a necessidade de realizar estudos em tecnologias como ELASTICSEARCH e SPARK, capazes de otimizar a entrega dos resultados.
Também é interessante a definição de uma abordagem para organizar os valores presentes no campo requisicao.