Gateway - How do I use multiple data sources in gateway SQL
Use the xpath as source table.
If you have multiple sources (dataviews) for gateway sql and want to combine them into a single dataview via gateway SQL, you can use the Xpath table. For example, you have 2 CPU samplers belonging to 2 different Managed entities and you want to display the percentUtilisation column only of these 2 samplers in gateway SQL dataview.
Here are the steps:
In this example, my gateway name is GATEWAY\_9370, you will need to change this xpath to match your gateway name.
- Setup the Xpath.
/geneos/gateway[(@name="GATEWAY\_9370")]/directory/probe/managedEntity/sampler[(@name="CPU")][(@type="")]/dataview[(@name="CPU")]/rows/row/cell[(@column="percentUtilisation")]
This will evaluate to 2 CPU samplers. In my example, my mananged entities are named LAWIN and LAWIN32.
- Define the columns you need. For this example, we define the Managed entity name, rowname, and perecntUtilisation.
- Query from the source table CPU.
- Resulting dataview will be the following:
- If you want to show the values from LAWIN managed entity only, then you can filter using sql query:
select * from CPU where "ME Name" = 'LAWIN'
The result will be the following:
Note
If the resulting dataview have duplicate rows, tick on the Show row id option. If the column name have spaces and you want to use it in a query, use double quotes ("").
Refer to the XML used:
<sampler name="Gateway SQL Example">
<plugin>
<Gateway-sql>
<tables>
<xpath>
<tableName>
<data>CPU</data>
</tableName>
<xpaths>
<xpath>/geneos/gateway[(@name="GATEWAY_9370")]/directory/probe/managedEntity[contains(@name,"LAWIN")]/sampler[(@name="CPU")][(@type="")]/dataview[(@name="CPU")]/rows/row/cell[(@column="percentUtilisation")]</xpath>
</xpaths>
<columns>
<column>
<name>
<data>ME Name</data>
</name>
<xpath>ancestor-or-self::managedEntity/@name</xpath>
</column>
<column>
<name>
<data>CPU</data>
</name>
<xpath>@rowname</xpath>
</column>
<column>
<name>
<data>% util</data>
</name>
<xpath>@value</xpath>
</column>
</columns>
</xpath>
</tables>
<views>
<view>
<name>
<data>Combined CPU</data>
</name>
<sql>
<data>select * from CPU</data>
</sql>
<showRowID>true</showRowID>
</view>
</views>
</Gateway-sql>
</plugin>
</sampler>
For more information, see Gateway SQL Reference Guide.