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.

Releases

Version 2.7.1 - Download
Released on 2017/12/06
Compatible with Zenoss Resource Manager 5.0 - 6.1
Version 2.7.0 - Download
Released on 2017/10/09
Compatible with Zenoss Resource Manager 5.0 - 5.3

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

Caveats

GLIBC Version Error on Zenoss 4.x

When Sybase database type is used in a ZenSQLTx datasource, the following error `/lib64/libc.so.6: version `GLIBC_2.14' not found` might be observed in zencommand log files or when you're trying to test the datasource on the UI. It happens because your current GNU C Library version is lower than the required version.

To check the currently installed GNU C Library version on your Zenoss instance use ldd --version command.

You cannot update the GNU C Library on a Zenoss instance safely, therefore, the Sybase database monitoring is not supported on Zenoss 4.x.

Services

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

TypeName
Performance Collectorzencommand

Supported Databases

The following databases are officially supported. Other databases may also be supported, especially derivatives of MS SQL, MySQL and Sybase.

TypeVersion
Microsoft SQL Server2016
MySQL Community Server5.7.19
SAP ASE (Adaptive Server Enterprise)15
SAP ASE (Adaptive Server Enterprise)16

Changes

2.7.1

  • Fix an error when cycle time in ZenSQLTx Data Source is empty (ZEN-29196)

2.7.0

  • Add "Caveat" section to the documentation and update it with "GLIBC Version Error on Zenoss 4.x" topic (ZPS-2209)
  • Timeout parameter doesn't cause sybase calls to fail (ZPS-2196)
  • Add cycle time to ZenSQLTx Data Source (ZPS-2119)
  • Change ZenSQLDataSource password type and hide the password on Zenoss UI for security (ZPS-2133)
  • Fix monitoring of MSSQL databases (ZPS-1826)
  • Fix broken ZenSQLTx monitoring (ZPS-1657)
  • Fix monitoring of Sybase databases (ZPS-513)
  • Tested with Zenoss Resource Manager 5.3.2 and Zenoss Resource Manager 4.2.5 RPS 743

2.6.6

  • Fix SQL datasource test to test from appropriate collector (ZEN-22199)

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.