How can we help you today? How can we help you today?

Custom Alert With Details

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.
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>
IanKeogh
0

Comments

1 comment

Sort by

Add comment

Please sign in to leave a comment.