ZenPack:SQL Transactions

From Zenoss Wiki
This is the approved revision of this page, as well as being the most recent.
Jump to: navigation, search


Note: This ZenPack is available in commercial versions of Zenoss. Click here to request more information about this commercial ZenPack. Click here to see all commercial ZenPacks.

ZenPack name
ZenPacks.zenoss.ZenSQLTx


Applications Monitored: 



SQL Transactions ZenPack

The ZenSQLTx ZenPack allows you to test the availability and performance of MySQL, Sybase and Microsoft SQL servers. It provides a SQL data source where user-defined SQL queries can be executed against a database.

Warning

The ZenPack Catalog has moved to its new home at https://www.zenoss.com/product/zenpacks as of January 17, 2017. The following information may be out of date, and this page will eventually be removed.

Support

This ZenPack is included with commercial versions of Zenoss and enterprise support for this ZenPack is provided to Zenoss customers with an active subscription.

Releases

Version 2.6.5
Released on 2016/10/19
Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.x.x
Version 2.6.4
Released on 2016/10/05
Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.x.x
Version 2.6.3- Download
Released on 13 Jan 2016
Compatible with Zenoss Resource Manager 4.2.x, Zenoss Resource Manager 5.x.x

Background

The ZenPacks.zenoss.ZenSQLTx ZenPack monitors the availability and performance of MySQL, Sybase and Microsoft SQL servers.


With this ZenPack, user-defined SQL queries can be executed against a database, and the results are returned as a SQL data source.

Prerequisites

Each remote collector must have an installed MySQL client.


Enable SQL Server Monitoring

Ensure that your Microsoft SQL Server authentication mode is set to "SQL Server and Windows Authentication mode." For more information about this setting and how to change it, refer to:

http://msdn.microsoft.com/en-us/library/ms188670.aspx


  1. Click the device in the device list.
  2. Select Device under Monitoring Templates in the left panel.
  3. Select Add Local Template from the Action menu. The Add Local Template dialog appears.
  4. Enter a name of the template, and then click Submit.
  5. Click the newly created template in the left panel.
  6. In the Data Sources area, click Add.
  7. Enter a name for the data source, select SQL as the type, and then click Submit.
  8. Double-click the newly created data source. The Edit Data Source dialog appears.
  9. Change options as needed.
    MS SQL Server Transactions Data Source Options
    Option Description
    Database Type Enter MS SQL
    Host Name Set the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    Port Set the port on which the database server is listening. If you do not specify a port number, then the default port for the database is used.
    Database Name Specify the name of the database (required).
    User Specify a user name with permission to connect to the database and run queries.
    Password Specify the user password.
    SQL Queries Specify the SQL queries that this data source should execute. A summary of MS SQL syntax is available in the documentation accompanying the software.


  10. Click Save to save your changes. Zenoss platform creates a data point that corresponds to the total query time in milliseconds.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.


For more information about setting up thresholds, graphs, and data points, refer to Zenoss Service Dynamics Resource Management Administration.


Enable Sybase Server Monitoring

  1. Click the device in the device list.
  2. Select Device under Monitoring Templates in the left panel.
  3. Select Add Local Template from the Action menu. The Add Local Template dialog appears.
  4. Enter a name of the template, and then click Submit.
  5. Click the newly created template in the left panel.
  6. In the Data Sources area, click Add.
  7. Enter a name for the data source, select SQL as the type, and then click Submit.
  8. Double-click the newly created data source. The Edit Data Source dialog appears.
  9. Change options as needed.
    MySQL Server Transactions Data Source Options
    Option Description
    Database Type Enter Sybase
    Host Name Set the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    Port Set the port on which the database server is listening. If you do not specify a port number, then the default port for the database is used.
    Database Name Specify the name of the database (required).
    User Specify a user name with permission to connect to the database and run queries.
    Password Specify the user password.
    SQL Queries Specify the SQL queries that this data source should execute. A summary of Sybase syntax is available at the Sybase Manuals Web site.


  10. Click on the Save button to save your changes. Zenoss platform creates a data point that corresponds to the total query time in milliseconds.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.


For more information about setting up thresholds, graphs, and data points, refer to Zenoss Service Dynamics Resource Management Administration.


