Skip navigation
Currently Being Moderated

ODBC Data Source

VERSION 16  Click to view document history
Created on: Sep 14, 2009 1:33 PM by bigegor - Last Modified:  Jul 13, 2012 11:50 AM by bigegor

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:


          Known issues:

          Attachments:
          Note: binary attachments ending in .zip will need to be unzipped before use.
          Comments (5)