Moogsoft Docs

ExternalDb

Description

The ExternalDb Moobot module allows Moogsoft AIOps to access the following external relational databases (as well as any relational database that supports JDBC):

  • MySQL

  • Microsoft SQL Server

  • IBM DB2

  • Oracle

  • PostgreSQL

With ExternalDb, Moogsoft AIOps can retrieve information from external databases for use in alerts and Situations and can also update information in external databases with information from Moogsoft AIOps.

ExternalDb is available to load into any standard Moobot.

To use, at the top of a Moobot js file, define a new global object externalDb to load the ExternalDb module:

var externalDb = MooBot.loadModule('ExternalDb');

externalDb is the name of the database.

Methods
Reference Guide
externalDb.connect()

Establishes connection to an external database with defined connection properties.

Request Arguments

Name

Type

Description

<properties>

Object

A Javascript object containing connection properties. See below.

Database connection properties

The ExternalDb module connect method defines connection properties as a Javascript object, which may include the following keys:

Key

Description

type

The type of the database.

If type is omitted you must specify the URL, jar files and JDBC class name. To use an external database other than those in the supported list, omit the type from the connection properties.

host

The database host name or IP address (default is: 'localhost').

database

The database name.

port

The port number. Default values:

MySQL - 3306

SQL Server - 1433

DB2 - 50000

Oracle - 1521

PostgreSQL - 5432

user

The user name. If omitted can be specified in the URL (for some databases) or the properties.

password

The password. If omitted, it can be specified in the URL (for some databases) or the properties.

encrypted_password

Encrypted version of password (encrypted using moog_encryptor).

properties

A map of key-value pairs of properties to specify the connection properties. For example, loginTimeout for SQL Server or useCompression for MySQL.

jar_files

A list of the files locations indicating the the JDBC driver jar file location.

Defaults:

SQL Server - sqljdbc4.jar

DB2 - db2jcc4.jar

Oracle - ojdbc6.jar

PostgreSql - postgresql-9.3-1102.jdbc41.jar

Assumes it will find these files in $MOOGSOFT_HOME/lib/cots/

They are not bundled in a regular Moogsoft AIOps installation.

class_name

The name of the JDBC class. Defaults:

SQL Server - com.microsoft.sqlserver.jdbc.SQLServerDriver

MySQL - com.mysql.jdbc.Driver

DB2 - com.ibm.db2.jcc.DB2Driver

Oracle - oracle.jdbc.OracleDriver

PostgreSql - org.postgresql.Driver

URL

JDBC specific URL. If specified, it can override other properties.

pool_properties

A map of key-value pairs of properties of the connection pool that will be created. It may be used to define the number of connections made available to the external database by including the pool_size key.

pool_size

The number of connections in the pool. Must be 1 or more, defaults to 10. Generally, this should match the number of threads configured to run the Moobot.

Note: You can also define connection properties in the following configuration file: moog_external_db_details.conf

Return Parameter

Type

Description

Object

A Java object containing connection details, depending on the requested connection properties

Returns null if no connection is available (due to either misconfiguration or unavailability of the external database).

Notes:

The connect method can accept a single parameter with connection properties, or two parameters - one with the generic connection properties and one specific for this connection. For example:

var customersConnection = externalDb.connect(dbTypes.customers);

will connect to the customer database as is.

var customersConnection = externalDb.connect(dbTypes.customers, {user: 'admin', password: 'wrdPass'});

will connect to the same database as the ‘admin’ user, with the password supplied.

You can also use the name from the following configuration file: moog_external_db_details.conf

Before making a connection, make sure the relevant database JDBC connector jar(s) are located where the configuration indicates. These are usually available for download from the database vendor.

Using the database connection:

The connection variable is a virtual connection, with the actual connections held and managed within the Java Virtual Machine. Therefore, there is no need to manage the connection, just call the connect method before you need to use the actual connection.

externalDb.execute()

Performs an SQL update to the database.

The execute method has one string argument:

Request Argument

Name

Type

Description

<argument>

String

SQL string argument.

Return Parameter

Type

Description

Boolean

Indicates if the operation was successful: true = success, false = fail.

Example

employeesConnection.execute('Update pets set species="dog" where species null');
externalDb.query()

Performs an SQL query on the external database.

The query method has one string argument:

Request Argument

Name

Type

Description

argument

String

SQL string argument.

Return Parameter

Type

Description

Object

A Java object which can contain the sub methods listed below

Returns null if the query fails.

Response Methods

Name

Description

rows()

Return the number of rows.

next()

Return the next row.

rewind()

Go back to the first row.

hasNext()

Indicate whether the current row is the last one.

row(i)

Return row i (zero based index).

first()

Return the first row.

type(name)

Return the type of column called name (or null if no such column exists).

columnName(i)

Return the name of column i (zero based index).

isNumber(name)

Returns true if the column name is a numeric column.

isString(name)

Returns true if the column name is a not numeric.

Row Methods

Name

Description

value(name)

Return the value for column named name as a string.

columns()

Return the number of columns.

rewind()

Go back to the first column.

hasNext()

Indicate whether the current column is the last one.

next()

Return the value in the next column as a string.

column(i)

Return the value in column i as a string.

first()

Return the value in the first column as a string.

last()

Return the value in the last column as a string.

Example

var customers = customersConnection.query('Select * from customers');
while(customers.hasNext()==true)
{
    var customer=customers.next();
    var firstName = customer.value("first_name");
    var lastName = customer.value("last_name");
    logger.info(firstName + " " + lastName +" is a customer");
}
externalDb.prepare()

