Custom Alert With Details
IanKeogh
Posts: 11 Bronze 1
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 - https://sqlmonitormetrics.red-gate.com/database-drift-monitoring-unauthorized-object-changes/) 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.
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>
Tagged:
Answers
Thank you for your inquiry into SQL Monitor.
Regarding the email alerts, there is no setting in SQL Monitor to output a more detailed result within the alert email.
https://documentation.red-gate.com/sm/metrics-alerts-and-notifications/configuring-alert-notifications/setting-up-email-notification
If this is a feature you are looking to see in the future we recommend using the user voice forum to suggest this addition.
https://sqlmonitor.uservoice.com/forums/91743-suggestions
Best,
Dustin