Moogsoft Docs

JDBC

Java Database Connectivity ( JDBC ) is an application programming interface (API) for the programming language Java. It defines a database access mechanism for a client. It's a Java based data access technology and is used for Java database connectivity. It is a part of Java Standard Edition platform from the Oracle Corporation . It provides methods to query and update data in a database, and is oriented towards relational databases . The JDBC Integration (LAM) connects with a JDBC enabled database and fetches events from it.

  1. The JDBC LAM reads the configuration from the jdbc_lam.conf file.
  2. It connects with the specified database provided all the required connection parameters are listed and valid.
  3. It retrieves records from the specified table as per defined filters.
  4. The records are converted to JSON and then passed to Lambot.
  5. The Lambot converts the records to Moog events and passes them to Moog.
  6. The last value of indicator field is persisted in a state file.

Configuration

The records received from JDBC are processed according to the configurations in the jdbc_lam.conf file. The processed records are published to Moogsoft AIOps.

The configuration file contains a JSON object. At the first layer of object, LAM has a parameter called config , and the object that follows config has all the necessary information to control the LAM.

The JDBC LAM configuration file has the following sections:

Monitor

Agent

HA Configuration

Mapping

Constants and Conversions

Monitor

The JDBC LAM accesses the records from a JDBC enabled database. You can configure the parameters here to establish a connection with the JDBC:

General

Field
Type
Description
Example
name and class String

Reserved fields: do not change. Default values are JDBC Lam Monitor and CJdbcMonitor.


type String

The type of the database used. This is a mandatory field. It can be either MySQL, SQL Server, DB2, oracle or postgreSQL.

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 String The host name or the IP address of the Machine where the database server is running. The default host is localhost.
port Integer The port on which the database service is running. Default port values are:
MySQL - 3306
SQL Server - 1433
DB2 - 50000
Oracle - 1521
PostgreSQL - 5432

database String Name of the database where the lam will connect. This is a mandatory field.
user and Password String

Enter the username and password of the database server. If username and password is mentioned in the URL, then you don't have to specify it here.

If a username and password is specified at both the places, then their values will get overwritten.


encrypted_password String

If the encrypted password is to be used, then enter the encrypted password in this field and comment the password field. At a time, either the password or the encrypted_password field is used. If both the fields are not commented, then the field encrypted_password will be used by the JDBC LAM.


properties String

A mapping of key-value pairs of properties to specify the connection properties.
key1: 'val1',
key2: 'val2'

To enable SSL for MySQL:

useSSL : "true",
trustCertificateKeyStoreUrl : " file:///keystorefilename ",
trustCertificateKeyStorePassword : "password"

alias

String It can be any user defined name the LAM would use to identify the connection. This name has to be unique.


jar_files

String It is a list of file locations which indicates the JDBC driver jar file location.
Default values are:
SQL Server - sqljdbc4.jar
DB2 - db2jcc4.jar
Oracle - ojdbc6.jar
PostgreSql - postgresql-9.3-1102.jdbc41.jar
"/export/jdbcDrivers/postgresql-9.3-1102.jdbc41.jar"

class_name

String

The name of the JDBC driver class.

Default values are:
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 String

It's a fully constructed database connection url.

If url contains username and password, then you don't have to mention username and password attributes.

If url is not there in the config file, then you can mention type, host, port, and database information. Using this data, the LAM will construct the database connection url.

jdbc: " mysql://localhost:1321/customers "


type : "mysql",
host : "localhost",
port : "1321",
database : "customers",
Connection_order String

The connection order is mandatory when there is more than one database. Lam will pick alias names mentioned in the connection order one by one. First it will configure them and then on failover, it will iterate the configured connection order to establish a new connection.

If no alias is present under databases mentioned in the connection order, the Lam will fail.


table_name

String Enter the table name from the database from where you want to fetch the data.

indicator_column

Integer

A unique identifier for each polling cycle.  It should be of numeric type, else you have to specify the raw SQL. This forms the basis on which the updated events are fetched. The LAM will use the column mentioned here in a where clause along with the ">" operator.

  • The Jdbc_lam will look for the value of indicator_column in the jdbc_lam.state file.
  • If it finds any value in the .state file, then it will start polling the data from that point.
  • If it fails to find any value in the ‘.state’ file, then it will first fetch the max value of indicator_column from the table using the SQL Query: Select max(indicator_column) as indicator from tablename where filterclause; and then from the second poll , it will start fetching the data.

If you want the lam to start polling the data from a particular point, then you can create/modify .state file manually. See the .state file for your reference.

Download this state file and paste it in the config folder, and then you can enter the pointer value as per your preference.


Note

The state file is generated in the same folder where the config file is present e.g. $MOOGSOFT_HOME/config. The LAM generates the name of the state file as < proc_name>.state . Here the default proc_name (process name) is jdbc_lam , therefore, the state file name is jdbc_lam.state. proc_name is defined in the jdbc_lam.sh file located at $MOOGSOFT_HOME/bin .


where event_id > 0
indicator_column: "from_unixtime(myTimestampColumn)"

indicator_column : "convert(datetime, event_id)",

