Developing custom checks

General
1

Is there any plans to support custom checks? For instance I would like to ping remote servers or check linked servers were up, is this something we can do/develop now?

2

Hi,

Custom checks are already supported. You can add any check you like to the [dbo].[sqlwatch_config_check] table. The default checks that SQLWATCH comes with have negative IDs, if you add a new check yourself, it will go in with a positive ID. This way they will not be replaced during an upgrade.

If you wanted to ping a SQL Server over a linked server, you could add a check with the following query:

select @output=select top 1 1 FROM [LINKED_SERVER].master.sys.servers

And have the critical threshold set to <>1 so it will alert if the @output is not 1.
If you have 10 Linked Servers you are going to need 10 checks though.

One thing to keep in mind is that checks are single-threaded and synchronous. This means that if the remote server is offline, it may take some time before it times out and fails, in which case, no other check will run.

A workaround would be to use the reports functionality, build a report that would ping all servers on the list. This can be scheduled independently by its own job using the [report_batch_id] in [dbo].[sqlwatch_config_report] table. The report will only proceed to action if there is output so you would have to construct your query to not produce output if all servers are online.

For reference, have a look at the Notifications section in the docs: https://docs.sqlwatch.io/v/3.0/sqlwatch-database/notifications

3

Thinking about a bit more, if the @output evaluates to NULL you are going to get a warning that the check must not be null. It would have to evaluate to <> 1. for example 0:

I have tested the below and it works well:

BEGIN TRY
  EXEC master.sys.sp_executesql N'EXEC sp_testlinkedserver N''SQLWATCH-TEST-1X'';';
  SELECT @output = 1
END TRY
BEGIN CATCH
	SELECT @output = 0
END CATCH

SELECT @output

Just put in the check_query field in [dbo].[sqlwatch_config_check]

4

Thanks Marcin! I’ll give that a whirl :slight_smile:

1 Like
5

Hi @Marcin, just need a little help creating a custom check if you could please.

I’m looking to create a check that sends a Microsoft Teams notification when blocked transactions and Collected SPIDs go over a specified value. Every now and again these values go over 100 and I’d like to setup an alert.

whoIsActive
whoIsActive847×74 10.4 KB

The “Blocked Sessions” and “Collected Spids” values are on the “WhoIsActive” so is there a query I could run against the “sqlwatch_logger_whoisactive” table to get these as they are calulated values?

Thanks in advance

Blocked

6

Does the built-in check does not work for? You could certainly use it as start.

check_id check_name
-2 Blocked Process
7

Thanks @Marcin, I’ll try this one to start then see if it can be modified.

1 Like
10

You don’t have to modify a lot, just change the threshold as you wish.