Auditoría y reportes de bases de datos SQL Server continuos usando el registro de transacciones

Los archivos de registros de transacciones de la base de datos en SQL Server son continuamente inyectados con información transaccional y detalles acerca de los cambios de la base de datos por SQL Server mismo. Incluso cuando la información desde los archivos de registros de transacciones y copias de seguridad pueden ser usados como una fuente sólida para auditorías de bases de datos, SQL Server no provee una solución sólida para utilizar los archivos de registros de transacciones en su potencial completo, ni ofrece una manera simple de explorar esos archivos de registros de transacciones o analizar la información dentro para realizar una auditoría continua de la base de datos SQL.

Para usar la información dentro de los archivos de registros de transacciones y las copias de seguridad, ApexSQL Log es una herramienta poderosa de auditoría y recuperación que no sólo permitirá al usuario explorar sus archivos de registros de transacciones, sino que proveerá una solución fácil para cumplir con los principales requerimientos de auditoría:

• Auditoría continua de cambios DML y DDL – que aseguran que todos los cambios de la base de datos SQL Server son auditados sin crear ninguna entrada duplicada.

• Auditoría sin supervisión/automatizada – flujo constante e ininterrumpido de trabajos nocturnos de auditoría que son pre programados por cada plan de auditoría.

• Los datos auditados que está almacenada en tablas de repositorio – que pueden ser creadas directamente dentro de la base de datos SQL Server auditada o alguna otra específica.

• Acceso fácil y rápido a los datos auditados ya sea consultando el repositorio directamente o creando reportes de auditoría.

Solución

En este artículo vamos a mostrar cómo configurar ApexSQL Log para realizar una auditoría continua nocturna de los archivos/copias de seguridad del registro de transacciones de una base de datos SQL Server y crear tablas de repositorio que almacenarán todos los datos auditados creando una sesión repetible en ApexSQL Log. Luego, vamos a mostrar cómo automatizar la ejecución de esta sesión de manera regular usando PowerShell. Finalmente, vamos a tratar las capacidades de reporte y cómo consultar las tablas repositorio manualmente, lo cual se puede traducir en la creación de varios procedimientos almacenados.

Resumen rápido

• Configure ApexSQL Log para auditoría continua – ApexSQL Log es configurada de cierta manera para agarrar automáticamente todos los archivos de registros de transacciones existentes y a ser creados, así como continuar cada trabajo de auditoría subsecuente donde el trabajo previo ha finalizado.

• Proceso de automatización – utilizando el proyecto de ApexSQL Log, veremos cómo usar Windows PowerShell para programar trabajos de auditoría de modo que estos puedan correr en la noche- El script de automatización puede ser encontrado aquí.

• Finalmente, veremos cómo extractar la información auditada desde las tablas de repositorio.

Descripción

Como se mencionó arriba, comenzamos creando y configurando un sesión en ApexSQL Log.

1. Una vez que ApexSQL Log es iniciado, una nueva sesión es automáticamente iniciada, y el usuario puede siempre iniciar una sesión fresca haciendo clic en el botón ‘New’ en la cinta de la aplicación principal.

2. En el primer paso del asistente de sesión, elija un SQL Server desde el menú desplegable de servidores, elija entre los métodos de autenticación de Windows o SQL Server y provea credenciales apropiadas (si aplica). Finalmente, elija una base de datos para auditar y haga clic en el botón Next para proceder.

   

3. En el siguiente paso del asistente, Data sources, es importante asegurarnos de que ApexSQL Log busque en los archivos de registros de transacciones apropiados que contienen la información apropiada para la auditoría. En los casos cuando los respaldos de los registros de transacciones estén siendo creado regularmente, es importante asegurar que todos los archivos sean incluidos en el proceso de auditoría y que son automáticamente añadidos como fuentes por la aplicación. Para hacer esto, usaremos la característica de patrón de nombre que recuperará todos los archivos de registros de transacciones desde las carpetas especificadas e incluiremos todas las copias de seguridad de registros de transacciones que coincidan con el patrón provisto – los caracteres comodines deberían ser usados aquí para configurar el patrón para coincidir con condiciones de ambientes específicas. Para hacer esto, simplemente haga clic en el botón ‘Add pattern’, provea la localización de la carpeta y configure el patrón usando comodines – los archivos que coinciden con el patrón serán mostrados inmediatamente para asegurar que esto ha sido apropiadamente configurado. Haga clic en OK para cerrar la ventana de configuración de patrones, y asegúrese de que el patrón está seleccionado como fuente de datos. Adicionalmente, los registros de transacciones en línea deberían permanecer seleccionados como otra fuente de taos, pero pueden ser deseleccionados/excluidos si el usuario no desea usarlos como fuentes de datos debido a requerimientos específicos – esto no afectará la salida de la auditoría, sino que sólo afectará el último trabajo de auditoría – si el registro de transacciones no está seleccionado, cada última auditoría terminará con la última información desde la copia de seguridad del registro de transacciones, y no incluirá más información residiendo en el archivo de registro de transacciones hasta que la nueva copia de seguridad es creada. Para lograr el mejor desempeño, es recomendado crear copias de seguridad de registros de transacciones regularmente (por ejemplo, cada hora) y generar copias de seguridad de la base de datos también regularmente (por ejemplo, semanalmente) – esto asegurará que sólo las últimas copias de seguridad son usadas en el proceso de auditoría, haciéndolo más rápido y menos intensivo en el impacto en el desempeño en comparación con auditar cadenas interminables de registros de transacciones o archivos de registros en línea gigantes.

   Nota: Para que ApexSQL Log provea los detalles completos de la auditoría, es importante siempre incluir la cadena completa de copias de seguridad de los registros de transacciones (todas las copias de seguridad de registros de transacciones desde la última copia de seguridad completa de la base de datos en adelante) en el trabajo de auditoría, de manera que algunos usuarios puedan considerar ajustar sus políticas de creación/retención de copias de seguridad para asegurar que la cadena completa esté siempre disponible a ApexSQL Log.

   

