USE SentryOne
DECLARE @tablerows nvarchar(max), @body nvarchar(max);
-- this uses XML formatting to collapse
-- the output to a string of
s
SELECT @tablerows = CONVERT(nvarchar(max),
(SELECT
td = esc.ObjectName, N''
,td = [DatabaseName], N''
,td = [LoginName], N''
,td = [SPID], N''
,td = [StartTime], N''
,td = [EndTime], N''
,td = [Error]
FROM [SentryOne].[dbo].[PerformanceAnalysisTraceData] patd
JOIN EventSourceConnection esc ON patd.EventSourceConnectionID = esc.ID
WHERE esc.ObjectName like '%AMAZONAWS%' and Error > 0
AND NormalizedStartTime > dateadd(minute, -5, getutcdate())
FOR XML PATH(N'tr'), ELEMENTS));
SET @body = N'
Timeouts detected on the following AWS instances:
Instance | Database | Login | SPID | Start Time | End Time | Error ID | '
+ @tablerows + N' ';
EXEC msdb.dbo.sp_sentry_dbmail_20
@body = @body,
@body_format = N'HTML',
@recipients = N'YOUR EMAIL ADDRESS HERE',
@subject = N'AWS Timeouts Detected'; |