# Continuous Data from SQL Server into Logstash
**URL:** https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929
**Category:** Logstash
**Created:** [December 7, 2018, 12:35pm UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929 "2018-12-07T12:35:29Z")
**Posts on this page:** 16
**Page:** 1
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 7, 2018, 12:35pm UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/1 "2018-12-07T12:35:29Z")
I am using logstash to retrieve data from SQL into Elastic Search.
I run query data on logstash and index is created and able to visualize data in Kibana.
My concern is how to stream data always from SQL to logstash , whenever record is updated into SQL then that record should be updated into elasticsearch using logstash.
Is this possible
---
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 12, 2018, 6:57am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/2 "2018-12-12T06:57:30Z")
Hi , Any one has idea about above issue
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [December 12, 2018, 7:13am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/3 "2018-12-12T07:13:23Z")
The Logstash JDBC plugin retrieves data through queries and supports the use of a [tracking column](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-tracking_column), which you can use to fetch only new or updated records assuming you can write a suitable query that uses this. If your data e.g. has an updated timestamp, you can use this tracking column to only select records with timestamps higher than when the query last ran, selecting only new or updated data.
---
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 12, 2018, 9:34am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/4 "2018-12-12T09:34:11Z")
Actually I have used below logstash.
But every time below file invoke add all indexes from initial , not taking new or updated records alone.
input {
jdbc {
jdbc\_connection\_string =\> "jdbc:sqlserver://XXXXX;databaseName=XXX;integratedSecurity=false;"
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc\_user =\> "XXX"
jdbc\_password =\> "XXXX"
jdbc\_driver\_library =\> "/path/enu/jre8/sqljdbc42.jar"
statement =\> "SELECT id,name FROM TestTable where id\> :sql\_last\_value "
tracking\_column =\> "id"
use\_column\_value =\> true
clean\_run =\> true
last\_run\_metadata\_path =\> "/path/.logstash\_jdbc\_last\_run"
}
}
output {
elasticsearch {
hosts =\> "[http://localhost:9200](http://localhost:9200)"
index =\> "testindex"
}
stdout{ }
}
Not sure what is missing,
Any help @Christian_Dahlqvist .
---
### Author:  [@Eniqmatic](https://discuss.elastic.co/u/Eniqmatic)
#### Post date: [December 12, 2018, 9:36am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/5 "2018-12-12T09:36:06Z")
Use the "action =\> update" function in the elasticsearch output.
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [December 12, 2018, 9:38am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/6 "2018-12-12T09:38:05Z")
You do have `clean_run` set to `true`, which is likely to cause this.
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 13, 2018, 5:21am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/7 "2018-12-13T05:21:30Z")
hi [mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik),
can you please provide the correct code for this issue as i am facing the same problem.
thanks inadvance.
---
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 13, 2018, 6:16am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/8 "2018-12-13T06:16:02Z")
Hi @balumurari1 ,
input {
jdbc {
jdbc\_connection\_string =\> "jdbc:sqlserver://XXXXX;databaseName=XXXX;integratedSecurity=false;"
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc\_user =\> "XXXX"
jdbc\_password =\> "XXX"
jdbc\_driver\_library =\> "/path/jre8/sqljdbc42.jar"
statement =\> "SELECT id,name FROM sampletable where id\> :sql\_last\_value "
tracking\_column =\> "id"
use\_column\_value =\> true
last\_run\_metadata\_path =\> "/path/.logstash\_jdbc\_last\_run"
schedule =\> "\*/5 \* \* \* \* \*"
}
}
output {
elasticsearch {
hosts =\> "[http://localhost:9200](http://localhost:9200)"
index =\> "sampleindex"
}
stdout{ }
}
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 13, 2018, 6:44am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/9 "2018-12-13T06:44:30Z")
Hello @ [mubarak\_shaik](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/8),
thanks a lot for your reply,
when i am running above code i observe that my logstash\_jdbc\_last\_run file is getting empty.
I am confused why it gets empty.
the output console is as shown in the image,
The input code is as follows,
input {
jdbc {
jdbc\_driver\_library =\> "D:/Softwares/logstash/lib/com.mysql.jdbc\_5.1.5.jar"
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/testdb"
jdbc\_user =\> "root"
jdbc\_password =\> "root"
statement =\> "SELECT id,name FROM sample where id \> :sql\_last\_value"
tracking\_column =\> "id"
use\_column\_value =\> true
last\_run\_metadata\_path =\> "C:/Users/XXXXXX/.logstash\_jdbc\_last\_run"
schedule =\> "\*/1 \* \* \* \*"
}
}
output {
elasticsearch {
hosts =\> ["localhost:9200"]
index =\> "idx\_mysqldata"
}
stdout { codec =\> rubydebug }
}
Please correct me if i am wrong.
Thanks in advance.
---
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 13, 2018, 6:56am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/10 "2018-12-13T06:56:16Z")
Hi ,
Please try with some ID Value first in where condition.
input {
jdbc {
jdbc\_driver\_library =\> "D:/Softwares/logstash/lib/com.mysql.jdbc\_5.1.5.jar"
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/testdb"
jdbc\_user =\> "root"
jdbc\_password =\> "root"
statement =\> "SELECT id,name FROM sample where id \> 1"
tracking\_column =\> "id"
last\_run\_metadata\_path =\> "C:/Users/XXXXXX/.logstash\_jdbc\_last\_run"
schedule =\> "\*/1 \* \* \* \*"
}
}
output {
elasticsearch {
hosts =\> ["localhost:9200"]
index =\> "idx\_mysqldata"
}
stdout { codec =\> rubydebug }
}
Then check your results first.
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 13, 2018, 7:24am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/11 "2018-12-13T07:24:28Z")
Hi [mubarak\_shaik](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/10),
thanks for your response. It works as shown below for id\>1
i have 3 records with id =1,2,3 in database
So for every 1 minute the files are continuously getting added into elasticsearch inadditional like
for first 1min = 2 hits with id = 2,3 (total hits:2)
for next 1min = 4 hits with id = 2,3,2,3(total hits:4)
for next 1min = 6 hits with id = 2,3,2,3,2,3(total hits:6)
Here, ES is getting filled up with duplicate records. but i need to check for any updates or add new records in database then it must reflect in elasticsearch
Please help me. thanks inadvance.
---
### Author:  [@mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik)
#### Post date: [December 13, 2018, 9:48am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/12 "2018-12-13T09:48:03Z")
Hi @balumurari1 ,
Please delete .logstash\_jdbc\_last\_run file. then follow below steps:
First Execute this one:
input {
jdbc {
jdbc\_driver\_library =\> "D:/Softwares/logstash/lib/com.mysql.jdbc\_5.1.5.jar"
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/testdb"
jdbc\_user =\> "root"
jdbc\_password =\> "root"
statement =\> "SELECT id,name FROM sample where id \> 1"
use\_column\_value =\> true
tracking\_column =\> "id"
last\_run\_metadata\_path =\> "C:/Users/XXXXXX/.logstash\_jdbc\_last\_run"
}
}
output {
elasticsearch {
hosts =\> ["localhost:9200"]
index =\> "idx\_mysqldata"
}
stdout { codec =\> rubydebug }
}
Now you have initial data , then now execute below config:
input {
jdbc {
jdbc\_driver\_library =\> "D:/Softwares/logstash/lib/com.mysql.jdbc\_5.1.5.jar"
jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"
jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/testdb"
jdbc\_user =\> "root"
jdbc\_password =\> "root"
statement =\> "SELECT id,name FROM sample where id \> :sql\_last\_value"
use\_column\_value =\> true
tracking\_column =\> "id"
last\_run\_metadata\_path =\> "C:/Users/XXXXXX/.logstash\_jdbc\_last\_run"
schedule =\> "\*/5 \* \* \* \* \*"
}
}
output {
elasticsearch {
hosts =\> ["localhost:9200"]
index =\> "idx\_mysqldata"
}
stdout { codec =\> rubydebug }
}
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 13, 2018, 9:56am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/13 "2018-12-13T09:56:28Z")
hi [mubarak\_shaik](https://discuss.elastic.co/u/mubarak_shaik),
Thanks a lot it worked fine now.
With this, we can insert any new rows inserted into database to the elasticsearch.
Can you please help me to know,
What is the scenario if we update the values from the existing rows, which must be updated in the ES aswell?
Ex:-
Table Name: Sample
columns(id,Name)
for id =1, we have name = 'Ram', in DB and in ES
suppose in database, i have updated name = 'Ramu' for id=1. How to reflect the change to ES.
Thanks in advance.
---
### Author:  [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [December 13, 2018, 9:58am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/14 "2018-12-13T09:58:19Z")
You need to have a column that you can use to identify the change, e.g. a modified timestamp, and then use this with the `sql_last_value` parameter.
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 13, 2018, 10:08am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/15 "2018-12-13T10:08:14Z")
fine, thank you..!!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 10, 2019, 10:08am UTC](https://discuss.elastic.co/t/continuous-data-from-sql-server-into-logstash/159929/16 "2019-01-10T10:08:24Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.