indicator_column : "event_id",

filter_clause

String This will enable the LAM to fetch more filtered data.

type like 'event' and error_type = 'syserror'

The filter clause will be wrapped in closing parenthesis and appended as an AND with the indicator_column clause.
filter_clause : "something > 2"
will be appneded to the core query as:
AND ( "something" > 2 )

flood_control Integer

JDBC provides a paging mechanism to return the result set in pages. This allows the large return data sets to be returned in manageable pages.

Flood_control determines the size of the pages,especially, the number of events the lam will process simultaneously.

page_size: This i ndicates the total number of records that are displayed on the current page.

The default page size is 100. If the specified value is less than 100, then it will switch to default.

interval: This is the time interval, in milliseconds, between requests.

The default value is 100. If the specified value is less than 1, then it will switch to default.


If the poll found 1000 rows, and the page_size was 100, and the interval is also set to 100, then the result set would be paged into 10 pages (1000/100) with an inter-page interval of 100 ms i.e. the entire result set of 1000 events would be passed through the lam in 1s (10 x 100ms).
polling_interval Integer

The polling time interval, in seconds, between the requests after which the event data is fetched from JDBC LAM.

Default = 10 seconds. If 0 is entered, the time interval is set to 10 seconds.


max_retries

Integer

The maximum number of retry attempts to reconnect with JDBC in case of a connection failure.

Default = -1, if no value is specified, then there will be infinite retry attempts. If the specified value is less than 1, then it will switch to default i.e. -1.

If the specified value is greater than 1, then the LAM will try that many times to reconnect.


retry_interval Integer

The time interval between two successive retry attempts.
Default = 60 seconds, if 0 is entered, the time interval is set to 60 seconds.


Note

The entry in the fields polling_interval , max_retries and retry_interval should be an integer, therefore enter the values in these fields without quotation marks.

Example

Config File
config:
    {

        monitor:
        {

            name                      : "JDBC Lam Monitor",

            class                     : "CJdbcMonitor",
			
			databases :
            {    "primary": {
                    type				: "mySql", 
                    host				: "localhost",
                    database			: "testdb", 
                    port				:  "3306",
                    user				: "user", 
                    password			: "password", 
                    # encrypted_password: 'X868Dl3TSJOlMC9GrdbchTygJtisAURGzhjWZKW53EA=', 
                    properties			: { 
                        					#key1: "val1", 
                        					#key2: "val2"
                    					   }
                			}	
                
                #Advanced Mode
                
                 #"alias": {
                	#jar_files			: ["/export/jdbcDrivers/postgresql-9.3-1102.jdbc41.jar"], 
                	#class_name			: "org.postgresql.Driver", 
                	#url				: "jdbc:mysql://localhost:3306/testdb", 
               		#properties			: {
                							#user				: "user", 
											#password			: "password",
                							#encrypted_password : ""o0Ht/tof0agM/BsTQvgUflfix6vMoa+ZyxvcK45I8dw=",
                							#key1				: "val1", 
                							#key2				: "val2"
               		 					 # }
                		#}
			} 
                
            connection_order 		   : ["alias", "alias1"],
			table_name                 : "tablename",

	        inidicator_column          : "event_id",

			filter_clause              : "",	

			flood_control			   :
				  {
			        page_size          : 100,
					interval    	   : 100, 
				  }

            polling_interval           : 10,

            max_retries                : -1, 

            retry_interval             : 60
		},

Database specific information

Microsoft SQL Server

Example declarations:

testdb: {
    type: "sqlServer",
    host: "localhost",
    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://localhost:1433;databaseName=moog",
    properties: { user: "sa", password: "password" }
}

MySQL

Example declarations:

testdb: {
    type: "mySql",
    host: "localhost",
    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://localhost:3306/moog",
    properties: { user: "root", password: "m00gsoft" ,useSSL }
}

IBM DB2

Example declarations:

testdb: {
    type: "db2",
    host: "localhost",
    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://localhost:50000/moog",
    properties: { user: "db2admin", password: "m00gsoft" }
}

Oracle

Example declarations:


testdb: {
    type: "oracle",
    host: "localhost",
    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@localhost:1521:moog"
}

PostgreSql

Example declarations:

testdb: {
    type: "postgresql",
    host: "localhost",
    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://localhost:5432/moog",
    properties: { user: "anotherUser", password: "password" }
}

Secure Sockets Layer

To enable SSL for any database, you have to specify the SSL properties for that particular database in the properties section of the config file.

Example properties for MySQL:

useSSL : "true",
trustCertificateKeyStoreUrl : " file:///keystorefilename ",
trustCertificateKeyStorePassword : "password"

Example properties for MS SQL Server:

encrypt:"true",
trustServerCertificate:"false",
trustStore: "truststorefilename",
trustStorePassword: "password"

Agent

Agent allows you to define two parameters:

Field

name This is the agent name, the events sent to MooMS by the JDBC are identified by the agent name in the log. In this example, the agent name is JDBC.
log JDBC LAM will write its ingress contents in the file jdbc_lam.log located at /var/log/moogsoft/.


Example

Agent
agent:
        {
                name    : "JDBC"
                #log    : "/var/log/moogsoft/jdbc_lam.log"
        },


HA Configuration

Refer the document HA Configuration of LAM

Mapping

Variable section is not required in the JDBC LAM, you can directly map events field of JDBC with Moogsoft fields displayed in the Moogsoft AIOps.

mapping :
          {
              catchAll: "overflow",
              rules:
              [
                  { name: "signature", rule:      "$signature" },
                  { name: "source_id", rule:      "$source_id" },
                  { name: "external_id", rule:    "$external_id" },
                  { name: "manager", rule:        "$manager" },
                  { name: "source", rule:         "$source" },
                  { name: "class", rule:          "$class" },
                  { name: "agent", rule:          "$LamInstanceName" },
                  { name: "agent_location", rule: "$agent_location" },
                  { name: "type", rule:           "$type" },
                  { name: "severity", rule:       "$severity", conversion: "stringToInt" },
                  { name: "description", rule:    "$description" },
                  { name: "agent_time", rule:     "$moog_now" }
              ]
          },
          filter:
          {            
              #stream: "myStream",
              presend:"JdbcLam.js"
          }
    }

