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 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 MOOG AIOps install.

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.

^ Back to Methods

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

^ Back to Methods

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

^ Back to Methods

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

^ Back to Methods

Database specific information

Important Notes on downloading JDBC Drivers

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