4. Una vez que provedemos a través del asistente, el paso ‘Select output’ nos proveerá con varias elecciones para la salida de la auditoría. Para propósitos de auditoría continua, optemos por la opción ‘Export results’.

   

5. Esto nos trae a la configuración de filtros en el asistente de sesión. Primero, el usuario debería seleccionar el filtro ‘Continuous auditing’ y elegir la localización donde el archivo de rastreo será almacenado – este archivo almacenará la información acerca de dónde terminó el último trabajo de auditoría continua (valor LSN de la última transacción) para asegurar que el siguiente trabajo continúa desde el primer LSN que sigue cuando el siguiente trabajo de auditoría sea iniciado.

   

   Adicionalmente, los usuarios pueden usar varios filtros para afinar su trabajo de auditoría. Un buen lugar para empezar es primero elegir qué operaciones DML/DDL incluir en el trabajo de auditoría, luego proceder a elegir qué tablas de base datos/sistema serán incluidas en el proceso, y elegir algunos filtros incluyendo filtros de usuario, de transacciones, de SPID, así como algunas opciones de filtración adicionales en este paso del asistente.

   

6. Una vez que los filtros hayan sido configurados, el paso final del asistente nos permite configurar la salida de exportación. Primero, optemos por la opción ‘Export to database’, dado que estamos apuntando a insertar la información de la auditoría en las tablas repositorio. Luego, haga clic en el botón ‘Select database’ y elija la base de datos en la cual serán creadas las tablas repositorio y la información se inyectará ahí (el usuario necesitará elegir SQL Server y proveer credenciales apropiadas), y haga clic en Connect.

7. Finalmente, haga clic en el botón ‘Save’ y provea el nombre de la sesión para grabar la sesión y complete el proceso de configuración.

   

Ahora que la sesión ha sido creada, puede ser usada desde la Interfaz Gráfica de la aplicación cuando sea que el usuario lo requiera (haciendo clic en el botón ‘Open’ en la cinta principal y yendo a través del asistente pre definido), o la sesión puede ser usada para el proceso de automatización, el cual es lo que mostraremos a continuación.

Automatización de la auditoría

ApexSQL Log soporta CLI completamente y también, todas las características de la Interfaz Gráfica están disponibles a través de los interruptores CLI. Más información acerca de los comandos CLI disponibles puede ser vista en este artículo.

Para automatizar el trabajo de auditoría continua y correr de forma regular trabajos de auditoría en intervalos predefinidos (y en rangos de tiempo específicos), podemos usar varias herramientas. Por ejemplo, tal automatización puede ser lograda creando un archivo de script de lotes que puede ser programado vía el programador de Windows o una herramienta similar. Otra solución es usar Windows PowerShell para crear y programar la tarea que es superior al primer ejemplo, dado que todo es logrado en el proyecto PowerShell, y no hay necesidad de usar herramientas adicionales para programar.

Para automatizar el trabajo de auditoría vía PowerShell, llamaremos al archivo de la sesión de ApexSQL Log (.axlp) que hemos creado al final del asistente previamente descrito.

Aquí están los comandos que usaremos:

Con estos comandos, podemos programar una tarea de auditoría regular. Aquí está un ejemplo de un script PowerShell que creará una tarea “ApexSQL Log continuous auditing”que correrá la sesión predefinida (grabada como c:\ApexSQL Log\Saved sessions\Continuous auditing.axlp) cada hora, cada día entre las 05:00 AM y las 11:00 PM.

