JDBC Input file format


#1

I am wondering what the proper format is for multiple JDBC queries. Do you need a different input file for each JDBC call or one file with multiple entries. I have tried both and the multiple entry does not seem to work.

Below is an example of the multi entry-single file input. I also tried adding the JDBC at the beginning of the second query section and that also failed.

input {
jdbc {
jdbc_driver_library => "/etc/logstash/conf.d/JDBC/mysql-connector-java-5.1.29.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306"
jdbc_user => root
jdbc_password => "Password"
schedule => "*/5 * * * "
statement_filepath => "/etc/logstash/conf.d/SQL_QUERY/Location"
type => "JDBC"
add_field => {"query_type" => "Locations"}
}
{
jdbc_driver_library => "/etc/logstash/conf.d/JDBC/mysql-connector-java-5.1.29.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306"
jdbc_user => root
jdbc_password => "Password"
schedule => "
/10 * * * *"
statement_filepath => "/etc/logstash/conf.d/SQL_QUERY/Devices"
type => "JDBC"
add_field => {"query_type" => "Devices"}
}
}


(Magnus Bäck) #2

Please define "doesn't work". What appears to be happening? Check the Logstash logs and increase the verbosity if needed.


#3

By not working the agent fails to start. error log shows a bad config.
{:timestamp=>"2016-12-21T08:53:07.542000-0600", :message=>"fetched an invalid config", :config=>"filter {\n mutate {\n add_field => {\n "mnemonic" => "TTY_TY"\n "client_domain" => "NONPROD"\n }\n }\n}\n\ninput {\n heartbeat {\n message => "1"\n interval => 120\n type => "heartbeat"\n }\n}\n\ninput {\n file {\n path => ["/ibus//logs/_noContext_Current.log"]\n exclude => "ThisMonitor.log"\n add_field => {"service_type" => "MyService"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/bus//logs/DOMAIN_..log"]\n exclude => ".DI.log"\n add_field => {"service_type" => "StartUp"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/bus/**/logs/BusMonitor.log"]\n add_field => {"service_type" => "BusMonitor"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/ibus//logs/DIDOMAIN_.DI.log"]\n exclude => [".DIMgr.log", ".DIAD*.log"]\n add_field => {"service_type" => "Broker"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/bus//logs/DOMAIN_Mgr.log"]\n add_field => {"service_type" => "Manager"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/bus/**/logs/DOMAIN_DIAD*.log"]\n add_field => {"service_type" => "Activation_Daemon"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n file {\n path => ["/mysql/data*/.err"]\n add_field => {"service_type" => "MySQL"}\n type => "Bus"\n }\n file {\n path => ["/bus/dusscripts/sockets/.log"]\n add_field => { "service_type" => "MyService"}\n type => "Bus"\n codec => multiline {\n pattern => "^\["\n negate => true\n what => "previous"\n }\n }\n}\n\ninput {\n jdbc {\n jdbc_driver_library => "/etc/logstash/conf.d/JDBC/mysql-connector-java-5.1.29.jar"\n jdbc_driver_class => "com.mysql.jdbc.Driver"\n jdbc_connection_string => "jdbc:mysql://localhost:3306"\n jdbc_user => root\n jdbc_password => "password"\n schedule => "*/5 * * * "\n statement_filepath => "/etc/logstash/conf.d/SQL_QUERY/Location"\n type => "JDBC"\n add_field => {"query_type" => "Locations"}\n }\n {\n jdbc_driver_library => "/etc/logstash/conf.d/JDBC/mysql-connector-java-5.1.29.jar"\n jdbc_driver_class => "com.mysql.jdbc.Driver"\n jdbc_connection_string => "jdbc:mysql://localhost:3306"\n jdbc_user => root\n jdbc_password => "password"\n schedule => "/10 * * * "\n statement_filepath => "/etc/logstash/conf.d/SQL_QUERY/Devices"\n type => "JDBC"\n add_field => {"query_type" => "Devices"}\n }\n}\n\noutput {\n rabbitmq {\n exchange => "elk"\n exchange_type => "direct"\n host => "111.141.81.11"\n ssl => true\n durable => true\n key => "logstash"\n port => 1234\n user => "superuser"\n password => "password"\n }\n}\n\n", :reason=>"Expected one of #, if, ", ', } at line 111, column 5 (byte 2426) after input {\n jdbc {\n jdbc_driver_library => "/etc/logstash/conf.d/JDBC/mysql-connector-java-5.1.29.jar"\n jdbc_driver_class => "com.mysql.jdbc.Driver"\n jdbc_connection_string => "jdbc:mysql://localhost:3306"\n jdbc_user => root\n jdbc_password => "password"\n schedule => "/5 * * * *"\n statement_filepath => "/etc/logstash/conf.d/SQL_QUERY/Location"\n type => "JDBC"\n add_field => {"query_type" => "Locations"}\n }\n ", :level=>:error}


(Magnus Bäck) #4

You have

jdbc {
  ...
}
{
  ...
}

but it should be

jdbc {
  ...
}
jdbc {
  ...
}

(system) #5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.