Hacer Una Bitacora o Log

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