# How to configure logstash to get only new data/updated data into elasticsearch **URL:** \ **Category:** Logstash\ **Created:** [October 11, 2019, 4:08pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266 "2019-10-11T16:08:06Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 11, 2019, 4:08pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/1 "2019-10-11T16:08:06Z")
Dear All. i am facing the issue from a long time.i have unique column in sql server side and i want to get only new data/updated data into elasticsearch every 15 min. > how will i do ...after loading first time data into elasticsearch index or before loading data into elasticseach. i tried with after load the data into elastic then i get same copy of data two times. inputs :- > tracking\_column =\> "row\_num" > use\_column\_value =\> true > schedule =\> "\*/15 \* \* \* \* \*" > statement :---------------------------------------------------------------------------------------------------- > statement =\> "SELECT \*, ROW\_NUMBER() OVER(ORDER BY HEALTHRECORDKEY) row\_num from( > SELECT DISTINCT S.HEALTHRECORDKEY,NULL PRODUCTCODE,S.FIRSTNAME,S.LASTNAME,NULL DRUGDESCRIPTION, > PUL.LABTEST\_TYPE\_DESC,PUL.LABTEST\_DATE, PUL.DIAG\_SERVICE\_LOCATION,PUL.LABTEST\_RESULT\_DATE > FROM EDW.CONSENT.BENEFICIARY\_DETAILS S > INNER JOIN CDR.PHR\_USER\_HOSPITALIZATION PU ON S.HEALTHRECORDKEY = PU.HEALTHRECORDKEY > INNER JOIN CDR.ORDER\_INFO OI ON PU.HOSPITALIZATION\_ID = OI.HOSPITALIZATION\_ID > INNER JOIN CDR.PHR\_USER\_LABTEST PUL ON OI.ORDER\_ID = PUL.ORDER\_ID ``` UNION SELECT DISTINCT S.HEALTHRECORDKEY,PRODUCTCODE,S.FIRSTNAME,S.LASTNAME,DRUGDESCRIPTION,NULL,NULL,NULL,NULL FROM EDW.CONSENT.BENEFICIARY_DETAILS S INNER JOIN EDW.CDR.ERX_PATIENT_MEDICATION PM ON S.HEALTHRECORDKEY = PM.HEALTHRECORDKEY)a" ``` outputs:- > output { > elasticsearch { > hosts =\> ["ip:9200"] > index =\> "row\_num" > document\_type =\> "labs" > document\_id =\> "%{row\_num}" here row\_num is unique record and i am trying to trace this column here. > Need : please try to help me ...how to get newly/updated data into elastic (after loading or before loading we need to trace column value) Thanks HadoopHelp ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [October 11, 2019, 6:54pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/2 "2019-10-11T18:54:39Z")
I have same setup. I do not get duplicate of data because I use document\_id as uniq field can you show us output of duplicate data. how does it look like? it should basically remove old record and add new due to uniq document\_id ---
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 14, 2019, 9:59am UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/3 "2019-10-14T09:59:49Z")
Hi @elasticforme . thanks for being here .you are running logstash after loading first time data or before into elastic search index? Thanks HadoopHelp ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [October 14, 2019, 2:23pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/4 "2019-10-14T14:23:40Z")
I have this running on pipeline. basically you trace column row\_num. that means in theory it should only scan new rwo\_num but if you want to test it just do this select \* from xyz where row\_num \>= 100 and row\_num \<= 110; i.e selecting only 10 record and run it again. you index should still see 10 record. ---
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 15, 2019, 11:09am UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/5 "2019-10-15T11:09:00Z")
Hi @elasticforme. thanks but it is not updating the previous loaded data from elastic index with sql db data. note :- only getting new data like: in logstash.lastrun file store - 1001 then it is fetching from 1001 to up but not updating already data from elastic index. Any idea please . Thanks HadoopHelp ---
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 15, 2019, 2:39pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/6 "2019-10-15T14:39:56Z")
Dear @elasticforme and all . Now its working for me [getting new data as well as updated data from sql server] . Thanks HadoopHelp ---
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 15, 2019, 3:13pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/7 "2019-10-15T15:13:32Z")
Hi @elasticforme and all. now my logstash job is failing after some time and showing below error: ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/c/4c4463506bb9f6ce5f86ce71966a65156672d60d.png) Why this is happening please help me urgently need/help. RAM: 16GB HD=500GB Note: Elastic search is up and running !!! below is my logstash configuration file:- > input { > jdbc { > jdbc\_driver\_library =\> "C:\Users\Ramesh.kumar\Downloads\Elasticsearch\sqljdbc\_4.2\enu\jre8\sqljdbc42.jar" ``` jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver" jdbc_connection_string => "-------------------------------------;" jdbc_user => "Ra--------mar" jdbc_password => "999999919" jdbc_validate_connection => true tracking_column => "id" use_column_value => true schedule => "*/20 * * * * *" statement => "SELECT *, ROW_NUMBER() OVER(ORDER BY HEALTHRECORDKEY) id from( SELECT DISTINCT S.HEALTHRECORDKEY,NULL PRODUCTCODE,S.FIRSTNAME,S.LASTNAME,NULL DRUGDESCRIPTION, PUL.LABTEST_TYPE_DESC,PUL.LABTEST_DATE, PUL.DIAG_SERVICE_LOCATION,PUL.LABTEST_RESULT_DATE FROM EDW.CONSENT.BENEFICIARY_DETAILS S INNER JOIN CDR.PHR_USER_HOSPITALIZATION PU ON S.HEALTHRECORDKEY = PU.HEALTHRECORDKEY INNER JOIN CDR.ORDER_INFO OI ON PU.HOSPITALIZATION_ID = OI.HOSPITALIZATION_ID INNER JOIN CDR.PHR_USER_LABTEST PUL ON OI.ORDER_ID = PUL.ORDER_ID UNION SELECT DISTINCT S.HEALTHRECORDKEY,PRODUCTCODE,S.FIRSTNAME,S.LASTNAME,DRUGDESCRIPTION,NULL,NULL,NULL,NULL FROM EDW.CONSENT.BENEFICIARY_DETAILS S INNER JOIN EDW.CDR.ERX_PATIENT_MEDICATION PM ON S.HEALTHRECORDKEY = PM.HEALTHRECORDKEY)a" } } ``` output { elasticsearch { hosts =\> ["-,-,-,-:9200"] index =\> "final\_row" document\_type =\> "labs" document\_id =\> "%{id}" } } Thanks HadoopHelp ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [October 16, 2019, 1:23pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/8 "2019-10-16T13:23:56Z")
it says your elasticsearch services are down how did you check if your elasticsearch service is up and running? ---
**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\ **Post date:** [October 18, 2019, 9:48am UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/9 "2019-10-18T09:48:28Z")
Hi @elasticforme. why i am saying because i am able to access elastic index from kibana and other apps . and also we know :[http://localhost:9200/\_cat/indices](http://localhost:9200/_cat/indices) above command display all indices from Elasticsearch . > now its working but this issue come some time ? Thanks HadoopHelp ---
**Author:** ![MAvD](https://avatars.discourse-cdn.com/v4/letter/m/f08c70/32.png) [@MAvD](https://discuss.elastic.co/u/MAvD)\ **Post date:** [October 31, 2019, 1:41pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/10 "2019-10-31T13:41:11Z")
Hi, Can you show me your setup? I am fairly new to Logstash and I am trying to configure Logstash to get new data. The example of HadoopHelp does not work for me unfortunately, so I would really appreciate another example. Thanks in advance! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 28, 2019, 1:41pm UTC](https://discuss.elastic.co/t/how-to-configure-logstash-to-get-only-new-data-updated-data-into-elasticsearch/203266/11 "2019-11-28T13:41:19Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.