Configuring the SQL monitor adapter

Adapter type: ro-adapter-sql-monitor_vv.rr.nn

You can define the URL for an SQL query in the following ways:

  • Define the target, port, subprotocol, database, and any parameter as elements in the configuration, allowing the process to form the URL.

    The following figure is an excerpt from the SQL actor adapter configuration that depicts method 1 of defining the URL. You need supporting elements to form the complete configuration.

    XML sample of method 1 for defining a URL

    <host>host1</host>
    <port>port1</port>
    <subprotocol>oracle:thin</subprotocol>
    <database>database1</database>

  • Define the complete URL, including parameters, in a single element in the configuration.

    The following figure is an excerpt from the SQL actor adapter configuration that depicts method 2 to define the URL. You need supporting elements to form the complete configuration.

    XML sample of method 2 for defining a URL

    <url>jdbc:oracle:thin:@target1:port1:database1?param1=abc&param2=xyz</url>

The following table describes the adapter configuration elements for the SQL monitor adapter.

Note

The SQL monitor adapter does not support form-based adapter configuration at all. You would have to configure this adapter using the XML view. To configure the adapter, see Configuring-base-adapters.

SQL monitor adapter configuration node elements

Element

Description

Required

<targets>

Contains the <target> element

Yes

<target>

Contains the SQL server, database, and connection information.

This element has a name attribute that provides a target name. If the target name has a default attribute with the value set to true, the target is a global target, else the target is a local target.

No

<url>

Specifies the complete connection URL for the target database

If you do specify the <url> element, you must specify <host>, <port>, <subprotocol>, and <database> elements as needed so that the process can create the URL.

Note

You can specify an IPv6 address with a zone ID for the <url> element---for example, <url>jdbc:mysql://address=(protocol=tcp)(host= fe80::20f:feff:fe40:abaa)(port=3306)/test </url>.

To find the zone ID of the required computer, see Zone ID for an IPv6 address.

For IPv6 URL details for different databases, see IPv6 specifications for databases.

No

<host>

Specifies the host name or the IP address of the database server

This element is required in the absence of the <url> element.

Note

You can specify an IPv6 address with a zone ID for the <host> element---for example, <host>fe80::78d0:8144:ddee:f5a%4</host>.
To find the zone ID of the required computer, see Zone ID for an IPv6 address.

Conditional

<port>

Specifies the port on which the database server listens

The requirements for the JDBC driver specified in the <driver> element determine whether the <port> element is required. This element is not used if you specify the <url> element.

Conditional

<user-name>

Specifies the user name to be used for database authentication

The database requirements in the user's environment determine whether the <user-name> element is required.

Conditional

<password>

Specifies the password that corresponds to the <user-name>

The database requirements in the user's environment determine whether the <password> element is required.

Conditional

<subprotocol>

Specifies the driver specified subprotocol, used to build the connection URL

The adapter prepends jdbc: to the value provided. This element is required in the absence of the <url> element.

  • Format: <database type>:<subprotocol>
  • Sample values: sybase:Tds, oracle:thin

Conditional

<database>

Specifies the name of the database to which the adapter connects This element is required in the absence of the <url> element.

Conditional

<driver>

Specifies the fully qualified JDBC driver classname

The sample values are:

  • com.sybase.jdbc3.jdbc.SybDriver
  • oracle.jdbc.driver.OracleDriver

Yes

<character-set>

Specifies the supporting CharSet

Also called character set, it includes identifiers describing a series of universal characters.

You must specify the <character-set> element only if the target database is MySql.

No

<max-wait>

Specifies the maximum time, in milliseconds, to wait before timing out when establishing a connection to a database

Default value: 60,000 milliseconds (1 minute)

No

<max-connection-idle-time-millis>

Specifies the maximum time, in milliseconds that a connection can remain idle before it is closed

Default value: 300,000 milliseconds (5 minutes)

No

<min-connection-idle-time-millis>

Specifies the minimum time, in milliseconds that a connection can remain idle before being evaluated for termination

Default value: 120,000 milliseconds (2 minutes)

No

<evictor-idle-time-millis>

Specifies the frequency, in milliseconds, in which a thread used to close idle connections runs

Note: Set the value of this element lower than the value of the <max-connection-idle-time-millis> element.

Default value: 60,000 milliseconds (1 minute)

No

<max-active-connections>

Specifies the maximum number of connections allowed at any time in the connection pool Default value: 10

No

<initial-active-connections>

Specifies the minimum number of connections created during adapter startup

