Submitted by: Egor Puzanov

Description:

This Functionality ZenPack provides a new zenperfsql daemon and SQL data source. It based on Twisted  twisted.enterprise.adbapi interface to the standardized DB-API 2.0 API, which allows you to access a number of different RDBMSes.

• Connection String
  • Format the same as for adbapi.ConnectionPool: "dbmodulename", *args, **kwargs
  • Examples:
    • 'MySQLdb',host='localhost',user='user',passwd='pwd',db='somedb'
    • 'pyodbc','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
• SQLClient can be used standalon
• SQLPlugin 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.2, 3.2.1, 4.2.0
• External Dependencies:
• ZenPack Dependencies:
• Installation Notes: zenoss restart after installing this ZenPack.
• Configuration:

History:

Change History:

• 1.0 initial release
• 1.2 query parsing reworked
• 1.3 fix results parsing
• 1.4 fix SqlPerfConfig errors
• 1.5 escaping $ character within sql query
• 1.6 fix results parser (thanks Dmitry Petuhov for pointing that out)
• 2.0 new SQLClient and zenperfsql daemon
• 2.1 fix data source test function and thresholds processing
• 2.2 fix parsing of empty results
• 2.3 fix memory leaks in pywbemdb and pywsmandb, fix processing empty results
• 2.4 fix thresholds processing (thanks j053ph4 and gcarter)

Tested: This ZenPack was tested with versions 2.5.2, 3.2.1, 4.2.0

Source:  https://github.com/epuzanov/ZenPacks.community.SQLDataSource

Tagged Releases:

• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/sqldatasource-1.0
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/sqldatasource-1.2
• http://zenpacks.zenoss.org/trac-zenpacks/browser/tags/sqldatasource-1.3
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-1.4
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-1.5
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-1.6
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-2.0
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-2.1
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-2.2
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-2.3
• https://github.com/epuzanov/ZenPacks.community.SQLDataSource/tree/sqldatasource-2.4

Known issues: