Attachments

advanced/SQL Widget

1. SQL WIDGET PLUGIN

Project name:

com_nexthink_SqlTimeSeriesNGet

Source SVN:

http://svn.intra.nexthink.com/service/portal/plugin--sql

Contents

  1. SQL WIDGET PLUGIN
    1. Version information and downloads
    2. What's new ?
    3. Summary
    4. Prerequisites
    5. Limitations
    6. Deployment on a Production Site
      1. Prerequisites
      2. Quick installing (Portal V3.2.2 and upper)
      3. Manual installing (Portal V3.2.1 and before)
      4. Installing the examples
    7. Using the widget
      1. Creating your SQL expressions
      2. SQL-specific steps of the wizard
      3. SQL Hints

1.1. Version information and downloads

Latest version - V4 (64 bit):

Latest version - V3 (32 bit):

Older versions:

1.2. What's new ?

2010-07-15 [V1.2.0.0] Support for Oracle

  • whatsnew.png

2009-06-04 [v1.1.0.0] Support for MySQL

1.3. Summary

This project implements a Portal plugin, providing:

  • an SQL connector to an arbitrary Oracle/Postgres/MySQL RDBMS,
  • a configuration wizard to define all parameters, with in particular, SQL statements defining: a metric and an optional (top) table,
  • a visualization widget, displaying the metric and the table in time:

  • widget_view.png

1.4. Prerequisites

  1. An access to the RDBMS over TCP/IP, with an account able to read the wanted table(s).
  2. A working NEXThink Portal, version see "Version information" table above.

1.5. Limitations

  • RDBMS credentials are managed in a per-widget basis (not centralized).
  • RDBMS are stored in plain text into the Portal configuration.
  • When the server is unreachable, it takes approx 3 minutes to time out.

1.6. Deployment on a Production Site

1.6.1. Prerequisites

1.6.2. Quick installing (Portal V3.2.2 and upper)

  • As root: 

    cd /var/nexthink/portal
rsquery/installPlugin.py /path/to/project--plugin--sql--rXXXX.plugin

1.6.3. Manual installing (Portal V3.2.1 and before)

  1. Be root.

  2. Install the plugin as usual: 

    cd /var/nexthink/portal
cat /path/to/plugin-sql--rXXXX.plugin | tar xvz

  3. Import the module (this will install the necessary 3rd party packages): 

    cd /var/nexthink/portal
plugin-install/plugin--sql.sh

  4. The portal should restart automatically, make sure it did, and if not, do, as root: 

    /etc/init.d/nxt-tomcat restart

1.6.4. Installing the examples

  1. Create a test DB on the portal's Postgres server: 
    cd /var/nexthink/portal/ngets/com_nexthink_SqlTimeSeriesNGet/test/
./build_database.sh  postgres

    <!> The test DB contains items with dates from 2007-10-02 to 2010-10-28.

  2. Log into the portal web site as admin.
    • Take the sample module from the library:

    • library_view.png

    • Now you can compute the widget for a small date range between 2008-01-07 and 2008-10-28.
    • Upon error, see the file 
      tail -n 100 /var/nexthink/portal/log/portal.log
    • See the metrics in its dashboard.

1.7. Using the widget

1.7.1. Creating your SQL expressions

  • An SQL interactive terminal is useful, to create and test the SQL statements, and introspect the tables structures: For PosrgreSQL: 
    psql DATABASE -U USER
    for MySQL: 
    mysql DATABASE -u USER -p

1.7.2. SQL-specific steps of the wizard

  • Widget instances can be created interactively in the Portal, via Create Widget in the Admin tab.

1.7.2.1. Step 1: parameters

  • wizard_step1.png


  • Daily value

    • SQL expression:
      • The SQL expression must return a single value (the result of a reduction)

      • It can use these time-related tokens: %Y Year with 4 digits, %m Month with 2 digits, %d Day with 2 digits

    Daily table

    • SQL expression:
      • An SQL statement which should return a table. If omitted, no table will be computed nor displayed.

      • It can use these time-related tokens: %Y Year with 4 digits, %m Month with 2 digits, %d Day with 2 digits

    • Caption: The title appearing above the table, if the table is specified.
    • Columns: Each lines defines how each column of the table will be handled. Each line has the format:

      • Column title or Column title = TYPE

      • where TYPE is one of string or number (by default string if omitted)

1.7.2.2. Step 2: tests

  • This step allows to test the two SQL statements. A date can be entered. If no date is entered, the current date (today) is used.
  • The results are output in plain text into the text area.
  • Errors (such as connection, SQL, or column format) are also indicated into the text area.

  • wizard_test.png

1.7.3. SQL Hints

  • For the daily value:

    • Use a reduction function like COUNT, SUM, AVERAGE. Example: 

      SELECT count("ID") FROM ...

    For the daily table:

    • All computed rows will all be displayed. Hence, the table should not exceed a "reasonable" size (approx 5 columns and 50 rows).
    • Tips:

      • to sort the table, use ORDER

      • to limit the number of rows, use LIMIT

      • to produce rows counting similar entries, use COUNT and GROUP BY

    For all SQL statements:

    SQL dialects

    • Each RDBMS some syntactic specificities, especially to express date ranges. Examples:

      Database type

      Database

      Daily value

      Daily table

      MySQL

      NDOUtils (history tool for Nagios)

      select count("object_id") from nagios_statehistory where state_type=1 and state_time>='%Y-%m-%d 00:00:00' and state_time<'%Y-%m-%d 23:59:59'

      select state, count(*) from nagios_statehistory where state_type=1 and state_time>='%Y-%m-%d 00:00:00' and state_time<'%Y-%m-%d 23:59:59' group by state order by 1 desc

      PostgreSQL

      Data exported from McAfee ePO

      select count("ID") from test_schema.test_table where "EventDateTime" between '%Y-%m-%d' and (date '%Y-%m-%d' + interval '1 day')

      select "VirusName", COUNT(*) from test_schema.test_table where "EventDateTime" between '%Y-%m-%d' and (date '%Y-%m-%d' + interval '1 day') group by "VirusName" order by 2 desc

CategoryPortal CategoryPortalPlugin

last modified 2011-12-20 16:58:40