Utilisation de l’Audit de SQL Server 2008 dans un trigger sur une table Project Server

Bonjour,

Vous êtes nombreux à utiliser les triggers sous SQL Server pour ajouter des commandes et des actions basés sur des évènements se produisant sur une table de Project Server 2010.

Que ce soit pour alimenter une table externe ou envoyer une notification par mail, le trigger est une solution simple et efficace d’étendre les fonctionnalités de Project Server 2010, même si nous recommandons de l’utiliser avec parcimonie du fait de l’impact non négligeable qu’il peut avoir sur les performance de Project Server, pouvant dans un cas extrême, provoquer des deadlocks.

Mais le trigger peut aussi avoir un intérêt lorsqu’il faut chercher la cause d’une action dont on ne connait pas la cause.

Ainsi, prenons l’exemple d’un cas dans lequel des enregistrements sont supprimés d’une table, sans que l’on sache pourquoi, mais qui provoque des disparitions de lignes dans les Feuilles de temps ou dans l’affichage de Mes Tâches.

En fait, et je vous passe tous les détails de la recherche de cause, ces enregistrements sont supprimés de la tables MSP_TASKS_SAVED de la base Published, créant ainsi des enregistrements orphelins dans la tables MSP_ASSIGNMENTS_SAVED.

Comme nous n’avons de scénario pour reproduire à volonté ce phénomène, il nous reste le choix entre:

1. Exécuter SQL Profiler en mode Ring pour loguer toute l’activité de cette base de données sur un temps donné et stopper cette trace dès que le problème est détecté
2. Créer un trigger DDL (les trigger DML ne sont pas utilisables sur une action DELETE) qui logue l’action dans une table temporaire
3. Utiliser le fonction d’Audit de SQL Server 2008 (et R2) pour loguer l’action mais aussi des informations complémentaire sur ce qui a déclencher le trigger

La première solution est la plus facile à mettre en place mais le résultat obtenu peut varier considérablement en fonction du l’activité sur la base de données et surtout, il ne faut pas arrêter la trace trop tard sous peine de perdre les informations intéressantes.

De plus, cette solution a un impact non négligeable sur les performances et ne peut donc pas être utilisée sur une longue période.

La deuxième solution est aussi très facile à implémenter mais le résultat va rester assez limité car on ne pourra obtenir que des informations succintes sur l’évènement : Quand et Qui. Mais impossible de connaitre le Quoi car la fonction EVENTDATA de SQL ne peut être utilisée que dans un trigger DML qui, je le rappelle ne peut pas être associé à une action DELETE sur un table.

La dernière solution est donc la meilleure car en plus de pouvoir vous donner des informations sur le Quand et le Qui, elle va aussi vous indiquer le Quoi. C’est donc cette solution que je vais vous détailler:

Création du fichier de Log

La première étape consiste à créer le fichier de log où seront stockées les informations d’Audit:

use master

go

-- Create the server audit.

CREATE SERVER AUDIT Audit_mytrig

    TO FILE ( FILEPATH = 'C:\' ) ;

GO

-- Enable the server audit.

ALTER SERVER AUDIT Audit_mytrig

WITH (STATE = ON) ;

GO

Création du Trigger

Pour créer le trigger, vous devez exécuter la procédure suivante:

USE PROJECTSERVER_PUBLISHED ;

GO

CREATE DATABASE AUDIT SPECIFICATION Audit_mytrig_Table

FOR SERVER AUDIT Audit_mytrig

ADD (DELETE

     ON MSP_TASKS_SAVED by dbo)

WITH (STATE = ON) ;

GO

A partir ce cette étape, toute action de DELETE sur la table MSP_TASKS_SAVED va écrire une information dans le fichier de log.

Récupérer les informations du fichier de log:

Pour lire le contenu du fichier de log, vous pouvez utiliser la procédure suivante:

USE master

 GO

-- get the audit file

DECLARE @filepattern VARCHAR(300)

DECLARE @folder VARCHAR(255)

DECLARE @auditguid VARCHAR(36)

SELECT @auditguid = audit_guid,@folder = log_file_path

FROM sys.server_file_audits WHERE name = 'Audit_mytrig'

SELECT @filepattern = @folder + '*_' + @auditguid + '*'

-- view the results

SELECT event_time,session_id,a.name AS Action,c.class_type_desc AS ObjectType,

f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement,f.additional_information

FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f

JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type

JOIN sys.dm_audit_actions a ON f.action_id = a.action_id

AND c.securable_class_desc = a.class_desc

WHERE f.action_id <> 'AUSC'

ORDER BY event_time DESC,sequence_number

Voici un exemple de ce que la requête renvoie alors qu’un DELETE vient d’être exécuté sur la table MSP_TASKS_SAVED:

Vous remarquez qu’en plus des informations basiques du Quand et du Qui, on a aussi une information très intéressante sur le Quoi. Cela peut être une requête ou une Procédure Stockée. Dans ce dernier cas, le nom de la procédure stockée apparait dans la colonne Statement.

Conclusion:

En conclusion, on peut dire que cette fonction peut être très utile pour connaitre la raison d’un phénomène que l’on ne peut expliquer. Est-ce une requête ou une procédure stockée liée à du code personnalisé ? Est-ce du code lié à une action particulière faite dans Project Server ?

L’information Statement peut nous aider considérablement pour comprendre ce qu’il se passe et peut être un très bon point de départ à la recherche d’une cause ou d’un scénario de reproduction.

N’hésitez pas à commenter cet article.

Bonne journée,

Marc Biarnès