Schtask.exe /create /tn ApexSQL Log continuous auditing /sc hourly /st 05:00 /et 23:00 /tr c:\Program Files\ApexSQL\ApexSQL Log\ApexSQLLog.com c:\ApexSQL Log\Saved sessions\Continuous auditing.axlp

Reportes

Ahora que hemos visto cómo crear y automatizar la auditoría continua, enfoquémonos en los reportes. Dado que toda la información auditada con el ejemplo de arriba está siendo almacenada en las dos tablas de la base de datos SQL Server, la manera más fácil de extraer la información es consultando las tablas directamente vía SQL Server Management Studio o cualquier otra herramienta similar. Para información detallada acerca de la topología del repositorio, consulte la guía detallada en el artículo en la Base de Conocimientos “ApexSQL Log continuous auditing repository topography” (Topografía del repositorio de auditoría de ApexSQL Log). Mientras que correr reportes básicos como la auditoría diaria o los cambios por reportes de historial de tablas específicas o usuarios pueden ser fácilmente realizados, obtener más datos específicos en los reportes puede ser difícil para algunos usuarios con menos experiencia en SQL Server. Para aquellos que prefieren consultar la base de datos directamente, una guía detallada acerca de cómo trabajar con tablas de repositorio directamente se ofrece en el artículo “How to work with the ApexSQL Log continuous auditing repository directly, including querying and reporting” (Cómo trabajar con el repositorio de auditoría continua de ApexSQL directamente, incluyendo consultas y reportes). Para aquellos que desean llevar los reportes un paso más allá, aquí están los ejemplos de los reportes más comúnmente usados:

Transacciones mensuales – este reporte mostrará todas las acciones en el rango de tiempo especificado en la variable @time_frame (la variable @time_frame puede ser cambiada para ajustar la precisión por fecha/tiempo. Por ejemplo, ‘YYYY-MM-DD-hh’ mostrará todas las transacciones a nivel de horas).

DECLARE @time_frame AS VARCHAR(50)

SET @time_frame = 'yyyy-MM'

SELECT FORMAT(TRANSACTION_BEGIN, @time_frame) AS "Date/Time"
	,count(DISTINCT TRANSACTION_ID) AS "Transaction Count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
GROUP BY FORMAT(TRANSACTION_BEGIN, @time_frame)

Transacciones diarias – este reporte mostrará todas las transacciones por día.

SELECT CONVERT (DATE
	,TRANSACTION_BEGIN) AS "Date"
	,count (DISTINCT TRANSACTION_ID) AS "Transaction Count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
GROUP BY CONVERT (DATE
	,TRANSACTION_BEGIN

Conteo de operaciones por tipo – este reporte mostrará el número de operaciones por tipo de operación.

SELECT OPERATION_TYPE
	,COUNT(OPERATION_TYPE) AS "Operation count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
GROUP BY OPERATION_TYPE

Conteo de operaciones por fecha (y tipo) – este reporte mostrará el número de operaciones específicas separadas por fecha.

SELECT CONVERT 8DATE
	,TRANSACTION_BEGIN) AS "Date"
	,OPERATION_TYPE
	,COUNT (OPERATION_TYPE) AS "Operation count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
GROUP BY OPERATION_TYPE
	,CONVERT (DATE
	,TRANSACTION_BEGIN)

Conteo de operaciones por tipo y fecha por operación – este reporte mostrará el número de operaciones por fecha para tipos de operaciones específicos. Para configurar el reporte para tipos de operaciones específicos, simplemente establezca la variable @operation_type a operaciones específicas (por ejemplo, @operatio_type = ‘DELETE’ para obtener los resultados de operaciones de eliminación).

DECLARE @operation_type AS VARCHAR(50)

SET @operation_type = 'INSERT'

SELECT CONVERT (DATE
	,TRANSACTION_BEGIN) AS "Date"
	,(OPERATION_TYPE)
	,COUNT OPERATION_TYPE AS "Operation count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
WHERE OPERATION_TYPE = @operation_type
GROUP BY OPERATION_TYPE
	,CONVERT (DATE
	,TRANSACTION_BEGIN)

Conteo del top 10 de transacciones por tabla – este reporte mostrará el conteo del top 10 de transacciones para las tablas de la base de datos. Para configurar el rango de tiempo, establezca las variables @start_date y @end_date a fechas específicas desde/a.

DECLARE @start_date AS VARCHAR(50)
DECLARE @end_date AS VARCHAR(50)

