# How to read data from a DB table and project it on KIBANA DB **URL:** \ **Category:** Logstash\ **Created:** [November 5, 2020, 1:59pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420 "2020-11-05T13:59:20Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 5, 2020, 1:59pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/1 "2020-11-05T13:59:20Z")
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 ---
**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\ **Post date:** [November 5, 2020, 2:16pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/2 "2020-11-05T14:16:41Z")
That functionality is typically being done in Logstash with the [JDBC Input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html). ---
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 5, 2020, 3:49pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/3 "2020-11-05T15:49:59Z")
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.. ---
**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\ **Post date:** [November 5, 2020, 5:54pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/4 "2020-11-05T17:54:22Z")
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. ```auto 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" } } ``` ---
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 6, 2020, 9:57am UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/5 "2020-11-06T09:57:29Z")
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. ---
**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\ **Post date:** [November 6, 2020, 1:55pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/6 "2020-11-06T13:55:54Z")
You need to get the [Oracle DB driver](https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html) 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. ---
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 9, 2020, 8:44am UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/7 "2020-11-09T08:44:14Z")
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? ---
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 12, 2020, 10:45am UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/8 "2020-11-12T10:45:06Z")
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 } } ``` ---
**Author:** ![aaron-nimocks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aaron-nimocks/32/73965_2.png) [@aaron-nimocks](https://discuss.elastic.co/u/aaron-nimocks)\ **Post date:** [November 12, 2020, 1:40pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/9 "2020-11-12T13:40:37Z")
When you run this what does the `stdout` give you? ---
**Author:** ![Dinesh\_Potey](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_potey/32/73432_2.png) [@Dinesh\_Potey](https://discuss.elastic.co/u/Dinesh_Potey)\ **Post date:** [November 12, 2020, 3:31pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/10 "2020-11-12T15:31:27Z")
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'"]}` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 10, 2020, 5:31pm UTC](https://discuss.elastic.co/t/how-to-read-data-from-a-db-table-and-project-it-on-kibana-db/254420/11 "2020-12-10T17:31:48Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.