I'm new to Idera and trying to configure alert responses. I want the response of a given alert to be run a SQL query and then send an email with the result of the query, however I'm not able to achieve that using the alert response interface. I can only run the sql script, but I don't have any option to provide the output on the send email action.
Is there any way to achieve this on the interface?
Can you write a Powershell script to do everything you describe?
There is a Powershell Alert Response Action available.
I think you can follow the below steps to achieve this,
EX: change query according to your requeirment
if exists (select * from sysprocesses where spid<>blocked and blocked<>0 and datediff (mi,last_batch,getdate())>5)begin
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Profilename', @recipients = 'XXX@mail.com, @query = 'select * from sysprocesses where spid<>blocked and blocked<>0 and datediff (mi,last_batch,getdate())>5' , @subject = 'Blocking with last Batch time greater than 5 minutes', @body = 'Blocking with last Batch time greater than 5 minutes';
Powered by IDERA