SET @start_date = '2016-06-01'
SET @end_date = '2016-12-01’
SELECT TOP 10 OBJECT_NAME, count(distinct TRANSACTION_ID) as "Transaction Count" FROM [dbo].[APEXSQL_LOG_OPERATION] WHERE TRANSACTION_BEGIN > @start_date AND TRANSACTION_END < @end_date group by OBJECT_NAME ORDER BY "Transaction Count" DESC

Conteo del top 10 de transacciones por usuario – este reporte mostrará el top 10 de transacciones por usuario. Como con el reporte previo, para configurar el rango de tiempo, establezca las variables @start_date y @end_date a fechas específicas desde/a.

DECLARE @start_date AS VARCHAR(50)
DECLARE @end_date AS VARCHAR(50)

SET @start_date = '2016-6-01'
SET @end_date = '2016-12-12'

SELECT TOP 10 USER_NAME
	,count(DISTINCT TRANSACTION_ID) AS "Transaction Count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
WHERE TRANSACTION_BEGIN > @start_date
	AND TRANSACTION_END < @end_date
GROUP BY USER_NAME
ORDER BY "Transaction Count" DESC

Top 10 de las transacciones con más tiempo de ejecución – este reporte muestra las transacciones con más tiempo de ejecución con detalles apropiados (cuándo ocurrió la transacción, el usuario que la corrió…) que pueden ser configurados para añadir más columnas a la tabla.

SELECT DISTINCT TOP 10 TRANSACTION_ID
	,CAST TRANSACTION_END - TRANSACTION_BEGIN AS TIME AS "Transatcion_duration"
	,USER_NAME
	,TRANSACTION_BEGIN
	,TRANSACTION_DESCRIPTION
FROM [dbo].[APEXSQL_LOG_OPERATION]
ORDER BY CAST(TRANSACTION_END - TRANSACTION_BEGIN AS TIME) DESC

Conteo del top 10 de transacciones por operación – este reporte muestra las transacciones que han incluido la mayor cantidad de operaciones.

SELECT TOP 10 TRANSACTION_ID
	,MAX(USER_NAME) AS "Username"
	,MAX(TRANSACTION_BEGIN) AS "Start time"
	,COUNT(TRANSACTION_ID) AS "Operation count"
FROM [dbo].[APEXSQL_LOG_OPERATION]
GROUP BY TRANSACTION_ID
ORDER BY COUNT(TRANSACTION_ID) DESC

Todas las consultas mostradas arriba pueden volverse procedimiento almacenados para propósitos de reutilización, usando el código estándar ‘create stored procedure’:

CREATE PROCEDURE < PROCEDURE_NAME
	,SYSNAME
	,ProcedureName >
	-- Add the parameters for the stored procedure here
	< @Param1
	,SYSNAME
	,@p1 > < Datatype_For_Param1
	,
	,INT > = < Default_Value_For_Param1
	,
	,0 >
	,< @Param2
	,SYSNAME
	,@p2 > < Datatype_For_Param2
	,
	,INT > = < Default_Value_For_Param2
	,
	,0 >
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Insert statements for procedure here
	SELECT < @Param1
		,SYSNAME
		,@p1 >
		,< @Param2
		,SYSNAME
		,@p2 >
END
GO

Ahora que hemos creado varios reportes y los hemos grabado como procedimientos almacenados, podemos usar el Servicio SQL Server Reporting para completar el proceso de reporte y crear reportes basados en estos procedimientos almacenados. Los reportes pueden ser mejorados en muchas maneras, incluyendo añadir gráficos, listas y muchos otros elementos a los reportes. La guía acerca de cómo crear tales reportes puede ser encontrada aquí

Preguntas Frecuentes

¿Puedo correr la sesión de ApexSQL Log manualmente cuando es requerido (en adición a los trabajos automatizados)?

R: Sí – esto le permitirá realizar una auditoría inmediata cuando sea necesario y simplemente actualizará la información de rastreo de manera que el trabajo automatizado pueda empezar donde usted terminó.

Quiero realizar una auditoría continua manual, pero no quiero afectar a la auditoría ya automatizada – ¿qué pasos debería realizar?

Si el usuario desea continuar la auditoría cuando el proceso previo ha iniciado sin afectar el archivo actual (activo) de rastreo, simplemente haga una copia de él y especifique la localización del archivo copiado en el campo de localización correspondiente en el paso de filtro de fecha/tiempo en el asistente de sesión.

¿Puedo tener múltiples trabajos de auditoría continua separados en la misma base de datos al mismo tiempo?

Sí, sólo asegúrese de que cada trabajo tiene su archivo de rastreo separado y que los trabajos no están programados en el mismo tiempo (sólo un trabajo de auditoría puede correr al mismo tiempo).

Descargas

Por favor descargue los scripts asociados con este artículo desde nuestro repositorio GitHub.

Por favor contáctenos para cualquier problema o pregunta con los scripts.