Articles in this section

See more
Self help
How To
["How To"]

Geneos - Gateway - How do I use multiple data sources in gateway SQL

Avatar
ITRS Support
  • Updated
Follow

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 my example my gateway name is GATEWAY_9370 you will need to change this xpath to match your gateway name.

  1. Setup the Xpath to be:  /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.
  2. Define the columns you need. For this example, we define the Managed entity name, rowname, and perecntUtilisation.
  3. Simply query from the source table "CPU"
  4. Resulting dataview will be:
  5. 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' and the result will be:
  • If the resulting dataview will 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 ("")
  • XML segment included. 
    <sampler name="Gateway SQL Example">
    <plugin>
    <Gateway-sql>
    <tables>
    <xpath>
    <tableName>
    <data>CPU</data>
    </tableName>
    <xpaths>
    <xpath>/geneos/gateway[(@name=&quot;GATEWAY_9370&quot;)]/directory/probe/managedEntity[contains(@name,&quot;LAWIN&quot;)]/sampler[(@name=&quot;CPU&quot;)][(@type=&quot;&quot;)]/dataview[(@name=&quot;CPU&quot;)]/rows/row/cell[(@column=&quot;percentUtilisation&quot;)]</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>

 

 

Further Reading

Please refer to the GatewaySQL reference guide for additional informaiton - Gateway SQL Reference Guide.

Related articles

Comments

0 comments

Please sign in to leave a comment.