How To Perform Custom SQL Server Health Checks

Aireforge Monitor Checks work by running SQL Scripts against your servers, then running a small query against the results to define the output shown in Aireforge. This page describes the script syntax to execute and process your results from a multi-server database review.

Overview

Let's assume you want to run a SQL Server health check script that checks to see if SQL Server recommends any missing indexes.

You'd enter a SQL script looking something like this:

SELECT COUNT(1)
FROM sys.dm_db_missing_index_details;

This SQL will return the number of missing indexes that SQL Server is suggesting, zero if none. To make use of the result of running this SQL, you could enter a very simple output script something like this:

text = {col:0}
tooltip = There are {col:0} missing indexes
if {col:0} > 0
    status = Warning
else
    status = OK
end

Going through this script, line by line:

text = (col:0)

Lines starting text = assign the main Health Check result text that is displayed when viewing the check results. And {col:0} means "the first column in the first row of the first result set", which is the count of missing indexes in this example, so we're telling it to display the count of missing indexes in the Health Check result.

tooltip = There are {col:0} missing indexes

Lines starting tooltip = provide extra information that's shown in a tooltip over the Health Check result. As above, the {col:0} is telling it to substitute the value of the first column of the first row of the first result set in the text.

if (col:0) > 0

Lines starting if are testing some condition about the results. And this value is being tested to see if it's greater than zero. If the check passes (i.e., the count returned by the SQL is greater than zero), then the following lines are processed. If the check doesn't pass (i.e., zero returned by the SQL), then the following lines are skipped.

status = Warning

Lines starting status = assign the Health Check result to a general-purpose status. The Health Check results are grouped by status, so this provides a quick high-level way of knowing whether a Health Check result is good, bad, or in between. In this case, if the SQL returns a count greater than zero, we're saying that we want this Health Check to be classified as a "warning".

else

Lines with else allow you to do something if the proceeding if the check failed. In this example, if the SQL returned zero, the line following the if would be skipped, but lines after the else would be processed.

status = OK

This line sets the status to "OK" because if the SQL returns a count of zero missing indexes, then everything is OK.

end

Lines with end must be used to end an if or if..else block. This indicates that the lines following this line should be processed.

That covers every line of our sample SQL Health Check, which should give you an idea of how to start writing your own. The following sections go into more detail on the script syntax.

Status

The status keyword assigns the following text to the status of the Health Check, which is used as a high-level classification of the Health Check result. Aireforge groups Health Checks by their status and presents that as the summary of the Health Check results. You can use as many or as few statuses as you want, having just simple "Pass" or "Fail" status, or "Green", "Amber", "Red" or anything you want!

Usage:

status = <status value>

E.g.

status = OK    
status = Warning    
status = A-OK!

Text

The text keyword assigns the following text to the Health Check result. The Health Checks are grouped by their status (see above section), but clicking into the grouped results will show each Health Check separately, where this keyword is used to define what's shown in the grid. You can use = to simply assign the following text to the Health Check result, or you can use += to append the following text to the result text so far.

Usage:

text = <text>    
text += <text>

E.g.

text = your text here    
text += adding some more text

Tooltip

The tooltip keyword assigns the following text to the tooltips shown over the Health Check result. This element is purely optional but can be used to provide more information or explanation about what a Health Check result means. As with the "text" keyword, you can use = to assign the tooltip and += to append to any tooltip you've already set.

Usage:

tooltip = <text>    
tooltip += <text>

E.g.

tooltip = your text here    
tooltip += adding some more text

Foreground

The foreground keyword sets the text color of the Health Check result cell. This is a purely optional element but can be used to draw attention to specific result values. The color is defined using an HTML color code. If not used, the default text color of black is used.

Usage:

foreground = <HTML colour>

E.g.

foreground = #000000

Background

The background keyword works in the same manner as the foreground keyword, except controls the background color of the result cell. If not specified, the default background color of white is used.

Usage:

foreground = <HTML colour>

E.g.

foreground = #ffffff

If..else...end

You can perform tests on your SQL script results using if statements. If the logic following if evaluates to be true, then the following lines are processed. If the logic evaluates to false, then the following lines are not processed. Construction of the test following the if uses the operators >, >=, <, <=, and =. You can also perform multiple tests and combine the results using && and ||.

As well as using the else statement to specify an alternative path if the if evaluated to false, you can use else if lines to determine alternative tests.

All if blocks, no matter whether using else or else if must end with an end keyword.

Usage:

if <test>
    ....do something
end

if <test>
    ....do something
else
    ...do something else
end

if <test>
    ....do something
else if <second test>
    ...so something else
else
    ...do something else
end

E.g.

if {col:0} > 0 
    status = Warning 
end

if {col:0} > 0
    status = Warning
else
    status = OK
end

if {col:0} > 10
    status = Severe Warning
else if {col:0} > 0
    status = Warning
else
    status = OK
end

Referencing SQL Results

In the above examples, we've seen that using {col:0} refers to the first column in the first row of the first result set. This is a shorter way of writing the fuller version of the syntax which is:

{result:x,row:y,col:z}

The number after the result indicates which result set, the number after row indicates the row, and the number after col indicates the column. All numbers start from zero being the first result, row, or column. If either the result or row elements are missed out, then zero is assumed for them.