The above example specifies the mapping of the JDBC alarm fields with the Moogsoft AIOps fields. Data not mapped to Moogsoft AIOps Fields goes into "Custom Info".

Note

The signature field is used by the LAM to identify correlated alarms.


Constants and Conversions

Constants and Conversions allows you to convert format of the received data.

Field
Description
Example
Severity and sevConverter has a conversion defined as sevConverter in the Conversions section, this looks up the value of severity defined in the severity section of constants and returns back the mapped integer corresponding to the severity.
severity:
{
    "clear"	         : 0,
    "info"          	 : 1,
    "warning"       	 : 2,
    "minor"         	 : 3,
    "major"         	 : 4,
    "critical"      	 : 5,
    moog_lookup_default  : 1
}, 
sevConverter:
{
    lookup : "severity",
    input  : "STRING",
    output : "INTEGER"
},
stringToInt used in a conversion, which forces the system to turn a string token into an integer value.
stringToInt:
{
    input  : "STRING",
    output : "INTEGER"
},
timeConverter used in conversion which forces the system to convert time. If epoch time is to be used, then timeFormat mentioned in timeConverter should be commented. Otherwise, the user should provide the timeFormat.
timeConverter:
{
    timeFormat : "yyyy-MM-dd'T'HH:mm:ss.SSS",
    input      : "STRING",
    output     : "INTEGER"
}

Example

Example Constants and Conversions
constants:
        {
            severity:
            {
            	"clear"				 : 0,
                "info"          	 : 1,
                "warning"       	 : 2,
                "minor"         	 : 3,
                "major"         	 : 4,
                "critical"      	 : 5,
			    moog_lookup_default  : 1
            }
           
        },
        conversions:
        {
            sevConverter:
            {
                lookup: "severity",
                input:  "STRING",
                output: "INTEGER"
            },
            stringToInt:
            {
                input:      "STRING",
                output:     "INTEGER"
            },
            timeConverter:
            {
                timeFormat: "yyyy-MM-dd'T'HH:mm:ss.SSS",
                input:      "STRING",
                output:     "INTEGER"
            }
        },


Severity Reference

Severity Levels
Moogsoft Severity Levels
severity:
        {
            "CLEAR" : 0,
            "INDETERMINATE" : 1,
            "WARNING" : 2,
            "MINOR" : 3,
            "MAJOR" : 4,
            "CRITICAL" : 5,
            moog_lookup_default: 3
        }
Level Description
0 Clear
1 Indeterminate
2 Warning
3 Minor
4 Major
5 Critical

Service Operation Reference

Process Name Service Name
jdbc_lam jdbclamd

Start the LAM Service:

service jdbclamd start

Stop the LAM Service:

service jdbclamd stop

Check the LAM Service status:

service jdbclamd status

Command Line Reference

To see the available optional attributes of the jdbc_lam, run the following command:


jdbc_lam --help

The jdbc_lam is a command line executable, and has the following optional attributes:

Option Description

--config

Points to a pathname to find the configuration file for the LAM. This is where the entire configuration for the LAM is specified.
--help Displays all the command line options.
--version

Displays the component’s version number.

--loglevel

Specifies the level of debugging. By default, user gets everything. In common with all executables in MOOG, having it set at that level can result in a lot of output (many messages per event message processed).

In all production implementations, it is recommended that log level is set to WARN. This ensures only warning, error and fatal messages are recorded.

Performance Information

Minimum requirement
Component Value
CPU 2 core
RAM 4 GB
Operating System CentOS Linux release 6.7

Version

Integration Version Database Version
1.0 mysql ver 14.14
Integration Version
JDBC API Version
1.0 JDBC 4.2 api version

Integration Version

Database Name Jar Version
1.0 MySQL mysql-connector-java-5.1.37.jar
1.0

PostgreSQL

postgresql-9.3-1102.jdbc41.jar
1.0 SQLServer sqljdbc4.jar
1.0 DB2 db2jcc4.jar
1.0 Oracle ojdbc6.jar