Vous êtes ici :
Ce script permet de configurer une session d’évènements étendus pour capturer les évènements d’erreurs utilisateur.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
/*------------------------------------------------------------------- [SCRIPT] User Errors xevent session [DATABASE] master [DESCRIPTION] create an xevent session for user SQL Errors [MAJ PAR] DATAFLY - Arian Papillon [DATE] 20190128 -------------------------------------------------------------------*/ USE master; GO DECLARE @XELPath NVARCHAR(MAX) , @SQLCmd NVARCHAR(MAX); SELECT @XELPath = [path] + 'SQLUserErrors.XEL' FROM sys.dm_os_server_diagnostics_log_configurations; SET @SQLCmd = 'CREATE EVENT SESSION SQLUserErrors ON SERVER ADD EVENT sqlserver.error_reported ( -- ACTION( sqlserver.tsql_stack ) ACTION ( sqlserver.tsql_stack , sqlserver.sql_text , sqlserver.database_id , sqlserver.username , sqlserver.client_app_name , sqlserver.client_hostname ) WHERE (( [error_number] <> 2528 -- DBCC execution completed... AND [error_number] <> 3014 -- BACKUP LOG successfully processed ... AND [error_number] <> 3262 -- The backup set on file is valid ... AND [error_number] <> 4035 -- Processed 0 pages for database ... AND [error_number] <> 5701 -- Changed database context to ,,, AND [error_number] <> 5703 -- Changed language setting to ... AND [error_number] <> 18265 -- Log was backed up. ... AND [error_number] <> 8153 -- NULL Aggregate. ... AND [error_number] <> 3211 -- Progression message. ... AND [error_number] <> 14205 -- (unknown) AND [error_number] <> 14213 -- Core Job Details: AND [error_number] <> 14214 -- Job Steps: AND [error_number] <> 14215 -- Job Schedules: AND [error_number] <> 14216 -- Job Target Servers: AND [error_number] <> 14549 -- (Description not requested.) AND [error_number] <> 14558 -- (encrypted command) AND [error_number] <> 14559 -- (append output file) AND [error_number] <> 14560 -- (include results in history) AND [error_number] <> 14561 -- (normal) AND [error_number] <> 14562 -- (quit with success) AND [error_number] <> 14563 -- (quit with failure) AND [error_number] <> 14564 -- (goto next step) AND [error_number] <> 14565 -- (goto step) AND [error_number] <> 14566 -- (idle) AND [error_number] <> 14567 -- (below normal) AND [error_number] <> 14568 -- (above normal) AND [error_number] <> 14569 -- (time critical) AND [error_number] <> 14570 -- (Job outcome) AND [error_number] <> 17550 -- DBCC Information AND [error_number] <> 17551 -- DBCC Information AND [error_number] <> 14635 -- Mail queued. AND [error_number] <> 14638 -- Activation successful. AND [error_number] <> 15477 -- Rename object AND [error_number] <> 9911 -- alimentation Auto de texte intégral commencée AND [error_number] <> 22866 -- Informations Change Tracking AND [error_number] < 50000 -- Exclude User Errors -- AND [database_id] = 8 -- AND ( [client_hostname] <> ''HOSTXYZ'' ))) -- Target File Mode ADD TARGET package0.asynchronous_file_target ( SET filename = ''' + @XELPath + ''', max_file_size = 256, max_rollover_files = 4 ) WITH ( MAX_MEMORY = 4096KB , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS , MAX_DISPATCH_LATENCY = 1 SECONDS , MEMORY_PARTITION_MODE = NONE , TRACK_CAUSALITY = ON -- Very important to get the event in order later on , STARTUP_STATE = ON -- Note that we start on );'; EXEC sp_executesql @SQLCmd; -- Start the session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'SQLUserErrors' ) ALTER EVENT SESSION SQLUserErrors ON SERVER STATE = START; ELSE RAISERROR( 'ERROR : XEventSession SQLUserError NOT created, cannot start' , 19, 1) WITH LOG; GO |
Pour obtenir les évènements capturés sous forme tabulaire, utiliser le script suivant :
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
/*------------------------------------------------------------------- [SCRIPT] read SQL User Errors xevents [DATABASE] master [MAJ PAR] DATAFLY - Arian Papillon [DATE] 20190128 -------------------------------------------------------------------*/ SET NOCOUNT ON; DECLARE @outputfile NVARCHAR(max) SELECT @outputfile = [path] + 'SQLUserErrors' FROM sys.dm_os_server_diagnostics_log_configurations; -- Récupère les évènements IF ( OBJECT_ID('tempdb..#EventXML') IS NOT NULL ) DROP TABLE #EventXML; DECLARE @path NVARCHAR(260) = @outputfile + '*.XEL' , @mdpath NVARCHAR(260) = @outputfile + '*.xem' , @initial_file_name NVARCHAR(260) = NULL , @initial_offset BIGINT = NULL; SELECT IDENTITY(INT, 1, 1) AS ID , * , CAST(REPLACE(E.event_data, CHAR(3), '?') AS XML) AS X INTO #EventXML FROM master.sys.fn_xe_file_target_read_file( @path ,@mdpath, @initial_file_name, @initial_offset) E; -- Tabularise le XML SELECT node.value('./@timestamp', 'datetime') AS event_time , node.value('./@name', 'varchar(4000)') AS event_name , node.value('(data[@name="message"]/value)[1]', 'varchar(4000)') AS Messsage , node.value('(data[@name="error_number"]/value)[1]', 'int') AS Error_Value , node.value('(data[@name="severity"]/value)[1]', 'int') AS Severity , node.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') AS SQL_Text , node.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id , node.value( '(action[@name="client_hostname"]/value)[1]' ,'varchar(128)') AS hostname , node.value( '(action[@name="client_app_name"]/value)[1]' ,'varchar(128)') AS appname , node.value('(action[@name="username"]/value)[1]', 'varchar(128)') AS username -- , #EventXML.* , DB_NAME(node.value('(action[@name="database_id"]/value)[1]', 'int')) AS database_name FROM #EventXML CROSS APPLY #EventXML.x.nodes('//event') n(node) ORDER BY event_time |
Table of Contents