For example, https://sqlmonitormetrics.red-gate.com/suspiciouserrors/ (Detect Suspicious Errors Typical of a SQL Injection Attack)...

If suspicious activity crosses a threshold, it throws an alert. Great. Now what? I have an alert but no data to investigate without opening SSMS (or tool of choice) and running a query like this (I’m sure there are better ways but this works for an example)

SELECT CAST(targetdata asxml) AS targetdata
INTO #SuspiciousErrors
FROM sys.dmxesessiontargets xet
JOIN sys.dmxesessions xes ON xes.address=xet.eventsessionaddress
WHERE xes.name='MonitorSuspiciousErrors'
AND xet.targetname ='ringbuffer';

SELECT
dateadd(mi,datediff(mi,getutcdate(),getdate()),xed.eventdata.value('(@timestamp)[1]','datetime2'))AS EventTime
, xed.eventdata.value('(data[@name="errornumber"]/value)[1]','int')AS ErrorNum
, xed.eventdata.value('(data[@name="message"]/value)[1]','varchar(400)')AS ErrorMessage
, xed.eventdata.value('(action[@name="username"]/value)[1]','varchar(400)')AS UserName
, xed.eventdata.value('(action[@name="ntusername"]/value)[1]','varchar(400)')AS NTUserName
, xed.eventdata.value('(action[@name="databasename"]/value)[1]','varchar(400)')AS DatabaseName
, xed.eventdata.value('(action[@name="sqltext"]/value)[1]','nvarchar(max)')AS sqlText
, xed.eventdata.value('(action[@name="clientappname"]/value)[1]','nvarchar(125)')AS sqlText
FROM #SuspiciousErrors
CROSSAPPLY targetdata.nodes('//RingBufferTarget/event') AS xed(eventdata)
WHEREDateDiff
(MINUTE,Convert
(DATETIME2,
SwitchOffset(
Convert(DATETIMEOFFSET,xed.event_data.value('(@timestamp)[1]','datetime2')
),
DateName(TzOffset,SysDateTimeOffset())
)
),
GetDate()
)<20;

DROPTABLE #SuspiciousErrors;

It would be great if there was a way to capture actual data supporting the alert trigger so that it could be viewed with the alert details. There are a myriad of other alerts (alerts firing off the default trace (e.g. failed logins), alerts that could be triggered from the system_health extended event (e.g. tempdb contention)…).

With an optional secondary data gathering option, proper details can be included with the alert in instances like the above.

Even listing examples would be helpful for something like this where users may not be good (like myself) at shredding or able to shred XML.

You do this for some alerts.

https://sqlmonitormetrics.red-gate.com/monitor-tempdb-contention/
&
https://www.red-gate.com/hub/product-learning/sql-monitor/monitoring-tempdb-contention-using-extended-events-and-sql-monitor