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