Custom health checks

Create your own custom health checks

The Aireforge Health Checks work by running SQL against your servers, then running a small script against the SQL results to define the output shown in Aireforge. This page describes the script syntax to process your SQL results and get the results you want.

Overview

Let's assume you want to run a health check that checks to see if SQL Server is recommending 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 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 was 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 some thing if the proceeding if check failed. In this example, if the SQL returned zero, then the line following the if would be skipped, but then lines after the else would be processed instead.

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 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 the 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 colour of the Health Check result cell. This is a purely optional element but can be used to draw attention to certain result values. The colour is defined using an HTML colour code. If not used, the default text colour 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 colour of the result cell. If not specified, the default background colour 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 specify 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 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.