usp_sqlwatch_internal_process_actions - @last_action_time (snapshot_time) in UTC, compared against non-UTC GETDATE()

[seanwhitent]

Describe the bug
@last_action_time, which comes from the most recent snapshot_value in [dbo].[sqlwatch_logger_check_action] for an action, is stored in UTC time. In the stored procedure usp_sqlwatch_internal_process_actions, to determine if it should retrigger the action, compares this UTC time against the (potentially) non-UTC time of GETDATE() in lines 169 and 170 in the datediff:

when @check_status <> 'OK' and last_check_status = @check_status and (@action_repeat_period_minutes is not null and datediff(minute,isnull(@last_action_time,'1970-01-01'),getdate()) > @action_repeat_period_minutes) then 'REPEAT'

For my time zone in UTC-7, the datediff returns a negative integer. This means that even though action_repeat_period_minutes is set to 1, action_hourly_limit is set to 60, and action_every_failure is set to 1, it will not repeat the action every minute.

Expected behavior
A row in [sqlwatch_config_check_action] with action_repeat_period_minutes is set to 1, action_hourly_limit is set to 60, and action_every_failure is set to 1 will have its associated action trigger every minute when the corresponding check is not "OK".

Screenshots

Windows Server (please complete the following information):

• OS Version: Windows 10 Pro

SQL Server (please complete the following information):

• SQL Version: SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
• SQL Edition: Developer

SQL Server Management Studio (SSMS -> about -> copy info):

• paste "about" info here:
  SQL Server Management Studio 15.0.18410.0
  SQL Server Management Objects (SMO) 16.100.47008.0+9f71e8549924d85d66afcca2b9f45a33061faa1b
  Microsoft Analysis Services Client Tools 15.0.19750.0
  Microsoft Data Access Components (MDAC) 10.0.19041.1
  Microsoft MSXML 3.0 6.0
  Microsoft .NET Framework 4.0.30319.42000
  Operating System 10.0.19044

SQLWATCH version (from DACPAC or from sysinstances)

• 4.2.0.28234

[marcingminski]

Hi, did you try version 4.3? And if yes - does the problem still exist?