ZenPacks

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.

Contents

  1. Usage
    1. Enable SQL Server Monitoring
    2. Enable Sybase Server Monitoring
    3. Enable MySQL Server Monitoring
    4. Storing Query Results
  2. Troubleshooting
  3. Services
  4. Changes

Usage

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 MSDN Documentation: Change Server Authentication Mode

  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.
  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.
  9. Change options as needed.
    OptionDescription
    Database TypeEnter MS SQL
    Host NameSet the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    PortSet the port on which the database server is listening. If you don't specify a port number, then the default port for the database is used.
    Database NameSpecify the name of the database (required).
    UserSpecify a user name with permission to connect to the database and run queries.
    PasswordSpecify the user password.
    SQL QueriesSpecify 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.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.

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.
  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.
  9. Change options as needed.
    OptionDescription
    Database TypeEnter Sybase
    Host NameSet the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    PortSet the port on which the database server is listening. If you don't specify a port number, then the default port for the database is used.
    Database NameSpecify the name of the database (required).
    UserSpecify a user name with permission to connect to the database and run queries.
    PasswordSpecify the user password.
    SQL QueriesSpecify 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.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.

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.
  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.
  9. Change options as needed.
    OptionDescription
    Database TypeEnter MySQL
    Host NameSet the host name on which the database is located. This field accepts a TALES expression, such as ${here/id} or ${here/getManageIp}
    PortSet the port on which the database server is listening. If you don't specify a port number, then the default port for the database is used.
    Database NameSpecify the name of the database (required).
    UserSpecify a user name with permission to connect to the database and run queries.
    PasswordSpecify the user password.
    SQL QueriesSpecify the SQL queries that this data source should execute. A summary of MySQL syntax is available at: MySQL SQL Statement Syntax
  10. Click on the Save button to save your changes.
  11. Click Test to verify that the database connection can be completed, and that the data returned from the queries are correct.

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).
  • 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

Returns 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;

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 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.

NameDatabase Type
mssqlMS SQL Server
sybaseSybase
mysqlMySQL Server

Services

The following Zenoss services are required to be running by this ZenPack.

TypeName
Performance Collectorzencommand

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
Commercial

This ZenPack is developed and supported by Zenoss Inc. Commercial ZenPacks are available to Zenoss commercial customers only. Contact Zenoss to request more information regarding this or any other ZenPacks. Click here to view all available Zenoss Commercial ZenPacks.