The poolable API of Apache software does not support this feature. However, the BasicDataSource (non-poolable-version), supports this feature.

Default value: 2

No

<max-idle-connections>

Specifies the maximum number of idle connections allowed at any time

Default value: 5

No

<min-idle-connections>

Specifies the minimum number of idle connections allowed at any time

Default value: 0

No

<validate-query>

Specifies the query statement used to validate the connection

This element is required in the presence of the <validate-connections-on-return> or <validate-idle-connections> element.

Conditional

<validate-connections-on-return>

Indicates whether a connection is validated when it is returned to the pool

The <validate-query> element must be present to use this feature.

Valid values: true, false (default)

With a value of true, the connection is evaluated to determine the status and, if not alive, the connection is removed from the pool.

No

<validate-idle-connections>

Indicates whether periodic checks are done on idle connections in the pool (set of connections)

The <validate-query> element must be present to use this feature.

Valid values: true, false (default)

With a value of true, the connection is evaluated to determine the status and, if not alive, the connection is removed from the pool.

No

<event-statements>

Contains the <event-statement> elements

Yes

<event-statement>

Contains the details about the query to be performed on the specified host

Yes

<query>

Specifies the query to be executed periodically on the specified database

The query results are returned in the adapter response. The SQL query for a Monitor adapter only supports SELECT statements, not INSERT, UPDATE, and DELETE statements.

Yes

<poll-interval>

Specifies the frequency, in seconds, in which the specified <host> is queried

Default value: 60 seconds

No

<single-monitor-event>

Generates a single event for all the records retrieved by the <query> statement

Valid values: true (default), false

No

<suppress-row-info>

Specifies whether to add or suppress row-related elements from the event if the <single-monitor-event> is set to false.

Valid values: true, false (default)

If true, the following elements are removed from the event XML:

<row-number>
<response-row-count>
<row-count>

If false, all row related information is retained in the event XML.

No

<ktgt-cache-file>

Specifies the location of a valid Kerberos Ticket Granting Ticket (TGT) to obtain a connection through the Kerberos tunnel

Note:

