Jump to content
Fórum Script Brasil
  • 0

Monitorar atividades dos usuários - Postgres


Ninja2112

Question

Ola...

Desejo preparar um relatorio sobre as atividades individuais dos usuários(inserts,updates,...). Como posso fazer isto com o objetivo de monitorar a produção ? A versão que trabalho é Postgres 9.0 com PgAdmin III. Já tentei com pg_stat_activity e pg_stat_statement mas não fornecem os dados que preciso.                                                                        Desejo pegar o resultado dessa query e exportar para um arquivo .xls por exemplo...

Grato

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Bom dia, uso uma única trigger genérica e uma única tabela para gravar o log de todas as tabelas a serem monitoradas. Todos os campos das tabelas são gravados no campo dados1 do tipo jsonb. No exemplo1, todas as tabelas tem como chave o campo id, e para comandos update, somente os campos que foram atualizados são gravados nos campos dados1 e dados2, depois e antes da atualização, respectivamente.

Você pode simplificar e fazer o comando do insert funcionar para o update (exemplo2), nesse caso pode excluir o campo dados2 da tabela _generic_log.

Caso deseje gravar os dados antes da alteração, e as tabela possuírem os campos chaves com nomes diferentes, já que se trata de um banco antigo, para cada tabela repetir o comando, substituindo a palavra key pelo nome da campo chave da tabela (exemplo3).

Na ultima parte, tem a rotina que associa todas as tabelas do banco para gravar na tabela _generic_log, com exceção as tabelas que estão as linha do IF, nesse caso a própria tabela _generic_log, pois entrará em loop ao incluir seu próprio log.

Feito isso, a tabela _generic_log terá as informações necessárias para você monitorar todas as ações dos usuários.

-- ********************

drop table if exists public._generic_log;

create table public._generic_log (
  id serial not null primary key,
  tabela varchar(50) not null,
  usuario varchar(50) not null default current_setting('app.user_name'),
  ip_address varchar(15) not null default current_setting('app.ip_address'),
  mac_address varchar(17) not null default current_setting('app.mac_address'),
  data_hora int8 not null default to_char(now(), 'yyyymmddHH24missms')::int8,
  operacao char not null check (operacao in ('I', 'U', 'D')),
  dados1 jsonb not null,
  dados2 jsonb null );
 
create index _generic_log_ind on public._generic_log using brin ( tabela );

-- ***************************************************

exemplo1

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if tg_op = 'INSERT' then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;        
  else -- update
    insert into _generic_log (tabela, operacao, dados1, dados2 )  
      select *
        from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                 from jsonb_each( to_jsonb( new ) ) n
                 join jsonb_each( to_jsonb( old ) ) o on n.key = o.key
                 where n.key in ('id','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
        where dados1 <> dados2;            
  end if;  

  return null;
end;
$$ language plpgsql;]

-- ****************************                    

exemplo2

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if (tg_op = 'INSERT') or (tg_op = 'UPDATE') then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;             
  end if;  

  return null;
end;
$$ language plpgsql;
                          
-- ****************************                    

exemplo3

drop function if exists public.check_generic_log();

create or replace function public.check_generic_log() returns trigger as $$
begin    
  if (tg_op = 'INSERT') or (tg_op = 'UPDATE') then
    insert into _generic_log ( tabela, operacao, dados1 )
     select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value )
       from jsonb_each( to_jsonb( new ) ) n;
  elseif tg_op = 'DELETE' then
    insert into _generic_log ( tabela, operacao, dados1 )
      select tg_table_name, tg_op::char, jsonb_object_agg( o.key, o.value )
        from jsonb_each( to_jsonb( old ) ) o;             
  else -- update

    if tg_table_name = 'bancos' then
      insert into _generic_log (tabela, operacao, dados1, dados2 )  
        select *
          from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                   from jsonb_each( to_jsonb( new ) ) n
                   join jsonb_each( to_jsonb( old ) ) o on n.cdbanco = o.cdbanco
                   where n.key in ('cdbanco','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
          where dados1 <> dados2;       
    elseif tg_table_name = 'agencias' then
      insert into _generic_log (tabela, operacao, dados1, dados2 )  
        select *
          from ( select tg_table_name, tg_op::char, jsonb_object_agg( n.key, n.value ) as dados1, jsonb_object_agg( o.key, o.value ) as dados2
                   from jsonb_each( to_jsonb( new ) ) n
                   join jsonb_each( to_jsonb( old ) ) o on n.cdagencia = o.cdagencia
                   where n.key in ('cdagencia','n.key') or (coalesce(n.value, o.value) is distinct from coalesce(o.value, n.value) and n.key <> 'data_hora_log')) a
          where dados1 <> dados2;  
    end if;
     
  end if;  

  return null;
end;
$$ language plpgsql;

-- ***************************************************
-- ***************************************************
--
--  Executa a trigger generica para todas as tabela do banco de dados
--

do
$$
  declare _tabela varchar(100);
  declare _obj record;
begin  
  for _obj in select schemaname as schema, relname as tabela from pg_stat_user_tables where substring(relname for 1 from 1) <> '_' order by relname loop

    _tabela := _obj.schema || '.' || _obj.tabela;
    
    execute format('drop trigger if exists log_generic on %s;', _tabela );
    execute format('drop trigger if exists log_generic_update on %s;', _tabela );
    
    if not _obj.tabela = any (values ('_generic_log','_tabela1...','_tabela2...')) then

      -- insert or delete
      execute format('create trigger log_generic after insert or delete on %s for each row execute function public.check_generic_log();', _tabela );
      
      -- update --> somente se houve alteração ==> for each row WHEN (old.* is distinct from new.*)        
      execute format('create trigger log_generic_update after update on %s for each row when (old.* is distinct from new.*) execute function public.check_generic_log();', _tabela );
    end if;
  end loop;
end;
$$

Edited by Ronivaldo Lopes
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



  • Forum Statistics

    • Total Topics
      152.1k
    • Total Posts
      651.9k
×
×
  • Create New...