SQL Query Plugin

SQL Query Plugin

The majority of HQ's built-in database plugins use SQL queries to collect the metrics provided by each server and service type. The support classes for collecting these metrics can be used to create custom plugins that collect metrics using SQL queries defined by the plugin author.

Connection Configuration

All SQL query plugins require configuration properties for connecting to the database. These properties are:

• Database driver

• JDBC URL

• Username

• Password (optional in some cases)

Supported drivers and corresponding JDBC URL syntax:

The SQL configuration schema can be imported by any plugin using the following snippet:

<config include="sql"/>

This config is defined by the sqlquery plugin as follows:

<config>
  <option name="jdbcDriver" type="enum"
          description="JDBC Driver Class Name">

    <include name="org.postgresql.Driver"/>
    <include name="oracle.jdbc.driver.OracleDriver"/>
    <include name="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
    <include name="com.mysql.jdbc.Driver"/>
  </option>

  <option name="jdbcUrl"
          description="JDBC Connection URL"/>
          default="jdbc:postgresql://localhost:9432/hqdb"/>

  <option name="jdbcUser"
          description="JDBC User"/>

  <option name="jdbcPassword"
          type="secret"
          optional="true"
          description="JDBC Password"/>
</config>

Your custom plugin is free to define this config schema rather than including it, should you wish to change the descriptions or defaults.
Only the name attributes must remain the same for the SQL Query plugin to create the database connection.
A custom plugin can also override the default values using the property tag and any of the name attributes defined in the config schema:

<property name="jdbcUrl"
          value="jdbc:oracle:thin:@localhost:1521:ORA1"/>

<property name="jdbcDriver"
          value="oracle.jdbc.driver.OracleDriver"/>

<property name="jdbcUser"
          value="orauser"/>

<property name="jdbcPassword"
          value="mypass"/>

<config include="sql"/>

Changing the default configuration properties to:

Overriding the defaults will make it easier to configure your custom service and in the future could allow for auto-discovery.

Example

For the example, we'll use a plugin included in the HQ agent distribution which you'll find at: pdk/examples/hq-inventory-plugin.xml.
This plugin uses simple SQL queries to collect Availability and counters for the number of Platforms, Servers and Services currently
in the server's inventory. First, the plugin defines the service type:

<plugin>
  <service name="HQ Inventory">
  ...
  </service>
</plugin>

Next, the required SQL configuration schema:

<config include="sql"/>

A filter is used to define the metric template:

<filter name="template" value="sql:${query}:${name}"/>

• sql - Metric domain routes collection to the SQL query plugin
• ${query} - The query to execute, defined by each metric tag
• ${name} - The name of the metric being collected, defined by each metric tag

Another filter is used to for part of the syntax common to each query of this plugin:

<filter name="count" value="SELECT COUNT(*) FROM"/>

Now the plugin can start defining queries to be collected. The Availability metric is a special case where we want 1 to be returned as the value,
appending WHERE 1=1 to accomplish that:

<metric name="Availability"
        query="${count} EAM_CONFIG_PROPS WHERE 1=1"
        indicator="true"/>

The rest of the metrics use queries that just count the number of rows in each table:

<metric name="Number of Platforms"
        query="${count} EAM_PLATFORM"
        indicator="true"/>

<metric name="Number of Servers"
        query="${count} EAM_SERVER"
        indicator="true"/>

<metric name="Number of Services"
        query="${count} EAM_SERVICE"
        indicator="true"/>

Command Line Test

% java -jar pdk/lib/hq-product.jar -Dplugins.include=hq-inventory -DjdbcDriver=org.postgresql.Driver -DjdbcUrl=jdbc:postgresql://localhost:9432/hqdb -DjdbcUser=hqadmin -DjdbcPassword=hqadmin
HQ Inventory Availability:
   HQ Inventory:sql:SELECT COUNT(*) FROM EAM_CONFIG_PROPS WHERE 1%3D1:Availability:jdbcDriver=org.postgresql.Driver,jdbcUrl=jdbc%3Apostgresql%3A//localhost%3A9432/hqdb,jdbcUser=hqadmin,jdbcPassword=hqadmin
   =>100.0%<=
HQ Inventory Number of Platforms:
   HQ Inventory:sql:SELECT COUNT(*) FROM EAM_PLATFORM:Number of Platforms:jdbcDriver=org.postgresql.Driver,jdbcUrl=jdbc%3Apostgresql%3A//localhost%3A9432/hqdb,jdbcUser=hqadmin,jdbcPassword=hqadmin
   =>17.0<=
HQ Inventory Number of Servers:
   HQ Inventory:sql:SELECT COUNT(*) FROM EAM_SERVER:Number of Servers:jdbcDriver=org.postgresql.Driver,jdbcUrl=jdbc%3Apostgresql%3A//localhost%3A9432/hqdb,jdbcUser=hqadmin,jdbcPassword=hqadmin
   =>172.0<=
HQ Inventory Number of Services:
   HQ Inventory:sql:SELECT COUNT(*) FROM EAM_SERVICE:Number of Services:jdbcDriver=org.postgresql.Driver,jdbcUrl=jdbc%3Apostgresql%3A//localhost%3A9432/hqdb,jdbcUser=hqadmin,jdbcPassword=hqadmin
   =>1,289.0<=

HQ Inventory plugin sources

• The hq-inventory-plugin.xml plugin (Download)
  Deploy following these steps