# Update is not Working in Logstash JDBC Input

**URL:** https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356
**Category:** Logstash
**Created:** [December 19, 2017, 6:05am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356 "2017-12-19T06:05:38Z")
**Posts on this page:** 7
**Page:** 1

<div class="post-metadata">

### Author: ![Sahul\_Hameed\_B](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Sahul\_Hameed\_B](https://discuss.elastic.co/u/Sahul_Hameed_B)
#### Post date: [December 19, 2017, 6:05am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/1 "2017-12-19T06:05:38Z")

</div>

Hi  
I am working on Elastic Search using logstash conf file using sql query. I am connecting Sql Database using Logstash conf File. This is my Conf file

input {  
jdbc {  
jdbc\_driver\_library =\> "D:\Elastic\elasticsearch-6.0.0\lib\sqljdbc42.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\> "jdbc:sqlserver://192.168.0.168;databaseName=Sample;"  
jdbc\_user =\> "sa"  
jdbc\_password =\> "1234"  
statement =\> "select Top 100 id,FName,LName,Email from dbo.AddUser WHERE id \> :sql\_last\_value"  
schedule =\> "\* \* \* \* \*"  
use\_column\_value =\> true  
tracking\_column =\> "id"  
tracking\_column\_type =\> "numeric"  
clean\_run =\> true

}  
}

output {  
elasticsearch {  
hosts =\> "localhost:9200"  
index =\> "users"  
document\_id =\> "%{id}"  
document\_type =\> "user"  
manage\_template =\> true  
}  
stdout { codec =\> rubydebug }  
}

My Table Column Details  
\<id, int\>  
\<FName, varchar(200),\>  
,\<LName, varchar(80),\>  
,\<Email, varchar(80),\>  
,\<Password, varchar(50),\>  
,\<CreatedDate, datetime,\>  
,\<LastUpdatedDate, datetime,\>  
,\<Status, int,\>  
,\<Resid, int,\>  
,\<Budget, int,\>  
,\<Dailsales, int,\>  
,\<Reports, int,\>  
,\<DayProfit, int,\>  
,\<View, varchar(20),\>  
,\<Boss, int,\>  
,\<usersfrom, varchar(50),\>)

When run this using logstash -f JDBCConnector.conf --debug .. it fetches all the records correctly.  
When i Insert new record into Database, it fetches the new record correctly due to Schedule command in Conf File.

If i Update any records in Database , its not reflecting in my elastic search Output.

What mistake i did here?? I am new to elastic search and Logstash.

Please help...

---

<div class="post-metadata">

### Author: ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [December 19, 2017, 6:53am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/2 "2017-12-19T06:53:03Z")

</div>

You're explicitly only selecting rows with an id that's higher than what it was the last time so the observed behavior is expected. If you want to catch old rows that have been updated you have two choices:

- Fetch all documents every time.
- Use a "last modified" column in the database that you can use instead of the id to select only updated rows.

---

<div class="post-metadata">

### Author: ![Sahul\_Hameed\_B](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Sahul\_Hameed\_B](https://discuss.elastic.co/u/Sahul_Hameed_B)
#### Post date: [December 19, 2017, 7:16am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/3 "2017-12-19T07:16:06Z")

</div>

Hi Magnus,

Thanks for Reply. First i go with your first choice

> Fetch all documents every time.

so i modified query like this

> select \* from dbo.AddUser WHERE id \> :sql\_last\_value

But its not reflecting updated value in ES Output.

If i Go with with your second choice

> Use a "last modified" column in the database that you can use instead of the id to select only updated rows.

In my database , all tables dont have last modified column..only some tables having last modified column.

So how can i achieve that without adding last modified column in all tables.. to get updated record.???

---

<div class="post-metadata">

### Author: ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [December 19, 2017, 8:21am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/4 "2017-12-19T08:21:14Z")

</div>

> But its not reflecting updated value in ES Output.

??? You're still only fetching rows with ids higher than the last seen value, i.e. only new rows. Drop the WHERE clause.

> So how can i achieve that without adding last modified column in all tables.. to get updated record.???

Logstash can't magically figure out which rows have changed. You need a "last modified" column (or something equivalent; a monotonically increasing integer field would work too).

---

<div class="post-metadata">

### Author: ![Sahul\_Hameed\_B](https://avatars.discourse-cdn.com/v4/letter/s/dfb087/32.png) [@Sahul\_Hameed\_B](https://discuss.elastic.co/u/Sahul_Hameed_B)
#### Post date: [December 20, 2017, 5:53am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/5 "2017-12-20T05:53:52Z")

</div>

Thanks its working.

I have doubt , I am trying to use SYS\_TABLE in SQL Server . If there is any change in table i am going to fetch Last modified date and time

for example.

> SELECT \*  
> FROM sys.dm\_db\_index\_usage\_stats  
> WHERE database\_id = DB\_ID( 'Fobesoft\_05Dec2017')  
> AND OBJECT\_ID=OBJECT\_ID('dbo.AddUser')"

this query will return output of

> last\_user\_update 2017-12-19T10:30:34.547Z

Based on these output i need to fetch all the records of that table

i.e, select \* from dbo.AddUser

If last\_user\_update time is changed . i need to run this query and need to fetch all the updated records

My question is..  
is there any if condition i can use this JDBC Input Plugin??

Is it possible to run another conf file from one conf file based on condition??

---

<div class="post-metadata">

### Author: ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)
#### Post date: [December 20, 2017, 8:36am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/6 "2017-12-20T08:36:17Z")

</div>

I don't think that's possible.

---

<div class="post-metadata">

### Author: ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 17, 2018, 8:36am UTC](https://discuss.elastic.co/t/update-is-not-working-in-logstash-jdbc-input/112356/7 "2018-01-17T08:36:18Z")

</div>

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