Submitted by: Egor Puzanov

Description:

This Functionality ZenPack provides a new ODBC data source.

• Connection String
  • ODBC connection string format
  • Example:
    • driver={MySQL};server=localhost;database=somedb;uid=user;pwd=pwd
• Columns name to Data Points name mapping
  • Description: use SQL Aliases Syntax for  columns to set the same name as Data Poins names.
  • Example:
    • Query: SELECT sum(data_length) as dataSize, sum(index_length) as indexSize, sum( data_length + index_length ) as sizeUsed FROM TABLES WHERE table_schema='mysql' GROUP BY table_schema
    • Data Points: dataSize, indexSize, sizeUsed
• Queries sorting (join multiple queries in one query)
  • Description: WHERE statement will be removed from SQL Query and used as key by results parsing.
  • Example: We have 3 databases ('events', 'information_schema' and 'mysql') and we need collect data and idx size of every database.
  • DataSource Query for 'events':  SELECT sum(data_length) as dataSize, sum(index_length) as indexSize, sum( data_length + index_length ) as sizeUsed FROM TABLES WHERE table_schema='events' GROUP BY table_schema
  • DataSource Query for 'mysql': SELECT sum(data_length) as dataSize, sum(index_length) as indexSize, sum( data_length + index_length ) as sizeUsed FROM TABLES WHERE table_schema='mysql' GROUP BY table_schema
  • DataSource Query for ' information_schema': SELECT sum(data_length) as dataSize, sum(index_length) as indexSize, sum( data_length + index_length ) as sizeUsed FROM TABLES WHERE table_schema=' information_schema' GROUP BY table_schema
  • Result query:  SELECT sum(data_length) as dataSize, sum(index_length) as indexSize, sum( data_length + index_length ) as sizeUsed,table_schema FROM TABLES GROUP BY table_schema
  • 3 queries will be replaced by 1 query
• support for DataPoint Aliases (evaluate befor write RRD)
  • before be saved in RRD, values will be evaluated by REVERSED alias.formula
  • supported operations +, -, *, /
  • tales variables: now, here
  • Example: 
    • alias.formula = "100,/,1,-" replaced by "1,+,100,*"
  • Why reversed?
    • raw data: 100 -> "100,100,/,1,-" -> RRD: 0 -> "0,100,/,1,-" ->Report: -1 - False!
    • raw data: 100 -> "100,1,+,100,*" -> RRD: 10100 -> "10100,100,/,1,-" ->Report: 100 -True!
• support for dictionary as DataPoint Aliases (evaluate befor write RRD)
  • Example:
    • "Unknown":0,"Other":1,"OK":2,"Warning":3,"Error":4
• ODBCPlugin CollectorPlugin
• support for avg, count, sum, min, max, first, last function for datapoints with multiline result
  • if query return multiple instance to zenperfsql datemon, avg, count, sum, min, max, first, last value can be evaluated

Screenshots:

Installation Requirements:

• Zenoss Versions Supported: 2.5, 3.0
• External Dependencies: pyodbc (optional)
• ZenPack Dependencies: SQL Data Source
• Installation Notes: zenoss restart after installing this ZenPack.
• Configuration:

History:

• 1.0 initial release
• 2.1 converted to a Zenoss 2.5 daemon
• 2.1.1 fixed "Test" errors
• 2.1.2 fixed an issue with plotting a zero values (Thanks jenkinskj for pointing that out)
• 3.0 switch to SQLDataSource framework
• 3.1 fix modeler plugin
• 3.2 fix parsing of empty values in isql output
• 4.0 latest SQL Data Source support

Tested: This ZenPack was tested with versions 2.5.2 and 3.0.0.

Source:  http://zenpacks.zenoss.org/trac-zenpacks/browser/zenpacks/ZenPacks.community.ZenODBC

Tagged Releases:

• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-1.0
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-2.1
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-2.1.1
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-2.1.2
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-3.0
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-3.1
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-3.2
• https://zenpacks.zenoss.org/trac-zenpacks/browser/tags/odbc-4.0

Known issues: