ExternalDb

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

  • MySQL

  • Microsoft SQL Server

  • IBM DB2

  • Oracle

  • PostgreSQL

Using ExternalDb, Moogsoft Enterprise can retrieve information from external databases for use in alerts and Situations. The ExternalDb method can also update external databases with information from Moogsoft Enterprise.

Load the module

The ExternalDb Moobot module is available to load into any standard Moobot. To load it, define a new global variable at the top of the Moobot Javascript file. For example:

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

Method reference

The ExternalDb module uses the following methods.

connect

Establishes a connection to an external database with defined connection properties.

Request arguments

The method takes the following arguments.

Name

Type

Required

Description

properties

Object

Yes

A Javascript object containing connection properties. See Database connection properties below.

Database connection properties

The properties object is a Javascript object that can contain the following keys. You can also define connection properties in the file moog_external_db_details.conf.

Key

Description

type

Database type. If you omit type 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

Database host name or IP address. Default is localhost.

database

Database name.

port

Port number. Default values:

MySQL: 3306

SQL Server: 1433

DB2: 50000

Oracle: 1521

PostgreSQL: 5432

user

Username to connect to the database. If omitted you can specify it in the URL (for some databases) or the properties.

password

Password to connect to the database. If omitted you can specify it in the URL (for some databases) or the properties.

encrypted_password

Encrypted version of the password.

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

The JDBC driver jar file locations. Default values:

SQL Server: sqljdbc4.jar

DB2: db2jcc4.jar

Oracle: ojdbc6.jar

PostgreSql: postgresql-9.3-1102.jdbc41.jar

The assumed location is $MOOGSOFT_HOME/lib/cots/. These files are not bundled in a standard Moogsoft Enterprise installation.

class_name

The name of the JDBC class. Default values:

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. You can define the number of connections made available to the external database by including the pool_size key.

pool_size: Number of connections in the pool. Must be 1 or more. Default is 10. Generally, this should match the number of threads configured to run the Moobot.

Response

The method returns the following parameter.

Type

Description

Object

A Java object containing the connection details. Returns null if the connection fails.

Examples

The method can accept a single parameter with connection properties, or two parameters: one with the generic connection properties and one specific to this connection.

The following command establishes a connection using the details in the customers object:

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

The following command establishes a connection using the same details, but the username and password in the object are overridden by the ones provided in the command:

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

execute

Performs an SQL update on the external database.

Request arguments

The method takes the following arguments.

Name

Type

Required

Description

argument

String

Yes

SQL string argument containing the update.

Response

The method returns the following parameter.

Type

Description

Boolean

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

Examples

The following example uses the execute method to update the species column in a pets database.

employeesConnection.execute('update pets set species="dog" where species null');

query

Performs an SQL query on the external database.

Request arguments

The method takes the following arguments.

Name

Type

Required

Description

argument

String

Yes

SQL string argument containing the query.

Response

The method returns the following parameter.

Type

Description

Object

A Java object containing the query results. Returns null if the connection fails.

Response methods

The response can contain the following methods.

Name

Description

rows

Returns the number of rows.

next

Returns the next row.

rewind

Goes back to the first row.

hasNext

Indicates whether the current row is the last one.

row(i)

Returns row i (zero based index).

first

Returns the first row.

type(name)

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

columnName(i)

Returns 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

You can use the following row methods in the query.

Name

Description

value(name)

Returns the value for column named name as a string.

columns

Returns the number of columns.

rewind

Goes back to the first column.

hasNext

Indicates whether the current column is the last one.

next

Returns the value in the next column as a string.

column(i)

Returns the value in column i as a string.

first

Returns the value in the first column as a string.

last

Returns the value in the last column as a string.

Example

The following example uses the query method to query the customers database.

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");
}

prepare

Performs SQL queries and updates on the external database. You can use the prepare method for complicated operations. For example, you can reuse the same SQL statement with different arguments, and you can use external data within a statement.

Request arguments

The method takes the following arguments.

Name

Type

Required

Description

argument

String

Yes

SQL string argument containing the query or update.

Response

The method returns the following parameter.

Type

Description

Object

A Java object containing the query results. Returns null if the connection fails.

Response methods

The response object can contain the following methods.

Name

Description

set(i, value)

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

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

Sets parameter 1 to value 1, parameter 2 to value 2 and so on. Returns false in case of failure.

bindCount

Returns the number of parameters needed to bind. Some vendors do not support this method in all cases, if not supported -1 is returned.

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

Sets parameter 1 to value 1, parameter 2 to value 2 and so on, and then executes the prepared statement. Returns false in case of a failure in one of the stages. If values are omitted, uses the previously set or bind.

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

Sets parameter 1 to value 1, parameter 2 to value 2 and so on, and then performs the query. Returns null in case of failure. Returns a result set if the operation was successful. If values are omitted, uses the previously set or bind.

close

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

Example

The following example uses the prepare method to set all pet species to "dog" if the breed is one of those specified:

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();

Database drivers and declarations

When downloading JDBC drivers:

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

  2. Copy or move the downloaded drivers to $MOOGSOFT_HOME/lib/cots/.

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: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

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=my_db",
    properties: { user: "myuser", password: "mypassword" }
}

MySQL

JDBC driver: Already included in Moogsoft Enterprise - 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: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

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/my_db",
    properties: { user: "myuser", password: "mypassword" }
}

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: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

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/my_db",
    properties: { user: "myuser", password: "mypassword" }
}

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: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

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

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: 'my_db',
    user: 'myuser',
    password: 'mypassword'
}

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/my_db",
    properties: { user: "myuser", password: "mypassword" }
}