Alert response - SQL Script and email

Hello,

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? 

Parents
No Data
Reply
  • Hi Lucas,

    I think you can follow the below steps to achieve this,

    •  Create the job with including the SQL query with DB mail configuration.

    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';


    Print 'Email'
    end

    •  create the alert and response with alert.
    • run the job when an alert occurs 

    Thanks 

Children
No Data