Geneos - GatewaySQL Example - Specific Value and Dynamic Rows comparison to alert if value not the same on either row

Example and Requirements:

1. The static data (192.XXX.0.78) should be monitored even when the row has changed.
2. The static data (192.XXX.0.78) should be present on either row of the value column. Hence, giving us an ok severity if present and critical if not.
    

Here we have used this data as an example:

 
For us to segregate these values, we have configured the Gateway-SQL to obtain the data coming from both columns:

Following this example should give you something like this. this way, the data should still be detected even if it's present on different rows.


 
Now, we will have a rule that should compare these two values, when the data is not present in the second column, it should trigger a critical severity:


 
As an example, here we have removed the highlighted ip on the 2nd column:

 
This should then trigger a critical severity given that the data is not available on the 2nd column:

 
To also check to see if this can be detected even with a different row:

Gateway SQL Sampler

<sampler name="testinggatewaysql">
<sampleInterval>
<data>20</data>
</sampleInterval>
<plugin>
<Gateway-sql>
<tables>
<dataview>
<tableName>
<data>timetoolkit</data>
</tableName>
<xpath>/geneos/gateway[(@name=&quot;Primary Gateway&quot;)]/directory/probe[(@name=&quot;1_probe&quot;)]/managedEntity[(@name=&quot;TESTS&quot;)]/sampler[(@name=&quot;toolkit-time&quot;)][(@type=&quot;&quot;)]/dataview</xpath>
<columns>
<column>
<name>
<data>time</data>
</name>
<type>TEXT</type>
</column>
<column>
<name>
<data>name</data>
</name>
<type>INTEGER</type>
</column>
<column>
<name>
<data>Value</data>
</name>
<type>INTEGER</type>
</column>
</columns>
</dataview>
</tables>
<views>
<view>
<name>
<data>toolkit</data>
</name>
<sql>
<data>SELECT time,name FROM timetoolkit WHERE name = &apos;192.XXX.0.78&apos;;</data>
</sql>
</view>
<view>
<name>
<data>value</data>
</name>
<sql>
<data>SELECT time,Value FROM timetoolkit WHERE Value = &apos;192.XXX.0.78&apos;;</data>
</sql>
</view>
</views>
</Gateway-sql>
</plugin>
</sampler>

Rule XML

<rule name="New Rule 1">
<targets>
<target>/geneos/gateway[(@name=&quot;Primary Gateway&quot;)]/directory/probe[(@name=&quot;1_probe&quot;)]/managedEntity[(@name=&quot;Main&quot;)]/sampler[(@name=&quot;testinggatewaysql&quot;)][(@type=&quot;&quot;)]/dataview[(@name=&quot;toolkit&quot;)]/rows/row/cell[(@column=&quot;name&quot;)]</target>
</targets>
<priority>1</priority>
<pathAliases>
<pathAlias name="outboundcolumn">/geneos/gateway[(@name=&quot;Primary Gateway&quot;)]/directory/probe[(@name=&quot;1_probe&quot;)]/managedEntity[(@name=&quot;Main&quot;)]/sampler[(@name=&quot;testinggatewaysql&quot;)][(@type=&quot;&quot;)]/dataview[(@name=&quot;value&quot;)]/rows/row/cell[(@column=&quot;Value&quot;)]</pathAlias>
</pathAliases>
<block>
<set>
<var ref="outbound"></var>
<dataItem>
<pathAlias ref="outboundcolumn"></pathAlias>
<property>@value</property>
</dataItem>
</set>
<if>
<equal>
<dataItem>
<property>@value</property>
</dataItem>
<var ref="outbound"></var>
</equal>
<transaction>
<update>
<property>state/@severity</property>
<severity>ok</severity>
</update>
</transaction>
<transaction>
<update>
<property>state/@severity</property>
<severity>critical</severity>
</update>
</transaction>
</if>
</block>
</rule>

Toolkit Sampler

<sampler name="toolkit-time">
<plugin>
<toolkit>
<samplerScript>
<data>/home/MNL/lbasinal/geneos/gateway/test_sql/toolkit_time.bash</data>
</samplerScript>
</toolkit>
</plugin>
<dataviews>
<dataview name="test">
<additions>
<var-headlines>
<data>
<headline>
<data>rowCount1</data>
</headline>
</data>
</var-headlines>
</additions>
</dataview>
</dataviews>
</sampler>

Bashscript for Toolkit

#!/bin/bash
echo time,name,Value
echo 00:01:00,192.111.0.71,192.111.0.70
echo 00:06:00,192.111.0.70,192.111.0.71
echo 00:02:00,192.111.0.73,192.111.0.73
echo 00:04:00,192.111.0.74,192.111.0.74
echo 00:05:55,192.111.0.78,192.111.0.77
echo 00:05:26,192.111.0.72,192.111.0.72

If you have any further questions:

• Please contact with our Client Services team via the chat service box available in any of our websites or via email to support@itrsgroup.com
  
   
• Make sure you provide to us:
  • ANY LOG FILE OR DIAGNOSTIC
  • ANY SCREENSHOT