multi numeric alert - how to pull out values in email

Question

hello support,

I am testing "Custom SQL Alert - Multiple Numeric Return" with following SQL

SELECT
CONVERT(VARCHAR,a.session_id) AS session_id, CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) AS duration_in_seconds
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
AND a.database_id <> 1
AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 50

It output session_id and its duration_in_seconds. When i test this alert with my current email template, it does not return me these values in email

this is my current template of email

Alerting At: [=alert.executionTime]
Alert Type: [=alert.type]
Alert Status: [=alert.status.value]
Alerting For: [=database.name]

What variable should I use to take this output out in email?

Thanks

sukhjinder.bhullar · Answer

this is how my query looks now

SELECT
CONCAT(
'Session ID: ', CAST(s.session_id AS VARCHAR(20)),
', Login: ', s.login_name,
', Database: ', d.name,
', Plan Handle: ', CONVERT(NVARCHAR(64), r.plan_handle, 2), ' running for '
) AS session_description,
DATEDIFF(SECOND, r.start_time, GETDATE()) AS execution_seconds
FROM
sys.dm_exec_requests r
JOIN
sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN
sys.databases d ON r.database_id = d.database_id
WHERE
r.status <> 'background'
AND r.database_id <> 1
AND DATEDIFF(SECOND, r.start_time, GETDATE()) > 300

multi numeric alert - how to pull out values in email

Top Replies