How to generate an alert if an SQL-Toolkit returns 0 rows

We had an user enquiry that wanted to generate an alert when an SQL-Toolkit output is empty. They have a number of SQL queries, and the time to finish can vary for each one. The problem is that when the data is initialized it reports rowsReturned = 0 despite the query not yet running. Any use of a delay in the rule will apply to all runs of that rule, even in normal operation which is not appropriate.

We had come up with a solution adding an expected row option in the sampler configuration. I added an ‘Expect row’ in the advanced configuration, meaning that when the data view is first created there is a row present with just one column (availability). the cell value is ‘absent’, but this is not so interesting. What IS important is that the columns that will be returned as a result of the SQL query is are not present, and will not be present until it has completed.

Because of this last point we can now write a rule that looks for one or more of the columns we expect to be present post run of the SQL, and where the row count is 0. The expected row itself if NOT counted in the rowsReturned value, so we can still test for the 0 condition.

set $(runcomplete) count(wpath “runcomplete” severity) if $(runcomplete) > 0 and value = 0 then

severity warning endif severity ok or the rule XML so you can see the path variables