Hi, So What I need and I am trying to figure out is this. I want a custom alert if someone has modified an object. I have the extended event running (I "borrowed" the one from this custom alert -
and it will fire when the count is greater than one. But what i really need is when the email alert is sent I need it to include the details. I have the query for this (see below) but how do i get it to include he details and not just tell me the alert has fired.
Thanks in advance.
Ian.
DROP TABLE IF EXISTS #t;
;WITH cte AS
( SELECT ed = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets xet
INNER JOIN sys.dm_xe_sessions xe ON
xe.[address] = xet.event_session_address
WHERE xe.name = N'WhoChangedWhat'
AND xet.target_name = N'ring_buffer')
SELECT event_data = x.ed.query('.')
INTO #t
FROM cte
CROSS APPLY cte.ed.nodes(N'RingBufferTarget/event') AS x(ed);
SELECT CONVERT(
DATETIME2, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, event_data.value(N'(event/@timestamp)[1]', N'datetime')), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS AlertDate
, [Alter_Type] = event_data.value(N'(/event/@name)[1]', N'nvarchar(100)')
, [UserName] = event_data.value(N'(event/action[@name="server_principal_name"]/value)[1]', N'nvarchar(100)')
, [ClientApp] = event_data.value(N'(event/action[@name="client_app_name"]/value)[1]', N'nvarchar(100)')
, [Database_Name] = event_data.value(N'(event/data[@name="database_name"]/value)[1]', N'nvarchar(100)')
, [Object_Name] = event_data.value(N'(event/data[@name="object_name"]/value)[1]', N'nvarchar(100)')
, [TSQL] = event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)')
FROM #t
WHERE DATEDIFF(
MINUTE
, CONVERT(
DATETIME2
, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, event_data.value(N'(event/@timestamp)[1]', N'datetime')), DATENAME(TZOFFSET, SYSDATETIMEOFFSET())))
, GETDATE()) < 30<br>
Hi Ian,
Thanks in advance.
Ian.
DROP TABLE IF EXISTS #t; ;WITH cte AS ( SELECT ed = CONVERT(XML, target_data) FROM sys.dm_xe_session_targets xet INNER JOIN sys.dm_xe_sessions xe ON xe.[address] = xet.event_session_address WHERE xe.name = N'WhoChangedWhat' AND xet.target_name = N'ring_buffer') SELECT event_data = x.ed.query('.') INTO #t FROM cte CROSS APPLY cte.ed.nodes(N'RingBufferTarget/event') AS x(ed); SELECT CONVERT( DATETIME2, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, event_data.value(N'(event/@timestamp)[1]', N'datetime')), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS AlertDate , [Alter_Type] = event_data.value(N'(/event/@name)[1]', N'nvarchar(100)') , [UserName] = event_data.value(N'(event/action[@name="server_principal_name"]/value)[1]', N'nvarchar(100)') , [ClientApp] = event_data.value(N'(event/action[@name="client_app_name"]/value)[1]', N'nvarchar(100)') , [Database_Name] = event_data.value(N'(event/data[@name="database_name"]/value)[1]', N'nvarchar(100)') , [Object_Name] = event_data.value(N'(event/data[@name="object_name"]/value)[1]', N'nvarchar(100)') , [TSQL] = event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)') FROM #t WHERE DATEDIFF( MINUTE , CONVERT( DATETIME2 , SWITCHOFFSET(CONVERT(DATETIMEOFFSET, event_data.value(N'(event/@timestamp)[1]', N'datetime')), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) , GETDATE()) < 30<br>