XEVENT：记录对表的更新删除

 

 

--以下xevent记录了通过sqlserver查询工具直接更新，删除表数据时的记录。
IF NOT EXISTS( SELECT 1 FROM sys.dm_xe_sessions dxs(NOLOCK) WHERE name = 'EventErrorMonitor' )
BEGIN
	CREATE EVENT session EventErrorMonitor on server
	ADD EVENT sqlserver.error_reported
	(
	ACTION
	(
	sqlserver.session_id, -- SPID which raises the error
	sqlserver.plan_handle, -- Plan handle which can be used to retrieve the graphical plan
	sqlserver.tsql_stack, -- T-SQL stack for extended debugging purposes
	package0.callstack, -- Callstack for extended debugging purposes
	sqlserver.sql_text, -- T-SQL query which encountered the error
	sqlserver.username, -- Name of the user that reported the error
	sqlserver.client_app_name, -- Client application name
	sqlserver.client_hostname, -- Host which initiated the query
	sqlserver.database_name -- Database against which the query was being executed
	)
	 WHERE severity >= 11 AND Severity <=16
	)
	ADD TARGET package0.ring_buffer
	WITH (max_dispatch_latency=1seconds)

	ALTER EVENT SESSION EventErrorMonitor on server state = START

END
ELSE
BEGIN

	SELECT
		 DATEADD(hh,
				DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
				n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
		n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS [database_name],
		n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
		n.value('(event/data[@name="message"]/value)[1]', 'nvarchar(max)') AS [message],
		n.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [username],
		n.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
		n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS [client_app_name],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@handle)[1]', 'varchar(max)') AS [tsql_stack],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetStart)[1]', 'int') AS [statement_start_offset],
		n.value('(event/action[@name="tsql_stack"]/value/frames/frame/@offsetEnd)[1]', 'int') AS [statement_end_offset]
	into #ErrorMonitor
	FROM
	(    SELECT td.query('.') as n
		FROM
		(
			SELECT CAST(target_data AS XML) as target_data
			FROM sys.dm_xe_sessions AS s
			JOIN sys.dm_xe_session_targets AS t
				ON t.event_session_address = s.address
			WHERE s.name = 'EventErrorMonitor'
			  --AND t.target_name = 'ring_buffer'
		) AS sub
		CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
	) as TAB

	INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
	SELECT TIMESTAMP,database_name,[message],sql_text,'',username,client_hostname,client_app_name
	FROM #ErrorMonitor a
	WHERE a.sql_text != '' AND client_app_name !='Microsoft SQL Server Management Studio - 查询'

	INSERT INTO PerfLogicMessage(StartTime,database_name,message,sql_text,alltext,username,client_hostname,client_app_name)
	--查看应用执行的
	SELECT  TIMESTAMP,database_name,[message],
	SUBSTRING(qt.text,a.statement_start_offset/2+1,
				(case when a.statement_end_offset = -1
				then DATALENGTH(qt.text)
				else a.statement_end_offset end -a.statement_start_offset)/2 + 1) sql_text,qt.text alltext,
	username,client_hostname,client_app_name
	FROM #ErrorMonitor a
	CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(max),a.tsql_stack,1) )  qt
	WHERE a.sql_text IS NULL AND tsql_stack != '' AND client_app_name = '.Net SqlClient Data Provider'

	DROP TABLE #ErrorMonitor

	--重启以清空
	ALTER EVENT SESSION EventErrorMonitor ON SERVER STATE = STOP
	ALTER EVENT SESSION EventErrorMonitor on server state = START
END