Perform more complicated SQL queries or updates.

For example, you may need to reuse the same SQL statement with different arguments more than once, or you may need to use external data within the statement (and want to avoid SQL injection).

The prepare method has one string argument, where ? can be used to define parameters within the SQL.

Request Argument

Name

Type

Description

<argument>

String

SQL string argument.

Return Parameter

Type

Description

Object

A prepared SQL statement object which can contain the following sub-methods listed below.

Response Methods

Name

Description

set(i, value)

Set parameter i (1 based index) to a value. Returns false in case of a failure.

bind(value1, value2, value3,...)

Set parameter 1 to value 1, parameter 2 to value 2 and so on. Returns false in case of a failure in either one.

bindCount()

Return the number of parameters needed to bind. Some vendors might not support this method in all cases, in case it is not supported ‘-1’ is returned.

execute(value1, value2, value3,...)

Set parameter 1 to value 1, parameter 2 to value 2 and so on, and then execute the prepared statement. Returns false in case of a failure in one of the stages. If value are omitted will use the previously set or bind.

query(value1, value2, value3,...)

Set parameter 1 to value 1, parameter 2 to value 2 and so on, and then perform the query with the prepared statement. Returns null in case of a failure in one of the stages. Returns a Result Set (as the one in query above) if the operation was successful. If values are omitted, use the previously set or bind.

close()

Close the prepared statement. Note: It is important to close the statement with this method when no longer needed.

Example

The following will set all pet species to “dog” if the breed is one of a specific dog breed:

var petsChange = employeesConnection.prepare('Update pets set species=? where breed = ?');
petsChange.set(1, 'dog');
for (var breed in ['Labrador', 'Terrier', 'Beagle', 'Boxer', 'Poodle'])
{
    petsChange.set(2, breed);
    petsChange.execute();
}
petsChange.close();

[empty]

Database Specific Information
Downloading JDBC Drivers

Note:

  1. Be sure to download the correct version of the JDBC Driver for your database.

  2. Ensure downloaded JDBC drivers are moved/copied to the $MOOGSOFT_HOME/lib/cots directory.

Microsoft SQL Server

JDBC driver: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

Connection properties: http://technet.microsoft.com/en-us/library/ms378672(v=sql.110).aspx

Example declarations:

testdb: 
{
        type: 'sqlServer',
        host: '172.16.87.248',
        port: '1433',
        database: 'moog',
        user: 'sa',
        password: 'password'
}

or:

testdb: 
{
        jar_files: ["/usr/share/moogsoft/lib/cots/sqljdbc4.jar"],
        class_name: "com.microsoft.sqlserver.jdbc.SQLServerDriver",
        url: "jdbc:sqlserver://172.16.87.248:1433;databaseName=moog",
        properties: { user: "sa", password: "password" }
}
MySQL

JDBC Driver: Already included in Moogsoft AIOps - no need to download.

Connection properties: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

Example declarations:

testdb: 
{
        type: 'mySql',
        host: '172.16.87.247',
        port: '3306',
        database: 'moog',
        user: 'root',
        password: 'm00gsoft'
}

or:

testdb: 
{
        jar_files: ["/usr/share/moogsoft/lib/cots/mysql-connector-java-5.1.37-bin.jar"],
        class_name: "com.mysql.jdbc.Driver",
        url: "jdbc:mysql://172.16.87.247:3306/moog",
        properties: { user: "root", password: "m00gsoft" }
}
IBM DB2

JDBC Driver: http://www-01.ibm.com/support/docview.wss?uid=swg21363866

Connection properties: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.1.0/com.ibm.db2.udb.apdv.java.doc/doc/tjvjcccn.htm?cp=SSEPGG_9.1.0%2F8-1-4-2-1-0

Example declarations:

testdb: 
{
        type: 'db2',
        host: '172.16.87.248',
        port: '50000',
        database: 'moog',
        user: 'db2admin',
        password: 'm00gsoft'
}

or:

testdb: 
{
        jar_files: ["/usr/share/moogsoft/lib/cots/db2jcc4.jar"],
        class_name: "com.ibm.db2.jcc.DB2Driver",
        url: "jdbc:db2://172.16.87.248:50000/moog",
        properties: { user: "db2admin", password: "m00gsoft" }
}
Oracle

JDBC Driver: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Connection properties: http://docs.oracle.com/cd/B28359_01/java.111/b31224/urls.htm

Example declarations:

testdb: 
{
        type: 'oracle',
        host: '172.16.87.248',
        port: '1521',
        database: 'moog',
        user: 'System',
        password: ‘2pass’
}

or:

testdb: 
{
        jar_files: ["/usr/share/moogsoft/lib/cots/ojdbc6.jar"],
        class_name: "oracle.jdbc.OracleDriver",
        url: "jdbc:oracle:thin:System/m00gsoft@172.16.87.248:1521:moog"
}
PostgreSql

JDBC Driver: https://jdbc.postgresql.org/download.html

Connection properties: http://jdbc.postgresql.org/documentation/head/connect.html

Example declarations:

testdb: 
{
        type: 'postgresql',
        host: '172.16.87.248',
        port: '5432',
        database: 'moog',
        user: 'anotherUser',
        password: ‘password’
}

or:

testdb: 
{
        jar_files: ["/usr/share/moogsoft/lib/cots/postgresql-9.3-1102.jdbc41.jar"],
        class_name: "org.postgresql.Driver",
        url: "jdbc:postgresql://172.16.87.248:5432/moog",
        properties: { user: "anotherUser", password: "password" }
}