How to achieve E-mail alerting when an SQL toolkit returns 0 rows

We had an interesting problem on a recent ticket where a client has a normal rule that alerts via E-mail when an SQL toolkit returns no rows. In this case its on an SQL toolkit sampler that has a set of views that will run in parallel. The problem is that when the data is initialized it reports rowsReturned = 0 despite the query not yet running. The time between the data view comes into existence and the SQL completing is an unknown. Any use of a delay in the rule will apply to all runs of that rule, even in normal operation which is not appropriate.

I was able to achieve what the client wanted by using an expected row 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>

Articles in this section

How to achieve E-mail alerting when an SQL toolkit returns 0 rows

Comments

Articles in this section

Related articles