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

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 

<rule name="No rows and sampler has returned value">
    <targets>
        <target>/geneos/gateway[(@name="Support Services")]/directory/probe[(@name="supportServices_probe002")]/managedEntity[(@name="PEBL Quality Issues")]/sampler[(@name="PEBL Quality issues")][(@type="PEBL Quality Issues")]/dataview[(@name="PEBL Quality Issues")]/headlines/cell[(@name="rowsReturned")]</target>
    </targets>
    <priority>1</priority>
    <pathAliases>
        <pathAlias name="runcomplete">../../rows/row/cell[(@value="")][(@column="Ticket")]</pathAlias>
    </pathAliases>
    <block>
        <set>
            <var ref="runcomplete"></var>
            <count>
                <dataItems>
                    <pathAlias ref="runcomplete"></pathAlias>
                    <property>state/@severity</property>
                </dataItems>
            </count>
        </set>
        <if>
            <and>
                <gt>
                    <var ref="runcomplete"></var>
                    <integer>0</integer>
                </gt>
                <equal>
                    <dataItem>
                        <property>@value</property>
                    </dataItem>
                    <integer>0</integer>
                </equal>
            </and>
            <transaction>
                <update>
                    <property>state/@severity</property>
                    <severity>warning</severity>
                </update>
            </transaction>
        </if>
        <transaction>
            <update>
                <property>state/@severity</property>
                <severity>ok</severity>
            </update>
        </transaction>
    </block>
</rule>