For the Windows OS, the path should be a fully
qualified path using double slashes ('\\') instead of
a single slash ('\') to the krb ticket file. 

Example:

 C:\\testFolder\\ticketFile
<ktgt-cache-file>/tmp/krb5cc_0
</ktgt-cache-file> 

No

<krb-service-principal>

Specifies the name of the service principal registered with the Sybase database to allow Kerberos authentication

This element is required if the <ktgt-cache-file> element is used.

Example:
<krb-service-principal>REALOPSLINUX1@SYNAPSE.COM
</krb-service-principal>

Conditional

Note

The following database servers support an IPv6 address:

  • Microsoft SQL Server 2005 and later
  • MySQL version 5.5.3 and later
  • MySQL Connector/Driver version 5.5.18 and later
  • Oracle version 11gR2 and later
  • Oracle Connector/Driver version 1.4

The following figure shows a sample XML of the monitor adapter configuration when <single-monitor-event> is false and <suppress-row-info> is true.

<config>
  <targets>
     <target name="oracle1" default="true">
        <user-name>orcluname1</user-name>
        <password>pass</password>
        <driver>oracle.jdbc.driver.OracleDriver</driver>
        <host>clm-xxx-xxxxxx</host>
        <port>1521</port>
        <subprotocol>oracle:thin</subprotocol>
        <database>ORA12C</database>
     </target>
  </targets>
  <event-statements>
     <event-statement>
        <query>select * from team</query>
        <poll-interval>60</poll-interval>
        <single-monitor-event>false</single-monitor-event>
        <suppress-row-info>true</suppress-row-info>
     </event-statement>
  </event-statements>
</config>

The following figure shows the sample event XML when  <single-monitor-event> is false and <suppress-row-info> is true.

<!-- event[1] when query returns 2 rows -->
<sql-monitor-event>
<row>
<column name="FIRST_NAME">Allen</column>
<column name="LAST_NAME">AB</column>
<column name="SEX">M</column>
<column name="AGE">31</column>
</row>
<metadata>
<target-name>oracle1</target-name>
<query-executed>select * from team</query-executed>
<response-column-count>4</response-column-count>
<status>success</status>
</metadata>
</sql-monitor-event>
<!-- event[2] -->
<sql-monitor-event>
<row>
<column name="FIRST_NAME">Allen</column>
<column name="LAST_NAME">Allbrook</column>
<column name="SEX">M</column>
<column name="AGE">38</column>
</row>
<metadata>
<target-name>oracle1</target-name>
<query-executed>select * from team</query-executed>
<response-column-count>4</response-column-count>
<status>success</status>
</metadata>
</sql-monitor-event>

The following figure shows the sample adapter configuration for a Procedure Event when <single-monitor-event> is false and <suppress-row-info> is true. 

<config>
 <targets>
   <target name="oracle1" default="true">
     <user-name>orcluname1</user-name>
     <password>pass</password>
     <driver>oracle.jdbc.driver.OracleDriver</driver>
     <host>clm-xxx-xxxxxx</host>
     <port>1521</port>
     <subprotocol>oracle:thin</subprotocol>
     <database>ORA12C</database>
   </target>
 </targets>
 <event-statements>
   <event-statement>
     <procedure>
       <name>SHOW_MESSAGES_TRANSACTION1</name>
       <argument-set>
         <argument>
           <index>1</index>
           <in-out>IN</in-out>
           <data-type>NUMBER</data-type>
           <value>2</value>
         </argument>
         <argument>
           <index>2</index>
           <in-out>OUT</in-out>
           <data-type>REFCURSOR</data-type>
         </argument>
       </argument-set>
     </procedure>
     <poll-interval>60</poll-interval>
     <single-monitor-event>false</single-monitor-event>
     <suppress-row-info>true</suppress-row-info>
   </event-statement>
 </event-statements>
</config>

The following figure shows the sample event XML for a Procedure Event when <single-monitor-event> is false and <suppress-row-info> is true.  

<!-- event[1] when procedure returns 2 rows -->
<sql-monitor-event>
 <row>
   <column name="ID" label="ID">1</column>
   <column name="MESSAGE" label="MESSAGE">xyz</column>
 </row>
 <procedure-metadata>
   <response-set-count>1</response-set-count>
   <query-executed>{call SHOW_MESSAGES_TRANSACTION1(?, ?) }</query-executed>
   <status>success</status>
 </procedure-metadata>
</sql-monitor-event>
<!-- event[2] -->
<sql-monitor-event>
 <row>
   <column name="ID" label="ID">2</column>
   <column name="MESSAGE" label="MESSAGE">aaaaaaa</column>
 </row>
 <procedure-metadata>
   <response-set-count>1</response-set-count>
   <query-executed>{call SHOW_MESSAGES_TRANSACTION1(?, ?) }</query-executed>
   <status>success</status>
 </procedure-metadata>
</sql-monitor-event>

The following figure shows an XML sample of the non-URL SQL monitor adapter configuration.

XML sample of the non-URL SQL monitor adapter configuration

<config>
   <targets>
       <target name = "target1" default = "true">
           <host>localhost</host>
           <port>3306</port>
           <user-name>root</user-name>
           <password>qbasic</password>
           <subprotocol>mysql</subprotocol>
           <database>moviedb</database>
           <driver>com.mysql.jdbc.Driver</driver>
           <max-wait>1200</max-wait>
           <max-connection-idle-time-millis>10000
           </max-connection-idle-time-millis>
       <min-connection-idle-time-millis>1000
           </min-connection-idle-time-millis>
       <evictor-idle-time-millis>5000</evictor-idle-time-millis>
       <max-active-connections>20</max-active-connections>
       <initial-active-connections>2</initial-active-connections>
       <max-idle-connections>5</max-idle-connections>
       <min-idle-connections>1</min-idle-connections>
       <validate-connections-on-return>true</validate-connections-on-return>
       <validate-idle-connections>true</validate-idle-connections>
       <validate-query>select sysdate from dual</validate-query>
       </target>
   </targets>
   <event-statements>
       <event-statement>
           <query>select * from movie</query>
           <poll-interval>2</poll-interval> (In Seconds)
           <single-monitor-event>true</single-monitor-event>
           <suppress-row-info>true</suppress-row-info>
       </event-statement>
       <event-statement>
           <query>select * from movietype</query>
           <poll-interval>4</poll-interval>
          <single-monitor-event>true</single-monitor-event>
       </event-statement>
   </event-statements>
</config>

The following figure shows an XML sample of the URL SQL monitor adapter configuration.

XML sample of the URL SQL monitor adapter configuration

<config>
   <targets>
       <target name = "mysql_bpm" default = "true">
           <url>jdbc:mysql://localhost:3306/test</url>
           <user-name>root</user-name>
           <password>root123</password>
           <driver>com.mysql.jdbc.Driver</driver>
           <max-wait>1200</max-wait>
       </target>
       <target name = "mysql_cmdb" default = "true">
           <url>jdbc:mysql://localhost:3306/firstuser</url>
           <user-name>firstuser</user-name>
           <password>firstuser</password>
           <driver>com.mysql.jdbc.Driver</driver>
           <max-wait>1200</max-wait>
       </target>
   </targets>
   <event-statements>
       <event-statement>
           <query>select * from test</query>
           <poll-interval>15</poll-interval>
           <single-monitor-event>true</single-monitor-event>
           <suppress-row-info>true</suppress-row-info>
           <targets>
               <target>mysql_cmdb</target>
           </targets>
       </event-statement>
       <event-statement>
           <query>select * from test</query>
           <poll-interval>20</poll-interval>
           <single-monitor-event>true</single-monitor-event>
       </event-statement>
   </event-statements>
</config>

The SQL monitor adapter supports the following MySql JDBC charsets:

big5

dec8

cp850

latin1

latin2

ascii

ujis

sjis

hebrew

tis620

euckr

koi8u

gb2312

greek

cp1250

gbk

latin5

utf8

ucs2

cp866

keybcs2

macce

macroman

cp852

latin7

cp1251

cp1256

cp1257

cp932


Note

The SQL monitor adapter supports the following databases:

  • MySql (<character-set> element required when configuring the adapter)
  • Sybase (You can append charset=value to the <url> element or include the <charset> element and it's value in the <parameters> element)
  • Oracle (<character-set> element not required when configuring the adapter)
  • Microsoft SQL Server (<character-set> element not required when configuring the adapter)
  • DB2 (<character-set> element not required when configuring the adapter)

    The target database must contain the supporting character set to get the desired results.

The following figure shows an XML sample for the SQL monitor adapter configuration with a charset that is supported.

XML sample of the SQL monitor adapter configuration with a supported charset

<config>
     <target>10.128.249.29</target>
     <port>3306</port>
     <user-name>root</user-name>
     <password>root</password>
     <subprotocol>mysql</subprotocol>
     <database>test</database>
     <driver>com.mysql.jdbc.Driver</driver>
     <character-set>utf8</character-set>
     <event-statements>
       <event-statement>
         <query>select * from  employee where address='1〒108-8282東京都港港南'</query>
         <poll-interval>30</poll-interval>
         <single-monitor-event>true</single-monitor-event>
         <suppress-row-info>true</suppress-row-info>
       </event-statement>
     </event-statements>
   </config

The following figure shows an XML sample for the SQL monitor adapter configuration with a charset that is not supported.

XML sample of the SQL monitor adapter configuration with a charset that is not supported

<config>
   <target>localhost</target>
   <port>3306</port>
   <user-name>root</user-name>
   <password>root</password>
   <subprotocol>mysql</subprotocol>
   <database>test</database>
   <driver>com.mysql.jdbc.Driver</driver>
   <parameters>
       <characterEncoding>shift_jis</characterEncoding>
       <useUnicode>true</useUnicode>
   </parameters>
   <event-statement>
       <query>select * from  employee where address='1?108-8282??????'</query>
       <poll-interval>30</poll-interval>
       <single-monitor-event>true</single-monitor-event>
       <suppress-row-info>true</suppress-row-info>
   </event-statement>
</config>

The following figure shows an XML sample for the SQL monitor adapter configuration when the target database is Sybase.
XML sample of the SQL monitor adapter configuration when the target database is Sybase.

<config>
 <targets>
    <target name="target1" default="true">
       <url>jdbc:sybase:Tds:10.128.249.29:5000/MASTER?charset=iso_1</url>
       <user-name>sa</user-name>
       <password />
       <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
     </target>
 </targets>
 <event-statements>
    <event-statement>
       <query>select * from  employee where address='äöÖüÜß'</query>
       <poll-interval>30</poll-interval>
       <single-monitor-event>true</single-monitor-event>
    </event-statement>
 </event-statements>
</config>

The following figure shows an XML sample of the non-URL SQL monitor adapter configuration supporting stored procedures.

XML sample of the non-URL SQL monitor adapter configuration supporting a stored procedure

<config>
 <targets>
   <target name="oracle" default = "true">
     <user-name>u1</user-name>
     <password>p1</password>
    <driver>oracle.jdbc.driver.OracleDriver</driver>
     <host>localhost</host>
     <port>1521</port>
     <subprotocol>oracle:thin</subprotocol>
     <database>D1</database>
   </target>
 </targets>
 <event-statements>
   <event-statement>
    <procedure>
       <name>customer_pkg.getCustomerCount</name>
       <argument-set>
         <argument>
           <index>1</index>
           <in-out>IN</in-out>
           <data-type>VARCHAR</data-type>
           <value>c1</value>
         </argument>
            <argument>
               <index>2</index>
           <in-out>IN</in-out>
           <data-type>NUMBER</data-type>
         </argument>
       </argument-set>
     </procedure>
     <poll-interval>4</poll-interval>
     <single-monitor-event>true</single-monitor-event>
   </event-statement>
 </event-statements>
</config>

Note

When the value of the <in-out> parameter is IN, the <index> parameter does not exist.

The following figure shows an XML sample of the SQL monitor adapter configuration with a function returning an integer.

XML sample of the SQL monitor adapter configuration with a function returning an integer

<config>
 <targets>
   <target name="oracle" default="true">
     <host>10.254.1.78</host>
     <port>1521</port>
     <user-name>bmcadmin</user-name>
     <password>abc123</password>
     <subprotocol>oracle:thin</subprotocol>
     <database>testdb</database>
     <driver>oracle.jdbc.driver.OracleDriver</driver>
   </target>
 </targets>
 <event-statements>
   <event-statement>
     <procedure>
       <name>Testing</name>
       <return-data-type>INTEGER</return-data-type>
       <argument-set>
         <argument>
           <index>1</index>
           <in-out>IN</in-out>
           <data-type>NUMBER</data-type>
           <value>100</value>
         </argument>
       </argument-set>
     </procedure>
     <poll-interval>60</poll-interval>
     <single-monitor-event>true</single-monitor-event>
   </event-statement>
 </event-statements>
</config>

The following figure shows an XML sample of the SQL monitor adapter configuration with Sybase Kerberos Authentication.

XML sample of the SQL monitor adapter configuration with Sybase Kerberos Authentication

<config>
<targets>
 <target name="SybaseDB1" default="true">
 <url>jdbc:sybase:Tds:10.128.248.105:5000/MASTER</url>
 <user-name>sybuser1</user-name>
 <driver>com.sybase.jdbc3.jdbc.SybDriver</driver>
 <password />
 <database>master</database>
 <ktgt-cache-file>/tmp/krb5cc_0</ktgt-cache-file>
 <krb-service-principal>REALOPSLINUX1@SYNAPSE.COM</krb-service-principal>
 </target>
</targets>
<event-statements>
 <event-statement>
  <query>select * from spt_monitor</query>
  <poll-interval>15</poll-interval>
  <single-monitor-event>true</single-monitor-event>
 </event-statement>
</event-statements>
</config>

Encrypting an element's contents

You can add the attribute secure="true" to an XML adapter element XML view to ensure that the element's contents is encrypted when displayed.

Note

You must be using TrueSight Orchestration Platform version 8.1 or later to use this encryption attribute.

Enabling custom logging

To enable custom logging for the adapter, you must specify a log file name. You can also provide additional parameters for logging.

Note

You must be using TrueSight Orchestration version 8.1 or later to use the custom logging feature. These parameters will be ignored in earlier versions of TrueSight Orchestration Platform.

These parameters are available with supported adapter versions. See TrueSight Orchestration Content documentation for details.

  • Log File Name: Provide a name for the log file.
    This file will be stored in the AO_HOME/tomcat/logs directory. If Log File Max Backup Index value is greater than 0, the log file name is suffixed with the backup index. For example, if the parameter value is a.log, backup log files will have names, such as a.log.1, a.log.2.
  • Log File Size: Specify a size limit for the log file. 
    If the value specified for Log File Max Backup Index is greater than 0, when the specified size is reached, the current file is renamed with the suffix .1. Otherwise, the log file will be reset and over-written. The default value is 10MB. The available units are KiloBytes (KB), MegaBytes (MB) or GigaBytes (GB).
  • Log File Max Backup Index: Enter the maximum number of backup files allowed. The default value is 10.
  • Log File Append: Select this option to append new log information to the existing information in the file. If unselected, the file will be overwritten with new log information.

  • Log Level: Enter the logging level using one of the following choices:

    Logging level

    Description

    DEBUG

    The most detailed logging level; logs low-level messages, normal execution, recoverable erroneous conditions, and unrecoverable erroneous conditions

    INFO

    (default)

    Logs normal execution, recoverable erroneous conditions, and unrecoverable erroneous conditions

    WARN

    Logs recoverable erroneous conditions and unrecoverable erroneous conditions

    ERROR

    The least detailed logging level; logs only error conditions that are not usually recoverable

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*