Hacer Una Bitacora o Log

[rgstuamigo]

Hola amigos del foro, estoy haciendo un Sistema de Informacion y quisiera saber como puedo hacer una Bitacora o Blog para las actualizaciones,insersiones,eliminaciones de todas las tablas de una base de datos, de manera que pudiera saber entre un rango de fecha y/o hora las modificaciones que hubieron en mi base de datos (BD) para un usuario especifico. Es decir un ejemplo: poder saber lo que hizo el ususrio 'X' en la base de datos a partir de la fecha y/o hora 'f1' hasta la fecha 'f2'.y poder verlo detalladamente.
Cualquier sugerencia o ayuda es bienvenida....Gracias..

[[felipe88]]

Editado:
Esto no sirve...

[rgstuamigo]

Gracias por tu respuesta Felipe88 pero, no se vale usar un programa exterior a tu sistema , yo se de la existencia de estos programas,y se que son buenos , lo que necesito es hacerlo yo, he estado mirando otros foros ,pero pareciera que nadie ha implementado algo parecido,bueno al menos no he pillado, de todas manera cualquier comentario o ayudita sera bienvenida.....

[[felipe88]]

Editado:
Esto tampoco sirve... Dios!!!

[[maeyanes]]

Hola...

felipe88, creo que el título que puso rgstuamigo fue el causante de que te equivocaras. El no busca crear un Blog, si no un Log o Bitácora, donde pueda ir guardando los movimientos que realize X usuario en Y rango de tiempo...

rgstuamigo, una forma de implementar algo así es con la ayuda de los disparadores, por ejemplo, en Firebird tenemos disparadores BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE.



Saludos...

[[felipe88]]

Ufff... alguien que me saca de mi ignorancia ...

[rgstuamigo]

Si amigo maeyanes es LOG en ves de Blog tienes razon,
Bueno mi base de datos esta en Mysql, y se un poco de disparadores o Triggers, pero quisiera una idea o sugerencia , que sé yo por ahi crear una tabla llamada bitacora,donde se guarde todo o algo asi...........

[[poliburro]]

Ok, se me ocurre

Puedes crear una tabla llamada log_NombreTabla con los campos

Fecha, Campo, ValorAnt, ValorAct.

Entonces haciendo uso de los triggers puedes grabar en tu log los valores de los campo.

[rgstuamigo]

Puede ser una buena idea pero no te parece Poliburro que podria haber mucha redundancia de datos, sin contar que por cada tabla habria que hacer eso, seria mejor crear esa tabla pero solo que tenga el valor anterior ,no el actual pues ya esta en la tabla correspondiente.y que se vaya insertando a estatabla todo el el historial....

[Neftali [Germán.Estévez]]

Empezado por poliburro

Puedes crear una tabla llamada log_NombreTabla con los campos
Fecha, Campo, ValorAnt, ValorAct.

Yo a eso añadiría el usuario, al menos, para conocer quien ha hecho las modificaciones.
El campo fecha, entiendo que es FechaHora.

Nosotros tenemos un sistema similar, aunque bastante mas complejo. El problema es que eso implicaría muchos cambios en el diseño. Todos nuestras sentencias SQL están centralizadas, por lo que en ese punto es más sencillo guardar la información de este tipo.
Si no lo has tenido en cuenta desde un principio, creo que la forma más fácil (por no decir la única) es con Triggers, como te han dicho.

Puedes mejorar, por ejempo, utilizar una tabla auxiliar que te permita activar/desactivar las tablas sobre las que quieres LOG. Normalmente no se necesitas de todas, sino de algunas tablas que se consideran "críticas".

Si posees claves primarias únicas, en todas las tablas, te puede ayudar a posteriormente encontrar y relacionar los datos de las tablas con el LOG.

[[maeyanes]]

Hola...

Empezado por rgstuamigo

Puede ser una buena idea pero no te parece Poliburro que podria haber mucha redundancia de datos, sin contar que por cada tabla habria que hacer eso, seria mejor crear esa tabla pero solo que tenga el valor anterior ,no el actual pues ya esta en la tabla correspondiente.y que se vaya insertando a estatabla todo el el historial....

