Moogsoft Docs

ExternalDb

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

Moogsoft AIOps can use the ExternalDb module to retrieve information for use in alerts and Situations and it can also update information in external databases with information from Moogsoft AIOps.

ExternalDb is available to load into any standard Moobot. To use, define a new global object externalDb at the top of a Moobot JavaScript file:

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

where externalDb is the name of the database.

Reference Guide

The following methods are applicable to the ExternalDb Moobot module:

connect

Establishes a 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 database type. If the 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. Defaults to localhost.

database

The database name.

port

The port number. Default values:

  • MySQL: 3306
  • SQL Server: 1433
  • DB2: 50000
  • Oracle: 1521
  • PostgreSQL: 5432
user

The username to connect to the database. If omitted it can be specified in the URL (for some databases) or the properties.

password

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

encrypted_password

Encrypted version of password. See Moog Encryptor for more information.

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. Default values:

  • SQL Server: sqljdbc4.jar
  • DB2: db2jcc4.jar
  • Oracle: ojdbc6.jar
  • PostgreSQL: postgresql-9.3-1102.jdbc41.jar

The default location is $MOOGSOFT_HOME/lib/cots . These files are not bundled in a regular Moogsoft AIOps 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. 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. This should generally 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.

execute

Performs a SQL update to the database.

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

query

Performs a SQL query on the external database.

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

Returns the number of rows.

next

Returns the next row.

rewind

Goes back to the first row.

hasNext

Indicate 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

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

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 complex 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).

Request Argument

Name

Type

Description

<argument>

String

SQL string argument. A question mark can be used to define parameters within the SQL.

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)

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

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

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

bindCount()

Returns 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,...)

Sets 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,...)

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

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

Example

The following example sets 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();

Database Specific Information

Downloading JDBC Drivers

Note the following when 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

See the following for more information on JDBC drivers and connection properties:

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 is already included in Moogsoft AIOps. There is no need to download it.
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" }
}