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.
The JDBC LAM reads the configuration from the
jdbc_lam.conffile.It connects with the specified database provided all the required connection parameters are listed and valid.
It retrieves records from the specified table as per defined filters.
The records are converted to JSON and then passed to Lambot.
The Lambot converts the records to Moogsoft Onprem events and passes them to the message bus.
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 Onprem.
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 |
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
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. NoteThe 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 |
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 Onprem fields displayed in the Moogsoft Onprem.
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 Onprem fields. Data not mapped to Moogsoft Onprem 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 Onprem 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 Onprem, 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. |