Hi,
I want to run a select query on a DB table so that I project the output on KIBANA DB.
I am not expertise in modules. Could you please help me here in achieving this.
Thanks,
Dinesh
That functionality is typically being done in Logstash with the JDBC Input plugin.
Do I need to install this plugin where my logstash is running?
I don't see anything in my plugins folder. can you help me here on steps I need to follow to achieve this please..
It comes installed by default. Well, what version are you running?
You do need to download the jdbc_driver_library for whatever database you are using though like in the below example for a mysql server.
input {
jdbc {
jdbc_driver_library => "mysql-connector-java-5.1.36-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
jdbc_user => "mysql"
parameters => { "favorite_artist" => "Beethoven" }
schedule => "* * * * *"
statement => "SELECT * from songs where artist = :favorite_artist"
}
}
I am using logstash-7.3.1 version.Where should I place mysql-connector-java-5.1.36-bin.jar?
Is it under /tibco/ELK/logstash-7.3.1/logstash-core/lib/jars path?
In my case it is Oracle DB.
You need to get the Oracle DB driver that matches your DB version. You can put that anywhere (most of the time) and just give the full path to the location.
I'd look up oracle logstash input examples and follow those. I posted mysql which is kind of different.
Thanks.. Can I get a template file for Orcale like the one you share fr mysql please?
I need to create the .conf file for this?
Hi,
I created .conf file ( see below and I see required output is not shown on kibana.
Can you please help me here and let me know whats wrong in my .conf file please.
I removed few fields values wich are nit be shared 
input {
jdbc {
jdbc_driver_library => "/tibco/ELK/logstash-7.3.1/logstash-core/lib/jars/ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => ""
jdbc_user =>
jdbc_password =>
schedule => "*/2 * * * *"
statement => "select * from ELK_JDBC"
#statement => "select ID,FIRSTNAME,ROLE from ELK_JDBC where ID >= :sql_last_value"
#tracking_column => "ID"
#tracking_column_type => "numeric"
#use_column_value => true
}
}
output {
elasticsearch {
index => "mm-eai-jdbcelk"
document_id => "%{ID}"
hosts =>
ssl => true
ssl_certificate_verification => true
cacert => 'ca.crt'
user => m
password =>
}
stdout { codec => rubydebug
}
}When you run this what does the stdout give you?
I updated my .conf file and in startup log I see below error. Also when there are mutliple rows not sure why it gives me only 1 row in kibana.
input {
jdbc {
jdbc_driver_library => "/tibco/ELK/logstash-7.3.1/logstash-core/lib/jars/ojdbc6.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
jdbc_connection_string => "jdbc:oracle:thin:@.local:8080:12323T"
jdbc_user => "123"
jdbc_password => "123"
schedule => "*/2 * * * *"
statement => "SELECT * from ELK_JDBC where ID >= :sql_last_value"
use_column_value => true
tracking_column => "ID"
tracking_column_type => "numeric"
clean_run => true
}
}
output {
elasticsearch {
index => "mm-eai-jdbc"
document_id => "%{ID}"
hosts => "https://org:443"
ssl => true
ssl_certificate_verification => true
cacert => 'ca.crt'
user => mm_eai_client
password => 123
}
stdout { codec => rubydebug
}
}
Error in Logs I Observed:
2020-11-12T16:10:49,745][ERROR][logstash.outputs.elasticsearch] Failed to install template. {:message=>"Got response code '403' contacting Elasticsearch at URL 'https://abc.org:443/_template/logstash'", :class=>"LogStash::Outputs::ElasticSearch::HttpClient::Pool::BadResponseCodeError", :backtrace=>["/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/manticore_adapter.rb:80:in perform_request'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/pool.rb:291:in perform_request_to_url'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/pool.rb:278:in block in perform_request'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/pool.rb:373:in with_connection'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/pool.rb:277:in perform_request'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client/pool.rb:285:in block in Pool'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client.rb:341:in exists?'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client.rb:346:in template_exists?'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/http_client.rb:82:in template_install'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/template_manager.rb:28:in install'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/template_manager.rb:16:in install_template'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/common.rb:130:in install_template'", "/tibco/ELK/logstash-7.3.1/vendor/bundle/jruby/2.5.0/gems/logstash-output-elasticsearch-10.1.0-java/lib/logstash/outputs/elasticsearch/common.rb:51:in block in setup_after_successful_connection'"]}`
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.