# Logstash and databse **URL:** \ **Category:** Logstash\ **Created:** [July 24, 2015, 10:24am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225 "2015-07-24T10:24:22Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 24, 2015, 10:24am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/1 "2015-07-24T10:24:22Z")
First of all thanks to team that they provided us with jdbc plugin . I really needed it . Now i am stuck again I connected this plugin , ran the config file and yippie i could see the reflection in kibana. But now i wanted to add new row to my database so i did it and ran the command on logstash but now all of my rows got duplicated so what should we do now???? Now elasticsearch shows me double data plus 1 which i added .Isnt there a way by which logstash is constantly running and in certain way polls the database and bring all the newly added rows?? Help needed reply soon team. Thank you ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 28, 2015, 9:21pm UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/2 "2015-07-28T21:21:34Z")
This is unique to your setup. If I am understanding your data correctly, you have some sort of primary key on your table. If this is the case, you can maybe try something like this: ```auto input { jdbc { statement => "select my_id, another_col from table" } } output { elasticsearch { protocol => http document_id => "%{my_id}" action => index } } ``` This way, Elasticsearch will use your data's primary key as the document id (or some mapping of it) and will update the document with the new content. Unfortunately, this is not aware of whether the document exists already, but it does assure that you will not have duplicates. would you mind sharing the nature of your ids for your data? I am playing around with an idea of watermarking that would automate this for ordered fields for newer data. for example, remembering that all IDs less than 132 has been processed, so only fetch later data with greater id values. does that make sense? ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 29, 2015, 3:57am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/3 "2015-07-29T03:57:16Z")
see i have tables which checks CPU utilization for particular work so it has fields like timestamp ,hostname ,os\_type , data sent , data received,, etc etc.. Now i want to work on live data and so my database gets updated automatically. so using kibana i ll portray graphs .. but i want to work on real time but if jdbc plugin is to ingest data just once then in that case i dont think so it will work . as logstash shutdowns immediately.... unlike CSV file in which logstash doesnt shutdown until we wish for. Please help me out then which plugin i should use to fetch data from postgresql database ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 29, 2015, 5:18am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/4 "2015-07-29T05:18:04Z")
For now. you may want to take a look at the `:sql_last_start` computed parameter. This field represents the last time (in current time) for which a sql statement was run. So if your timestamps are based on real time as well, this may work for you. you can set it up like this: ```auto input { jdbc { ... statement => "select * from table where timestamp > :sql_last_start" schedule => "* * * * *" } } ``` This input configuration will only fetch latest records and run according to this cron-like schedule which means "run every minute, on the minute". By adding a schedule, the input plugin will stay alive and run the query every minute. Regarding postgresql. this plugin supports that. You just have to download the driver jars yourself from here: [https://jdbc.postgresql.org/download.html](https://jdbc.postgresql.org/download.html). and configure the `jdbc_driver_library` with the path of the downloaded driver. And set the `jdbc_driver_class` option to `org.postgresql.Driver`. does that make sense? for a number of reasons, we chose not to include these jars within the plugin itself. ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 29, 2015, 10:57am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/5 "2015-07-29T10:57:59Z")
Thanx its working.. it solved 90% of my problem ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 29, 2015, 2:50pm UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/6 "2015-07-29T14:50:33Z")
cool. what is 10% of the problem that still needs to be solved? ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 3:39am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/7 "2015-07-30T03:39:06Z")
i also have timestamp field and logstash also gives timestamp of its own so if i use "select \* from table where timestamp \> :sql\_last\_start" sql\_last\_start takes timefield of logstash so its conflicting with my timestamp field .. suppose my timestamp field has value 30-07-2015 09:04:08 and logstash has current value so next data which it takes from database will have timestamp greater than current logstash timestamp so its conflicting i tried using date{ } with match{ } and target { } but i am getting error i used this date { match =\> ["timestamp", "yyyy-MM-dd HH:mm:ss " , "ISO8601"] target =\> "@timestamp" } and i am getting error "cannot convert instance of class org.jruby.RubyTime to class java.lang.String " can we replace logstash timestamp field with my timestamp?? i guess this problem is due to different time zones... ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 30, 2015, 5:10am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/8 "2015-07-30T05:10:26Z")
The default target is already "@timestamp" for the date filter. assuming your timestamp field is a string of value: `30-07-2015 09:04:08`, this `date` filter to do the trick. ```auto date { match => ["timestamp", "dd-mm-YYYY HH:mm:ss"] remove_field => "timestamp" # remove now redundant field } ``` you seem to have mixed up the parsing, and it may have complained about that. If this is not the problem. run logstash with `--debug` for more information and stacktrace. ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 5:15am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/9 "2015-07-30T05:15:13Z")
my timestamp field is of timestamp type without timezone .. I used postgresql database ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 30, 2015, 5:16am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/10 "2015-07-30T05:16:22Z")
I do not assume a timezone is provided with this match ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 30, 2015, 5:16am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/11 "2015-07-30T05:16:57Z")
the default timezone that Logstash will set for it is UTC I believe. If this is an issue, it is configurable. ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 5:18am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/12 "2015-07-30T05:18:00Z")
what i want to do is use my timestamp field which is in database instead of logstash timestamp (@timestamp). what should i do ? because when i use \>: sql\_last\_start , it uses logstash by default and i get duplicacy again as my timestamp differ ---
**Author:** ![talevy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/talevy/32/44896_2.png) [@talevy](https://discuss.elastic.co/u/talevy)\ **Post date:** [July 30, 2015, 5:25am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/13 "2015-07-30T05:25:53Z")
oh, that is slightly different. `:sql_last_start` uses the system current time. ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 5:28am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/14 "2015-07-30T05:28:07Z")
input { jdbc{ jdbc\_driver\_library =\> "C:\anmol\postgresql-9.4-1201.jdbc4.jar" jdbc\_driver\_class =\> "org.postgresql.Driver" jdbc\_connection\_string =\> "jdbc:postgresql://localhost:5434/mm\_db\_Reporting" jdbc\_user =\> "mmsuper" jdbc\_password =\> "mmsuper" statement =\> "select \* from om\_report where timestamp \> :sql\_last\_start " jdbc\_paging\_enabled =\> "true" jdbc\_page\_size =\> "50000" } } filter { date { match =\> ["timestamp", "YYYY-mm-dd HH:mm:ss"] remove\_field =\> "timestamp"} } output { stdout { codec =\> rubydebug } elasticsearch { action =\> "index" host =\> "localhost" index =\> "mmsuper\_om\_report" } } this is my config file * * * * * * ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 5:28am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/15 "2015-07-30T05:28:25Z")
* * * this is the error * * * C:\Program Files\logstash-1.5.2\bin\>logstash agent -f example.conf io/console not supported; tty will not be manipulated ←[33mFailed parsing date from field {:field=\>"timestamp", :value=\>2015-07-30 09: 04:54 +0530, :exception=\>"cannot convert instance of class org.jruby.RubyTime to class java.lang.String", :config\_parsers=\>"YYYY-mm-dd HH:mm:ss", :config\_locale =\>"default=en\_US", :level=\>:warn}←[0m ←[33mFailed parsing date from field {:field=\>"timestamp", :value=\>2015-07-30 13: 04:54 +0530, :exception=\>"cannot convert instance of class org.jruby.RubyTime to class java.lang.String", :config\_parsers=\>"YYYY-mm-dd HH:mm:ss", :config\_locale =\>"default=en\_US", :level=\>:warn}←[0m ←[33mFailed parsing date from field {:field=\>"timestamp", :value=\>2015-07-30 15: 04:54 +0530, :exception=\>"cannot convert instance of class org.jruby.RubyTime to class java.lang.String", :config\_parsers=\>"YYYY-mm-dd HH:mm:ss", :config\_locale =\>"default=en\_US", :level=\>:warn}←[0m Jul 30, 2015 10:51:44 AM org.elasticsearch.node.internal.InternalNode INFO: [logstash-HFX2WS1-4800-11768] version[1.5.1], pid[4800], build[5e38401/201 5-04-09T13:41:35Z] Jul 30, 2015 10:51:44 AM org.elasticsearch.node.internal.InternalNode INFO: [logstash-HFX2WS1-4800-11768] initializing ... Jul 30, 2015 10:51:44 AM org.elasticsearch.plugins.PluginsService INFO: [logstash-HFX2WS1-4800-11768] loaded [], sites [] Jul 30, 2015 10:51:46 AM org.elasticsearch.node.internal.InternalNode INFO: [logstash-HFX2WS1-4800-11768] initialized Jul 30, 2015 10:51:46 AM org.elasticsearch.node.internal.InternalNode start INFO: [logstash-HFX2WS1-4800-11768] starting ... Jul 30, 2015 10:51:46 AM org.elasticsearch.transport.TransportService doStart INFO: [logstash-HFX2WS1-4800-11768] bound\_address {inet[/0:0:0:0:0:0:0:0:9301]}, publish\_address {inet[/100.96.85.51:9301]} Jul 30, 2015 10:51:46 AM org.elasticsearch.discovery.DiscoveryService doStart INFO: [logstash-HFX2WS1-4800-11768] elasticsearch/gFEXMhWBTEOfBW4nICa-Sg Jul 30, 2015 10:51:51 AM org.elasticsearch.cluster.service.InternalClusterServic e$UpdateTask run INFO: [logstash-HFX2WS1-4800-11768] detected\_master [Crossbones][WxFjSVf1QO6EfT6 AXA68vg][7LH4ZR1][inet[/100.96.85.52:9300]], added {[Doctor Spectrum][9BlOCK4KRm 6KOruMhVoI3g][HFX2WS1][inet[/100.96.85.51:9300]],[Crossbones][WxFjSVf1QO6EfT6AXA 68vg][7LH4ZR1][inet[/100.96.85.52:9300]],}, reason: zen-disco-receive(from maste r [[Crossbones][WxFjSVf1QO6EfT6AXA68vg][7LH4ZR1][inet[/100.96.85.52:9300]]]) Jul 30, 2015 10:51:51 AM org.elasticsearch.node.internal.InternalNode start INFO: [logstash-HFX2WS1-4800-11768] started Logstash startup completed { "timestamp" =\> 2015-07-30 09:04:54 +0530, "physicalserver" =\> "103.84.68.861", "logicalserver" =\> "190.57.590.87", "configuration" =\> "eric5", "collection" =\> "bus5", "servicename" =\> "gm", "configposition" =\> "middle", "activitytype" =\> "back-end", "activityname" =\> "log4", "countertype" =\> "request", "networkelement" =\> "node4", "additionalinfo" =\> "", "request" =\> 444, "success" =\> 407, "rejected" =\> 8, "timeout" =\> 4, "discarded" =\> 10, "failed" =\> 15, "@version" =\> "1", "@timestamp" =\> "2015-07-30T05:21:43.864Z", "tags" =\> [ [0] "\_dateparsefailure" ] } { "timestamp" =\> 2015-07-30 13:04:54 +0530, "physicalserver" =\> "109.84.68.861", "logicalserver" =\> "198.57.590.87", "configuration" =\> "eric3", "collection" =\> "buss", "servicename" =\> "mm", "configposition" =\> "front", "activitytype" =\> "back-end", "activityname" =\> "log", "countertype" =\> "request", "networkelement" =\> "node", "additionalinfo" =\> "rj65", "request" =\> 467, "success" =\> 408, "rejected" =\> 17, "timeout" =\> 13, "discarded" =\> 12, "failed" =\> 17, "@version" =\> "1", "@timestamp" =\> "2015-07-30T05:21:43.864Z", "tags" =\> [ [0] "\_dateparsefailure" ] } { "timestamp" =\> 2015-07-30 15:04:54 +0530, "physicalserver" =\> "10.84.68.861", "logicalserver" =\> "198.58.590.87", "configuration" =\> "eric3", "collection" =\> "bus5", "servicename" =\> "gm", "configposition" =\> "middle", "activitytype" =\> "back-end", "activityname" =\> "log", "countertype" =\> "response", "networkelement" =\> "node", "additionalinfo" =\> "rj65", "request" =\> 468, "success" =\> 409, "rejected" =\> 13, "timeout" =\> 17, "discarded" =\> 17, "failed" =\> 12, "@version" =\> "1", "@timestamp" =\> "2015-07-30T05:21:43.864Z", "tags" =\> [ [0] "\_dateparsefailure" ] } Logstash shutdown complete * * * this is the error ---
**Author:** ![Anmol\_Gupta](https://avatars.discourse-cdn.com/v4/letter/a/eb9ed0/32.png) [@Anmol\_Gupta](https://discuss.elastic.co/u/Anmol_Gupta)\ **Post date:** [July 30, 2015, 5:30am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/16 "2015-07-30T05:30:04Z")
if that is so then sql\_last\_start wont solve my problem, 😢 need to find something else? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 5:33am UTC](https://discuss.elastic.co/t/logstash-and-databse/26225/17 "2017-07-06T05:33:23Z")