I am setting up Notifications. From reading the documentation my understanding was that I would need to associate an action, to a check. i.e From the “[dbo].[sqlwatch_config_check]” table, check_id -1 Check_name is “Failed Agent Job” And From the “[dbo].[sqlwatch_config_action]” Table “action_id” -2 is “Send Email to DBAs using sp_send_mail”
So looking at this page: https://docs.sqlwatch.io/sqlwatch-database/notifications/checks
and the diagram of the tables. I thought i would have to add a record to associate an action (Send an email) to the Check (Failed agent Job). So I added this line to the “[dbo].[sqlwatch_config_check_action]” Table
-1 -2 1 1 900 6 -1 2020-07-04 14:26:46.403 NULL
I did also change the “action_exec” column in the “sqlwatch_config_action” table to enter my email address and made sure the profile works.
Executing the below does successfully send an email.(obviously with my email address in).
You shouldn’t need to do anything apart form setting up your email and enabling the action. You can check this view to see the association and all parameters:
SELECT *
FROM [dbo].[vw_sqlwatch_report_config_check_action]
WHERE check_id = -1
By default, the “Failed Agent Job” runs action_id -7 which is a report, to pull more information about the failed job, rather than just a simple message “job has failed but I do not know which job”
Then the report, as you say, will trigger action -2.
Once the “gathering” is completed, it will go into the queue table: [dbo].[sqlwatch_meta_action_queue] from which it will be sent out.
If you aren’t getting records in this table it means actions are not being processed. If you are getting records in this table but they are not being sent, it will means there are problems in further down the line.
Also please check the app_log table for any errors or warnings.
Ok so looking at the “vw_sqlwatch_report_config_check_action” table i can see that the “report_action_description” is using the “Send Email to DBAs using sp_send_mail (HTML)” value. And i had not configured that for my email address in the [dbo].[sqlwatch_config_action] table. I had only done it without the (HTML).
I have now done and tested successfully by executing the line.
But there is nothing in my [sqlwatch_meta_action_queue] table. So actions are not being processed? just looking at that now. is there something, a job for instance that i need to kick off to refresh?
The job SQLWATCH-INTERNAL-CHECKS runs every minute but checks are processed according to the [check_frequency_minutes] in [dbo].[sqlwatch_config_check]. The check_id =1 is by default NULL which means it will process every time the job runs. Once the job run and has processed all checks, it would have generated record in the queue table which is processed by another job SQLWATCH-INTERNAL-ACTIONS
If you are still not getting anything, enable INFO logging in [dbo].[sqlwatch_config]
update [dbo].[sqlwatch_config]
set [config_value] = 1 where [config_id] = 1
and observe entries in [dbo].[sqlwatch_app_log]. It should tell you what is going on.
Also, make sure you the job has failed recently otherwise it will not get picked up, only recently failed jobs get picked up i.e. since the last check run.
If we look at the check_query you will see the criteria to only pick jobs that have failed since last check run {LAST_CHECK_DATE}
select @output=count(*)
from msdb.dbo.sysjobhistory
where DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), msdb.dbo.agent_datetime(run_date, run_time)) >= dateadd(second,-1,'{LAST_CHECK_DATE}')
and run_status = 0
and step_id <> 0
Sounds good, I’m going to be looking at the “dbachecks” integration as well. so expect to hear from me again at some point in the not too distant future
