Skip navigation
Currently Being Moderated

SQL Data Source

VERSION 13  Click to view document history
Created on: Jul 13, 2010 11:12 AM by bigegor - Last Modified:  Sep 16, 2012 12:30 PM by bigegor

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:

        Screen shot 2010-07-12 at 10.41.31 PM.png


        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:

        Known issues:

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