 |
Geneos administrators are able to use a set of features in Gateway-SQL to build source data tables using XPaths. This FAQ is intended to introduce this feature in more detail and to show how to use XPaths, especially relative XPaths, to build these data tables and run SQL queries against them. It is assumed that the reader has a good understanding of Geneos Gateway configuration and has used the original Gateway-SQL plugin features. This is not intended to replace the documentation for the Gateway-SQL plugin. |
 |
To make things easier and to help spot errors more quickly when you first set up the Gateway SQL sampler, tick the 'Show row id' checkbox in the 'Views' to ensure each row has a unique ID, like this:
|
Gateway plugins are normally attached to a special Managed Entity that are is turn mounted on a Virtual Probe. This is because Gateway plugins provide access to data that is internal to the gateway. The Gateway-SQL plugin can operate this way and in it's original incarnation the administrator had to specify fully qualified and unique Dataviews as sources of data.
In the Gateway-SQL plugin data can also be built using the XPath mechanism which gives the administrator much more flexibility and brings in the other features like relative paths. Now placing a Gateway-SQL plugin in a Managed Entity that is associated with a normal Netprobe gives the administrators the power to refer to data in that Managed Entity and make the Sampler configuration more portable and one that can present Dataviews that are tailored to the location the plugin is deployed.
In this FAQ, we will be using the Disk Plug-In as an example. We will show how to build a plugin that summarises disk usage data across the Managed Entities on a whole gateway and then we will modify the plugin to be added to a specific Managed Entity and limit the data being shown to just that Managed Entity. While this latter step doesn't have a real world use per se there are many other applications where combining data relative to the plugin location is a benefit.
First, create a new Gateway-SQL Sampler and define the database tables:
Now, select 'xpath' as the option and click on the 'Xpath...' button to the right.
A new window will pop-up. Give the table a name which you will subsequently use in the SQL query to build the Dataview.
"Row" XPaths
Next we need to define one or more XPaths that trigger the generation each row of data in the table (remember, this is the data table that you build SQL queries against and not the Dataview).
These Row XPaths are defined in the 'Xpaths' section. Like many other parts of Geneos you can define one or more Xpaths that cumulatively may match zero to many actual Data Items. In this case the Data Items should normally be Table Cells in a single column.
|
If you use an XPath that matches all the cells in a 5x10 Dataview then you will trigger one data row per cell, making 50 rows, and not one data row per row of dataview - which is probably what you actually want. |
So, let's select every "freeSpace" cell across every DISK plugin on the Gateway using this XPath:
Paste the above into the XPath box:
Just like everywhere else in Geneos, you can use the Path Editor the change and validate the XPath, like this:
Column XPaths
'Name' will define the new column names in the database table (these do not need to be the same as the dataview column names).
Next we have to populate the source data table(s). Each column in the resulting data table is defined by a column XPath. These XPaths are normally relative to the "Row" XPaths used to create the rows in the database table. For each data row created, each Column XPath is evaluated and the resulting value is used. This means that each XPath must result in a value of some kind and all the normal relative path extractors can be used. See the Gateway Reference Guide for more.
Here we have created 6 columns, the first three are using data item properties and the last three cell data. You may notice the the freeSpace column has been selecting using an explicit path rather just the @value notation as the row XPath matches this column anyway - this is done to make it clear for future maintainers that the data is from that column and will prevent problems if someone edits the row Xpath or add another XPath that may not match that column.
So, at this point you have one data table called "Disks" that can be used to built new dataviews.
Views
4. SQL queries are used to populate the dataviews generated by the Plug-In. In this example we will use the following query:
This query will create the following dataview (once you have turned "Show row id" back off to the default):
Note: Ancestors
Ancestors can be used to anchor your dataview to be relative to where the sampler is configured. For example, to show only the disks in the Managed Entity where the sampler is attached change the "Row" XPath:
The dataview created then changes depending on the Managed Entity. This would create the following views when attaching the sampler to two different Managed Entities:
Obviously displaying data based on one single existing dataview doesn't seem very useful but here it is used purely as an example. Much more complex queries are possible.
XML for the Gateway-SQL sampler used in this FAQ:
<sampler name="Disk Summary">
<plugin>
<Gateway-sql>
<tables>
<xpath>
<tableName>
<data>Disks</data>
</tableName>
<xpaths>
<xpath>/geneos/gateway/directory/probe/managedEntity/sampler[(param("PluginName")="DISK")]/dataview/rows/row/cell[(@column="freeSpace")]</xpath>
</xpaths>
<columns>
<column>
<name>
<data>probe</data>
</name>
<xpath>ancestor::probe/@name</xpath>
<type>TEXT</type>
</column>
<column>
<name>
<data>me</data>
</name>
<xpath>ancestor::managedEntity/@name</xpath>
<type>TEXT</type>
</column>
<column>
<name>
<data>partitionName</data>
</name>
<xpath>@rowname</xpath>
<type>TEXT</type>
</column>
<column>
<name>
<data>freeSpace</data>
</name>
<xpath>../cell[(@column="freeSpace")]</xpath>
<type>TEXT</type>
</column>
<column>
<name>
<data>percentageUsed</data>
</name>
<xpath>../cell[(@column="percentageUsed")]</xpath>
<type>TEXT</type>
</column>
<column>
<name>
<data>fileSystem</data>
</name>
<xpath>../cell[(@column="fileSystem")]</xpath>
<type>TEXT</type>
</column>
</columns>
</xpath>
</tables>
<views>
<view>
<name>
<data>Disk Summary</data>
</name>
<sql>
<data> SELECT probe || ':' || partitionName as partitionName, freeSpace, percentageUsed FROM disks WHERE fileSystem IN ('rootfs', 'ext4')</data>
</sql>
</view>
</views>
</Gateway-sql>
</plugin>
</sampler>
Comments
0 comments
Please sign in to leave a comment.