Enable MySQL Server Monitoring

  1. Click the device in the device list.
  2. Select Device under Monitoring Templates in the left panel.
  3. Select Add Local Template from the Action menu. The Add Local Template dialog appears.
  4. Enter a name of the template, and then click Submit.
  5. Click the newly created template in the left panel.
  6. In the Data Sources area, click Add.
  7. Enter a name for the data source, select SQL as the type, and then click Submit.
  8. Double-click the newly created data source. The Edit Data Source dialog appears.
  9. Change options as needed.
    MySQL Server Transactions Data Source Options
    Option Description
    Database Type Enter MySQL
    Host Name Set the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    Port Set the port on which the database server is listening. If you do not specify a port number, then the default port for the database is used.
    Database Name Specify the name of the database (required).
    User Specify a user name with permission to connect to the database and run queries.
    Password Specify the user password.
    SQL Queries Specify the SQL queries that this data source should execute. A summary of MySQL syntax is available at:

    http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html


  10. Click on the Save button to save your changes. Zenoss platform creates a data point that corresponds to the total query time in milliseconds.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.


For more information about setting up thresholds, graphs, and data points, refer to Zenoss Service Dynamics Resource Management Administration.


Storing Query Results

If any data is retrieved from the database that can be interpreted as a number, that number can be used as a data point. In select statements in which a column name is used, that column name becomes the name of the data point. In select statements in which no column name is specified (for example, aggregate functions such as count(*), sum(), or min()), the data point name returned is database-dependent:

  • MySQL - The column name can be controlled with an 'AS' clause in the query. If used, then the column name is the "cleaned up" result of the 'AS' clause; otherwise, it uses the format: 'q' + query number (beginning with 0) + '_' + column number in the query (beginning with 0).
  • All other databases - The column name uses the format: 'q' + query number (beginning with 0) + '_' + column number in the query (beginning with 0).

Non-alphanumeric characters ([^za-zA-Z0-9_]) are removed from the column name to produce the data point name. Any query results that cannot be interpreted as a number are ignored, and the query numbers will not change.

For example, the queries:

select count(*) from Users;select UserName from Users; select count(*) * 4 from Users

return these results:

Queries completed successfully. | totalTime=2.13289260864 count=3.0 count4=12.0

Note: To use multiple queries (such as in the preceding example), they must be separated with a semicolon.


This example demonstrates multiple results from a single query:

select count(*) as count1, count(*)-1001 from history;

and returns these results:

Queries completed successfully. | totalTime=72.6099014282 count1=99894.0 count1001=98893.0

Notes:

  • For SQL Server, use the format q*_* if no column name is found.
  • The SQL 'as' renaming capability can be used to control the name of the data point.


Troubleshooting

To verify any queries, as well as any permissions or authentication issues, run the zensql.py command from the command line. Here's an example against the MySQL database on a Zenoss platform server:

cd $ZENHOME/ZenPacks/*ZenSQLTx*/Z*/z*/Z*
./zensql.py -t mysql -H localhost -u zenoss -p zenoss -d events 'select \* from events.log;'
Queries completed successfully. | totalTime=54.5899868011

Note: Single quotes (') are required around the SQL statement. Any wild card characters (such as *) must be escaped, as shown in the previous example.


For the zensql.py command, the database types understood are shown in the following table.


zensql.py Database Types
Name Database Type
mssql MS SQL Server
sybase Sybase
mysql MySQL Server


Daemons

Type Name
Performance Collector zencommand

Changes

2.6.5
  • Fix the traceback associated with errorLog (ZEN-24736)
2.6.4
  • Fix broken "timeout" field in SQL datasource edit dialog (ZEN-22955)
  • Make SQL libraries importable by other ZenPacks (ZEN-23722)
2.6.3
  • Added support for Zenoss 5.x
  • Fixed issue with not secure password on monitoring template

Installation

Normal Installation (packaged egg)

  1. Download the appropriate egg file for the version of Zenoss you are running.
  2. Ensure you are logged in as the zenoss user:
    $ sudo su - zenoss
  3. Install the ZenPack:
    $ zenpack --install ZenPacks.zenoss.ZenSQLTx-*.egg
  4. Restart these services:
    $ zenoss restart


Discuss

Purplemarker.png New: Don't forget to add yourself to the Zenoss User Map!

blog comments powered by Disqus