Sobre lo que dices de que solo contenga el valor anterior, podría acarrearte problema en que no vas a saber en un determinado momento cual fue el valor nuevo de un campo, solo el anterior, ya que para todos los registros de tu bitácora, el valor nuevo siempre será el último que usaste (o el valor actual del campo en cuestión). Es por eso que se necesita guardar en todo momento el valor viejo y el valor nuevo.

Ahora, si necesitas que sea más de una tabla, podrías indicar a que tabla pertenece ese valor, además de que también tienes que identificar a que registro de esa tabla pertenece ese cambio.

Podrías hacer una estructura así:

id: integer (campo clave)
tabla: varchar(32)
id_registro: integer
campo: varchar(50)
valor_anterior: varchar(1024)
valor_nuevo: varchar(1024)
fecha: datetime
usuario: varchar(10)

y ya desde tu programa para saber el historico de X registro de Y tabla puedes tener algo como:

Código SQL [-]
select campo, valor_anterior, valor_nuevo, fecha, usuario from bitacora
where tabla = 'Tabla_X' and id_registro = 1 order by fecha

Saludos...

[[Delphius]]

Disculpen que me entrometa, se que no voy a decir algo demasiado nuevo, pero me parece que cuando se trata de logs o bitácoras debe tenerse en cuenta la dimensión de lo que se desea realmente.
¿Se va controlar todo lo que se opere?¿O por el contrario sólo una parte?

Esto puede influir en el modo de como diseñar la/s tabla/s. En ciertas ocasiones basta con tener una única tabla en donde se identifique:
1. Hora y fecha
2. La acción realizada
3. La tabla donde se realizó la acción. (si corresponde)
4. El campo donde se realizó la acción. (si corresponde)
5. El valor nuevo (si corresponde)
6. El valor viejo (si corresponde)
7. El usuario
8. Etc...

Pero si se necesita de un diseño exquisito o mucho más elaborado, es muy probable que en vez de esta única tabla existan muchos más. Es más, como ya he indicado en otras ocasiones... se puede caer en la idea de añadir un 50% más de tablas.

En fin todo dependerá de los requisitos, de las restricciones, y hasta puede que existan leyes que influyan y/o determinen que, como, y/o cuando guardar en una bitácora. Lo que hay que preguntarse en este momento es ¿Para qué o con qué propósito añadimos está bitácora o log? ¿Quíen la usará? O mejor dicho: ¿Cómo se definirán los perfiles de acceso a la información?

A lo que voy es que debe dimensionarse la realidad del negocio. Si es para el negocio de la esquina, no vale la pena romperse demasiado la cabeza... si es para un sistema bancario, mejor tomarse algunas aspirinas.

Saludos,

[rgstuamigo]

Gracias a todos por darme ideas sobre el asunto.
La idea de hacer una bitacora o log no es para un pequeño sistemita hecho segun mi criterio(empiricamente), sino para poder implementarlo como dije en un Sistema de Informacion que estoy haciendo.(para los que no entiende que es un sistema de informacion)Es un Sistema completo bien detallado,documentado,con casos de usos,modelo de negocio, modelo de dominio,diagramas de secuencias,diagramas de flujo de la informacion,etc,en fin un sin numero de informacion documentada, donde lo que esta escrito en papel debe coincidir con el Sistema (Aplicacion Grande).
Osea no es Hacer por hacer sino seguir una medologia para la implementacion de tu sistema donde requieres de muchos datos.
Espero haber sido claro....Gracias por sus respuestas...
Pero si alguien puede agregar mas ,seria bien recibido por mi parte Saludos...

[lbuelvas]

Bueno, yo he implementado un sistema enerico de auditoria para mis aplicaciones donde se guarda la información de los campos sensibles, es decir, campos donde se requiere conocer que ha he hecho un usuario especificamente.

Se guarda la informacion de nombre de la tabla, usuario, fecha y hora del cambio, valor del identificador de la tabla, valor anterior del campo, valor nuevo del campo (cuando es un update).

