[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:

1. [L8] Selecionar o identificador de transação de usuário (id_infra_auditoria) mais recente (MAX);
2. [L11~L13] Inserir todos os registros de transação até o identificador selecionado na tabela de expurgo;
3. [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 10 e 12 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; 

ESTUDOS FUTUROS

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.