Skip to main content

jdbc input plug-in

Meet JDBC​

  • Tested versions: 5.1.8
  • Developed by Elastic
  • Configuration instructions can be found on every relevant filter plugin readme page. For example: MSSQL
  • Supported Guardium versions:
    • Guardium Data Protection: 11.3 and above
      • Guardium Insights: coming soon

This is a Logstash input plug-in for the universal connector that is featured in IBM Security Guardium. It connects to the Database using driver library and pull the events from the audit log tables. The events are then sent over to corresponding filter plugin which transforms these audit logs into a Guardium record instance (which is a standard structure made out of several parts). The information is then sent over to Guardium. Guardium records include the accessor (the person who tried to access the data), the session, data, and exceptions. If there are no errors, the data contains details about the query "construct". The construct details the main action (verb) and collections (objects) involved.

Purpose:​

Specify the "Select" query with the required parameters, to pull the Events from the audit tables, and this plugin will pull the events into Guardium.

Usage:​

Parameters:​

ParameterInput TypeRequiredDefault
jdbc_driver_classStringYes
jdbc_connection_stringStringYes
jdbc_userStringYes
jdbc_passwordStringYes
statementStringYes
use_column_valueStringYes
tracking_column_typeStringYesnumeric
tracking_columnStringYes
last_run_metadata_pathStringYes"$HOME/.logstash_jdbc_last_run"
scheduleStringYes
jdbc_paging_enabledBooleanNofalse
jdbc_page_sizeNumberNo100000

jdbc_driver_class​

The jdbc_driver_class setting allows to set JDBC driver class to load, for example, "org.apache.derby.jdbc.ClientDriver"

jdbc_connection_string​

The jdbc_connection_string setting allows to set the JDBC connection string, for example, "jdbc:sqlserver://<server_name>:;databaseName=<database_name>"

jdbc_user​

The jdbc_user setting allows to set the username used to connect to the database that has access to the Audit tables to be queried.

jdbc_password​

The jdbc_password setting allows to set the password for the above jdbc_user.

statement​

The statement setting allows to set the SELECT query using which the Audit tables are queried for the audit logs.

use_column_value​

The use_column_value setting when set to true, uses the defined tracking_column value as the :sql_last_value. When set to false, :sql_last_value reflects the last time the query was executed.

tracking_column_type​

The tracking_column_type setting allows to set the type of tracking column. Currently only "numeric" and "timestamp"

tracking_column​

The tracking_column setting allows to set the column whose value is to be tracked if use_column_value is set to true

last_run_metadata_path​

The last_run_metadata_path setting allows to set the path to file with last run time

schedule​

The schedule setting allows to set the schedule of when to periodically run statement, in Cron format for example: "* * * * *" (execute query every minute, on the minute). There is no schedule by default. If no schedule is given, then the statement is run exactly once.

jdbc_paging_enabled​

The jdbc_paging_enabled setting will cause a sql statement to be broken up into multiple queries. Each query will use limits and offsets to collectively retrieve the full result-set. The limit size is set with jdbc_page_size.

jdbc_page_size​

The jdbc_page_size setting will set the JDBC page size

Note: For moderate to large amounts of data, include pagination to facilitate the audit and to avoid out of memory errors. Use the parameters below in the input section when using a JDBC connector, and remove the concluding semicolon ';' from the jdbc statement: jdbc_paging_enabled => true jdbc_page_size => 1000

Logstash Default config params​

Other standard logstash parameters are available such as:

  • add_field
  • type
  • tags

Example​

input &#123;
	jdbc &#123;
		jdbc_driver_class => "com.sap.db.jdbc.Driver"
		jdbc_connection_string => "jdbc:sap://<server-name>:<db-port-number>/?databaseName=<db-name>&user=<user_name>&password=<password>"
		jdbc_user => "<user_name>"
		jdbc_password => "<password>"
		statement => "SELECT <parameters> FROM <Audit_tables> WHERE <tracking_column> > :sql_last_value;"
		use_column_value => true
		tracking_column_type => "numeric"
		tracking_column => "new_timestamp"
		last_run_metadata_path => "./.example_logstash_jdbc_last_run"
		type => "test"
		schedule => "*/2 * * * *"
	&#125;
&#125;

JDBC Load Balancing and Failover Configuration​

Guardium Insights​

To enable Load Balancing, set a weight property to 1 in the jdbc_connection_string field URL as seen below:

jdbc_connection_string => "jdbc:sqlserver://[serverName[\instanceName][:portNumber]];weight=1;databaseName=<db_name>;user=<usr_name>;password=<pwd>!;"

Add the following condition to the query encoded in the statement field to the WHERE clause:

SELECT <parameters> FROM <Audit_tables> WHERE (session_id % 2 = 0) <tracking_column> > :sql_last_value;

Note: this will prevent deduplication completely when there are 2 pods in the set.

Note: since all configurations are equal for all pods per connection added, there's no option to enable failover

Guardium Data Protection​

To enable load balancing, set a weight property. To enable failover, set a priority property as in the following jdbc_connection_string URL:

jdbc_connection_string => "jdbc:sqlserver://[serverName[\instanceName][:portNumber]];weight=<NUM>;priority=<PRIO>;databaseName=<db_name>;user=<usr_name>;password=<pwd>!;"

priority - this parameter is the priority of the target host. A lower value indicates a more preferred priority. The range is 0 to 65535.

weight - this parameter is a relative weight between 0 and 65535 for records with the same priority.

Procedure​

  1. On the first G Machine, in the input section for JDBC Plug-in, update the "statement" field as follows:

    SELECT <parameters> FROM <Audit_tables> WHERE mod(PK_column,2) = 0 and <tracking_column> > :sql_last_value;

  2. On the second G machine, in the input section for the JDBC Plug-in, update the "statement" field as follows:

    SELECT <parameters> FROM <Audit_tables> WHERE mod(PK_column,2) = 1 and <tracking_column> > :sql_last_value;