No es razonable guadar la información de todos los cambios que hagan todos lo usuarios, solamente los cambios a los campos sensibles, aun asi en muchos de los sistemas que he desarrollado la bitacora de auditoria puede crecer a un ritmo elevado, dándose ocasones donde mas del 50& de los registros de la base de datos lo constituye dicho log.

La estrucutura de la base de datos es asi:

Código SQL [-]
SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                 Generators                                 */
/******************************************************************************/

CREATE GENERATOR GEN_TS_LOG;
SET GENERATOR GEN_TS_LOG TO 0;

CREATE GENERATOR GEN_TS_LOG_DETALLE;
SET GENERATOR GEN_TS_LOG_DETALLE TO 0;

/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE TABLE TS_LOG (
    ID_LOG                INTEGER NOT NULL,
    TABLA_AFECTADA        VARCHAR(40) NOT NULL,
    VALOR_LLAVE_PRIMARIA  VARCHAR(200) NOT NULL,
    OPERACION             VARCHAR(1) NOT NULL,
    USUARIO               VARCHAR(20) NOT NULL,
    FECHA                 DATE NOT NULL,
    HORA                  TIME NOT NULL
);


CREATE TABLE TS_LOG_DETALLE (
    ID_LOG_DETALLE  INTEGER NOT NULL,
    NOMBRE_CAMPO    VARCHAR(80) NOT NULL,
    VALOR_ANTERIOR  VARCHAR(200),
    VALOR_NUEVO     VARCHAR(200),
    ID_LOG          INTEGER NOT NULL
);

/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE TS_LOG ADD CONSTRAINT PK_TS_LOG PRIMARY KEY (ID_LOG);
ALTER TABLE TS_LOG_DETALLE ADD CONSTRAINT PK_TS_LOG_DETALLE PRIMARY KEY (ID_LOG_DETALLE);

/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/

ALTER TABLE TS_LOG_DETALLE ADD CONSTRAINT FK_TSLOGDETALLE_TSLOG FOREIGN KEY (ID_LOG) REFERENCES TS_LOG (ID_LOG);

/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/

CREATE INDEX IDX_TS_LOG_FECHAHORA ON TS_LOG (FECHA, HORA);
CREATE INDEX IDX_TS_LOG_TABLAFECHAHORA ON TS_LOG (TABLA_AFECTADA, FECHA, HORA);

Elabore un editor grafico para revisar una tabla que campos sensibles tiene y ampliar los campo o quitarlos asi:



El codigo SQL de los triggers lo genero desde el aplicativo Delphi y se almacena en la base de datos directamente.

