jueves, 8 de septiembre de 2011

Auditando Sql Server - Consultando el Traceo x Default -

Amigos,

El Sql Server nos ofrece a partir la versión 2005 una interesantísima herramienta que nos permite auditar cuestiones relacionadas con performance y seguridad de la BD.
En efecto el Traceo por Default nos brinda una gran cantidad de información acerca de los cambios acontencidos en el sistema.
Esta herramienta consiste en 5 archivos de traceo (.trc) localizados en el directorio de instalación del Sql Server . Se encuentra habilitada por default.

Para asegurarnos de que el traceo está habilitado utilizamos la siguiente query:

-- Traceo Habilitado? --
Select * from sys.configurations
Where configuration_id = 1568


Si no está habilitado, (extraño), debemos hacerlo mediante el siguiente script:

-- Habilitación del Traceo --
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO


Para poder utizar la herramienta de traceo, debemos saber actualmente cual es el nombre del archivo sobre el cual el SQL Server está volcando el traceo actualmente. Lo hacemos de la siguiente forma:


SELECT * FROM sys.fn_trace_getinfo(0) ;GO


Para poder estudiar determinados eventos y objetos de la DB es necesario "joinear" nuestra consulta al archivo de traceo con las siguientes vistas:

-- Listado de Eventos que se pueden tracear --
SELECT * FROM sys.trace_events

-- Listado de Categorías que se pueden tracear
SELECT * FROM sys.trace_categories

Ya tenemos todos los elementos...,pongamos manos a la obra... Antes tengamos en cuenta que el Sql Server guarda sólo 5 archivos de 20 mb. Cuando termina de escribir el archivo quinto, realiza un "roll over" (borrado), del archivo más viejo...

El siguiente script sirve para tracear todos los eventos del tipo "Data File Autogrow" que acontecieron en la BD

-- Para Estudiar Evento Datafile Autogrow --

SELECT starttime, endtime,duration,ntusername,applicationname,textdata,loginname,loginsid,spid,hostname,applicationname,
servername,databasename,objectName,e.category_id,trace_event_id,eventsequence, cat.name as [CategoryName],
eventclass,eventsubclass, e.name as EventName,clientprocessid
FROM ::fn_trace_gettable('D:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_307.trc',0)

-- Inner Eventos a Trazear --
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id

-- Inner en Categorías --
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id

WHERE
objectname IS NULL AND -- filter by objectname
e.category_id = 2 AND -- category 2 database
e.trace_event_id in ('92')-- event "datafile autogrow"



Vamos a ver otra utilidad, otro análisis que podemos hacer y que tiene que ver directamente con la performance de nustras queries.
Hay eventos de error que el sql server loguea cuando no tiene suficiente memoria para realizar ordenamientos y operaciones hash y termina utilizando disco para resolver las operaciones. Estas operaciones son eventos del tipo Sort Warning y Hash... Vamos a ver como queriar la traza para obtener los detalles. Aqui el script...

-- Para Estudiar Eventos de Hash y Sort Warnings --

SELECT starttime, endtime,duration,ntusername,applicationname,textdata,loginname,loginsid,spid,hostname,applicationname,
servername,databasename,objectName,e.category_id,trace_event_id,eventsequence, cat.name as [CategoryName],
eventclass,eventsubclass, e.name as EventName,clientprocessid
FROM ::fn_trace_gettable('D:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_305.trc',0)

-- Inner Eventos a Trazear --
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id

-- Inner en Categorías --
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id

WHERE
e.trace_event_id in ('55','69')-- event "Hash Warning"-"Sort Warning"



Se va poniendo linda la cosa. Vamos ahora a realizar una tarea de auditoría... Qué tal si queremos saber quienes han realizado modificaciones de estructuras en nuestra BD? Para ello podemos utilizar el siguiente script:

-- Para Estudiar Eventos "Object:Created"-"Object:Deleted"-"Object:Altered"-

SELECT starttime, endtime,duration,ntusername,applicationname,textdata,loginname,loginsid,spid,hostname,applicationname,
servername,databasename,objectName,
CASE ObjectType
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 21577
THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE 'Hmmm???'
END AS ObjectType,
e.category_id, trace_event_id,eventsequence, cat.name as [CategoryName],
eventclass,eventsubclass, e.name as EventName,clientprocessid
FROM ::fn_trace_gettable('D:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_307.trc',0)

