JDBC LAM

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 Moogsoft Enterprise events and passes them to the message bus.

  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.conffile. The processed records are published to Moogsoft Enterprise.

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.

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

name and class

String

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

target

JSON Object

A top-level container for which you can define one or more target JDBC sources. You can specify the configuration for each target. If you don't specify a request_interval the target uses the globally defined interval.

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

For example: "/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.

For example:

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.

Note

  • 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 filefor 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.

For example:

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. For example:

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 indicates 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.

For example: If the poll found 1000 rows, and the page_size was 100, and the interval is also set to 100, thenthe result set would be paged into 10 pages (1000/100) with an inter-page interval of 100ms 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.

request_interval

Integer

Length of time to wait between requests, in seconds. Can be overridden by request_interval in individual targets. Defaults to 60.

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

You can configure the JDBC LAM to retrieve events from one or more sources. The following example demonstrates a configuration that targets one JDBC source (target1). If you have more than one source, add a target section for each one and uncomment properties to enable them.

monitor:
    {
        name                                    : "JDBC Lam Monitor",
        class                                   : "CJdbcMonitor",
        request_interval                        : 60,
        max_retries                             : -1,
        retry_interval                          : 60,
        targets:
        {
            target1:
            {
                                databases:
                                {
                                        "alias":
                                        {
                                                type                            : "mysql", 
                        host                            : "localhost",
                        database                        : "testdb", 
                        port                            : "3306",
                        user                            : "user_jdbc", 
                        #password                       : "password", 
                        encrypted_password      : "X868Dl3TSJOlMC9GrdbchTygJtisAURGzhjWZKW53EA=", 
                        properties                      : 
                                                                                        { 
                                                                        #key1: "val1", 
                                                                        #key2: "val2"
                                                                }
                                        }
                                        "alias1":
                                        {
                                                jar_files                       : [ "/export/jdbcDrivers/postgresql-9.3-1102.jdbc41.jar" ], 
                                class_name                      : "org.postgresql.Driver", 
                                url                                     : "jdbc:mysql://localhost:3306/testdb", 
                        properties                      : 
                                                                                        { 
                                                                        user: "user_jdbc2", 
                                                                        password: "password"
                                                                }

                                        }
                                }
                        connection_order                        : [ "alias", "alias1" ],
                        table_name                                      : "tablename",
                indicator_column                        : "event_id",
                        filter_clause                           : "", 
                        flood_control                           :
                                                                                {
                                                                                page_size: 100,
                                                                                        interval: 100, 
                                                                                }
                        request_interval                        : 60,
                        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 and Process Log

The Agent and Process Log sections allow you to configure the following properties:

  • name: Identifies events the LAM sends to the Message Bus.

  • capture_log: Name and location of the LAM's capture log file.

  • configuration_file: Name and location of the LAM's process log configuration file.

Mapping

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

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 Enterprise fields. Data not mapped to Moogsoft Enterprise 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"
            }
        },

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

If the LAM fails to connect to one or more JDBC sources, Moogsoft Enterprise creates an alert and writes the details to the process log.

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 Moogsoft Enterprise, 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.