Código SQL [-]
SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
/* Trigger: LOG_ABONO_AD */
CREATE TRIGGER LOG_ABONO_AD FOR ABONO
ACTIVE AFTER DELETE POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (OLD.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha hecho una eliminacion */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'D');

  /*Crear un registro para todos los campos sensibles */
  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'FECHA_ABONO', CAST (OLD.FECHA_ABONO AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'VALOR_PAGAR', CAST (OLD.VALOR_PAGAR AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'SALDO', CAST (OLD.SALDO AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'TOTAL_FACTURA', CAST (OLD.TOTAL_FACTURA AS VARCHAR(50)));

END
^


/* Trigger: LOG_ABONO_AI */
CREATE TRIGGER LOG_ABONO_AI FOR ABONO
ACTIVE AFTER INSERT POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (NEW.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha ingresado un registro */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'I');
END
^


/* Trigger: LOG_ABONO_AU */
CREATE TRIGGER LOG_ABONO_AU FOR ABONO
ACTIVE AFTER UPDATE POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (NEW.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha hecho una actualizacion */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'U');

  /*Crear un registro para todos los campos sensibles */
  if (
     ((old.FECHA_ABONO is     null) and (new.FECHA_ABONO is not null)) or
     ((old.FECHA_ABONO is not null) and (new.FECHA_ABONO is     null)) or
     (old.FECHA_ABONO <> new.FECHA_ABONO)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'FECHA_ABONO', CAST (OLD.FECHA_ABONO AS VARCHAR(50)), CAST (NEW.FECHA_ABONO AS VARCHAR(50)));
  END

  if (
     ((old.VALOR_PAGAR is     null) and (new.VALOR_PAGAR is not null)) or
     ((old.VALOR_PAGAR is not null) and (new.VALOR_PAGAR is     null)) or
     (old.VALOR_PAGAR <> new.VALOR_PAGAR)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'VALOR_PAGAR', CAST (OLD.VALOR_PAGAR AS VARCHAR(50)), CAST (NEW.VALOR_PAGAR AS VARCHAR(50)));
  END

  if (
     ((old.SALDO is     null) and (new.SALDO is not null)) or
     ((old.SALDO is not null) and (new.SALDO is     null)) or
     (old.SALDO <> new.SALDO)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'SALDO', CAST (OLD.SALDO AS VARCHAR(50)), CAST (NEW.SALDO AS VARCHAR(50)));
  END

  if (
     ((old.TOTAL_FACTURA is     null) and (new.TOTAL_FACTURA is not null)) or
     ((old.TOTAL_FACTURA is not null) and (new.TOTAL_FACTURA is     null)) or
     (old.TOTAL_FACTURA <> new.TOTAL_FACTURA)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'TOTAL_FACTURA', CAST (OLD.TOTAL_FACTURA AS VARCHAR(50)), CAST (NEW.TOTAL_FACTURA AS VARCHAR(50)));
  END

END
^

SET TERM ; ^

[[Delphius]]

Empezado por rgstuamigo

Gracias a todos por darme ideas sobre el asunto.
La idea de hacer una bitacora o log no es para un pequeño sistemita hecho segun mi criterio(empiricamente), sino para poder implementarlo como dije en un Sistema de Informacion que estoy haciendo.(para los que no entiende que es un sistema de informacion)Es un Sistema completo bien detallado,documentado,con casos de usos,modelo de negocio, modelo de dominio,diagramas de secuencias,diagramas de flujo de la informacion,etc,en fin un sin numero de informacion documentada, donde lo que esta escrito en papel debe coincidir con el Sistema (Aplicacion Grande).
Osea no es Hacer por hacer sino seguir una medologia para la implementacion de tu sistema donde requieres de muchos datos.
Espero haber sido claro....Gracias por sus respuestas...
Pero si alguien puede agregar mas ,seria bien recibido por mi parte Saludos...

Hola rgstuamigo,
¿Lo dices por mi?
Lo que he señalado con negrita y cursiva, me ha resultado un tanto ofensivo.
Muchos aqui seguramente entendemos lo que es. No es necesario venir y dar un cierto aire a prepotencia indicandonos que lo tuyo es bastante grande y complejo. Al menos... esa es la sensación que me queda.

Cuando yo expuse esto:

Empezado por Yo

A lo que voy es que debe dimensionarse la realidad del negocio. Si es para el negocio de la esquina, no vale la pena romperse demasiado la cabeza... si es para un sistema bancario, mejor tomarse algunas aspirinas

Lo he dicho con el toque de humor. Por algo he añadido esos emoticonos al final.

Lo cierto es que como la ha bien señalado el compañero lbuelvas lo importante no es llevar un log de toda la información sino de la más sencible y la que requiera de cierto control de auditoría.

La forma más usual de implementarla es mediante triggers que registren la información de las operaciones necesarias a monitorear. Creo que el ejemplo de lbuelvas así lo demuestra.

Cuanto más grande sea el proyecto, y más sencible y crítica sean los datos a guardar más complejo será el proceso de auditoría y el manejo del log.

Como he dicho antes, el diseño puede ser lo bastante simple como para tener una simple tabla y unos cuantos triggers que escriben en ella como puede ser lo suficientemente complejo como para que su diseño pueda ser considerado un proyecto entero. Es decir: lo enormemente complejo como para requerir su propios recursos (tiempo, dinero, personal, etc).

Estimativamente se puede llegar (en una situación extrema) a decir que para un sistema grande, complejo, bien documentado, con debidos procesos de auditoría, etc... puede requerir de hasta un 50% más de tablas. Es decir, por ejemplo, que si inicialmente el diseño consta de 50 tablas... añadir los procesos de auditoría podría llegar hasta añadir otras 25.

Lamentablemente el tema puede ser bastante ambiguo. Aqui se te hanpresentado de forma simple como puede llevarse a cabo la actividad. Sin conocer la magnitud a la que te enfrentas muy difícil que podamos asesorarte y decirte si con dos tablas, 5 disparadores, 3 procedimientos almacenados te basta, o por el contrario serán necesario contar con 50 tablas, 6 disparadores por cada tabla, y 10 SP (estoy dando una cifra exagerada para que se entienda).

La pregunta que deberíamos hacerte (y que debes hacerte) es ¿Que tanto deseas auditar?

Puedes imaginarte que por cada tabla que tengas, son posibles como mínimo 6 procesos a "disparar":
1. Antes de insertar un registro
2. Después de insertar un registro
3. Antes de borrar un registro
4. Después de borrar un regitro
5. Antes de actualizar un registro
5. Después de actualizar un registro

Dije mínimo puesto que hay posibles algunos efectos en cascada y pueden intervenir otras acciones.

Imagínate ahora llevar una bitácora que controlo TODO. Como lo ha dicho lbuelvas: sólo el registro de la bitácora se llevará el 50% de los datos.

¿Entiendes la magnitud de lo que se ha hablado?

Saludos,

[rgstuamigo]

Gracias amigos foristas, me han dado muchas ideas para hacerlo ,bueno mi sistema de informacion no es muy grande pero tampoco pequeño, segun mi diagrama de clase he podido obtener unos 30 casos de usos y unas 25 tablas sin contar la bitacora, para los que me entienden esto puede desmenusarse aun mas ,pues por cada caso de uso pueden aparecer aun mas todabia. La verdad enfrentarse con una bitacora es interesante y es verdad hay ser prudente en ese aspecto para guadar solo los datos mas necesarios,pero ya les estare comentando o poniendo algun otro hilo con algun problema con el que me topé.
LA VERDAD QUE ENTRE TODOS APRENDEMOS MUCHISIMOS....
Gracias a todos amigos Y Saludos....

[[Delphius]]

Empezado por rgstuamigo

Gracias amigos foristas, me han dado muchas ideas para hacerlo ,bueno mi sistema de informacion no es muy grande pero tampoco pequeño, segun mi diagrama de clase he podido obtener unos 30 casos de usos y unas 25 tablas sin contar la bitacora, para los que me entienden esto puede desmenusarse aun mas ,pues por cada caso de uso pueden aparecer aun mas todabia. La verdad enfrentarse con una bitacora es interesante y es verdad hay ser prudente en ese aspecto para guadar solo los datos mas necesarios,pero ya les estare comentando o poniendo algun otro hilo con algun problema con el que me topé.
LA VERDAD QUE ENTRE TODOS APRENDEMOS MUCHISIMOS....
Gracias a todos amigos Y Saludos....

¿Más casos de usos? ¿Desmesurarse más?
Ummm, yo diría que antes de seguir partiendo el problema te preguntes si te es tan necesario seguir destinando esfuerzo en seguirle buscando más casos de usos.
¿Cuál es tu propósito de hallar más? ¿Determinar relaciones de include y extends? Si es eso te informo que descubrir las relaciones de los casos por lo general no añaden información nueva y relevante, sólo sirve para reordenar ideas.

Los diagramas de caso de uso no aportan información nueva, lo que hace es ordenar la información existente. Más que eso, por lo general no tiene mucho uso. Si, hay algunas excepciones a la regla y es posible que destinar un poco de esfuerzo por descubrir relaciones de caso de usos nos evitan algunos líos y organizar mejor el trabajo, pero por lo general es preferible evitarse este esfuerzo.

Mi consejo: no le busques demasiadas vueltas. Si hay 25 hay 25, ni 50 ni 30.

Intentar desmesurar más a un caso de uso puede conducir a casos de usos livianos, con pocas acciones, vacíos, sin utilidad alguna.
Un caso de uso debería reflejar solamente lo que añade valor e información al negocio.
Partir el problema puede ser una buena ventaja, pero el tener más partes no te hace un todo: te hace un lío.

Ya nos dirás como avanzas,
Espero que lo que te hemos aportado te sea de ayuda, o al menos para tener otra perspectiva.

Saludos,