-- Inner Eventos a Trazear --
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id

-- Inner en Categorías --
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id

WHERE
objectname IS not NULL AND -- filter by objectname
e.trace_event_id in ('47','46','164')-- event "Object:Created"-"Object:Deleted"-"Object:Altered"



Por último vamos a ver la forma de estudiar los reinicios del SQL Server. Vemos...

-- Para Estudiar Un evento de "Audit Server Starts and Stops" --

SELECT starttime, endtime,duration,ntusername,applicationname,textdata,loginname,loginsid,spid,hostname,applicationname,
servername,databasename,objectName,e.category_id,trace_event_id,eventsequence, cat.name as [CategoryName],
eventclass,eventsubclass, e.name as EventName,clientprocessid
FROM ::fn_trace_gettable('D:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_303.trc',0)

-- Inner Eventos a Trazear --
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id

-- Inner en Categorías --
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id

WHERE
e.trace_event_id in ('18')-- event "Audit Server Starts and Stops"


CONCLUSION:

Amigos, como verán esta herramienta de traceo por dafaul que el Sql Server nos ofrece es excelente. Todo tìpo de evento y objeto es consultable... Sólo tiene que indagar sobre la vista de eventos y la vista de categorías. Ya saben.. no hay excusas para desconocer el "Que" "Cómo" "Cuándo" "Donde" y "Por Qué" dentro de nuestra Base de Datos...
Cualquier duda, como siempre quedo a vuestra disposición, saludos!!!

10 comentarios:

  1. UNA CONSULTA AMIGO, COMO O DONDE SE GRABA TODAS LAS OPERACIONES DML QUE REALIZO EN MI BASE DE DATOS EN QUE TABLA DEL SISTEMA O EN Q ARCHIVO.

    AH Y SE PUEDE SABER QUE PROCEDIMIENTO EJECUTO UN TRIGGER OSEA POR EJEMPLO ATRAVES DE UN PROCEDIMIENTO ACTUALIZO UN TABLA Y ESTA TABLA TIENE UN TRIGGER. COMO PUEDO SABER ATRAVES DEL TRIGGER QUE PROCEDIMIENTO(NOMBRE) FUE EL QUE REALIZO LA ACCION?

    ResponderEliminar
  2. Buen dia, gracias por tu comentario.
    No hay ciertamente ningun archivo o tabla del sistema donde se guarden una a una las intrucciones que referís.
    Tampoco hay nada que por default te permita saber quien ejecutó al triguer.
    Lo siento, un saludo.

    ResponderEliminar
  3. La otra forma, solo si estás logueando "full", sería leer el log de transacciones con la siguiente sentencia:
    SELECT * FROM ::fn_dblog(NULL, NULL)

    Ciertamente es muy dificil de interpretar al información resultante.

    Hay herramientas específicas que podés utilizar para tal fin como la sgte "Log Explorer for SQL Server", de Lumigent.

    Sdos.

    ResponderEliminar
  4. La otra es utilizar el Profiler y "tirar una traza" de x tiempo, para luego si, poder revisar una a una las sentencias ejecutadas.

    ResponderEliminar
  5. ok muchas gracias. :).

    ResponderEliminar
  6. un consulta el SQL tiene un tabla donde se guarda los errores que se generan al momento de realizar un proceso(insercion, actualizacion, eliminacion)? o un tabla donde se guarda que procedimientos, funciones, trigerre, etc se han usado?.
    oh Algo asi como el EVENTADATA() que es para base de datos, donde se puede sacar que tablas se modifco en su estructura, hay alguno para tablas o triggers o procedimientos?

    ResponderEliminar
  7. Buen dia. Lamentablemente la única forma de controlar errores al nivel que vos querés es implementando rutinas de Try Cach x bloques.
    Hay un post en este blog dedicado a ello, es muy sencillo, cualquier cosa me avisás. Sdos. gracias.

    ResponderEliminar
  8. Hola una consulta como hago para ejecutar un procedimiento cada segundo?. algo asi como un snapshot en Oracle que te permite correr un procedimiento cada cierto tiempo. Saludos

    ResponderEliminar
  9. Muy bueno, Muchas gracias..

    ResponderEliminar