Logstash input jdbc :unknown error:

[Olatunde_Tokun]

Hey,

I use multi pipeline to ingest logs from a database using jdbc driver. It works great, but every once (once in 2 hours) in a while I see errors from the jdbc input plugin.

There are no missing events because it uses a timebased sql_last_value, but im curious to know why there are these errors and how it can be resolved.

[2018-06-05T12:34:02,702][ERROR][logstash.inputs.jdbc     ] Java::JavaSql::SQLException: unknown error: SELECT client_application_id, client_application_name, FROM_UNIXTIME(connection_sec), HEX(dst_ipaddr) as dst, dst_port, event_id, FROM_UNIXTIME(event_time_sec) as rt, impact, interface_egress_name, interface_ingress_name, protocol_name, protocol_num, priority, rule_classification, rule_classification_id, rule_message as name, rule_signature_id, rule_generator_id, security_zone_egress_name, security_zone_ingress_name, sensor_name, sensor_address, HEX(src_ipaddr) as src, src_port, src_user_name, vlan_id FROM table WHERE FROM_UNIXTIME(connection_sec)>'2018-06-05 16:33:01.814906'

The input config file is below:
input {
jdbc {
jdbc_connection_string => "jdbc:vjdbc:rmi://server:port/VJdbc,eqe"
jdbc_validate_connection => false
jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/vjdbc.jar,/usr/share/logstash/logstash-core/lib/jars/commons-logging-1.1.jar"
jdbc_driver_class => "com.technology.vjdbc.VirtualDriver"
jdbc_default_timezone => "Etc/UTC"
schedule => "* * * * *"
clean_run => false
last_run_metadata_path => "/usr/share/logstash/config/.lastrun_intrusion"
add_field => { "source" => "tablename" }
tracking_column_type => "timestamp"
statement => "SELECT client_application_id, client_application_name, FROM_UNIXTIME(connection_sec), HEX(dst_ipaddr) as dst, dst_port, event_id, FROM_UNIXTIME(event_time_sec) as rt, impact, interface_egress_name, interface_ingress_name, protocol_name, protocol_num, priority, rule_classification, rule_classification_id, rule_message as name, rule_signature_id, rule_generator_id, security_zone_egress_name, security_zone_ingress_name, sensor_name, sensor_address, HEX(src_ipaddr) as src, src_port, src_user_name, vlan_id FROM table WHERE FROM_UNIXTIME(connection_sec)>:sql_last_value"
}
}

Thanks in advance.

[Olatunde_Tokun]

Below is running LS in debug mode. Though not sure how Im supposed to fix this. Is this happening because LS is trying to count the amount of rows to be added? Any ideas or pointers would be appreciated.

[DEBUG MODE]

[2018-06-05T15:59:02,781][ERROR][logstash.inputs.jdbc ] Java::JavaSql::SQLException: parse error: unexpected keyword [SELECT]: SELECT count(*) AS "COUNT" FROM (SELECT application_name, client_application_id, client_application_name, FROM_UNIXTIME(connection_sec), detection_name, detector_type, direction, disposition, domain_name, HEX(dst_ipaddr) as dst, dst_port, event_description, event_id, event_type, file_name, file_path, file_sha, file_size, file_timestamp, file_type, http_response_code,score/100 as severity, sensor_name as dvchost, HEX(src_ipaddr) as src, src_port, threat_name, timestamp as rt, url, username FROM table1 WHERE FROM_UNIXTIME(connection_sec)>'2018-06-05 19:48:01.625947') AS "T1" LIMIT 1

[2018-06-05T15:59:02,795][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: parse error: unexpected keyword [SELECT]>}
[2018-06-05T15:59:02,987][ERROR][logstash.inputs.jdbc ] Java::JavaSql::SQLException: parse error: unexpected keyword [SELECT]: SELECT count(*) AS "COUNT" FROM (SELECT action, application_id, application_name, FROM_UNIXTIME(connection_sec), client_application_name, direction, disposition, domain_name, HEX(dst_ipaddr) as dst, dst_port, event_description, file_name, file_sha, file_size, file_type, file_type_id, file_type_category, http_response_code, sensor_id, sandboxed, score/10 as severity, HEX(src_ipaddr) as src, src_port, sensor_address as dvc, sensor_name, threat_name, policy_uuid, url, username, user_id FROM table2 WHERE FROM_UNIXTIME(connection_sec)>'2018-06-05 19:48:01.642526') AS "T1" LIMIT 1

[2018-06-05T15:59:02,992][ERROR][logstash.inputs.jdbc ] Java::JavaSql::SQLException: parse error: unexpected keyword [SELECT]: SELECT count(*) AS "COUNT" FROM (SELECT access_control_policy_name, access_control_rule_name, application_protocol_name, blocked, client_application_id, client_application_name, FROM_UNIXTIME(connection_sec), HEX(dst_ipaddr) as dst, dst_port, event_id, FROM_UNIXTIME(event_time_sec) as rt, impact, interface_egress_name, interface_ingress_name, protocol_name, protocol_num, priority, rule_classification, rule_classification_id, rule_message as name, rule_signature_id, rule_generator_id, security_zone_egress_name, security_zone_ingress_name, sensor_name, sensor_address, HEX(src_ipaddr) as src, src_port, src_user_name, vlan_id FROM table3 WHERE FROM_UNIXTIME(connection_sec)>'2018-06-05 19:48:01.580091') AS "T1" LIMIT 1