# Auto-update Logstash on Insert on SQL Server

**URL:** <https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522>\
**Category:** Logstash\
**Created:** [October 21, 2019, 4:15pm UTC](https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522 "2019-10-21T16:15:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sam.cook](https://avatars.discourse-cdn.com/v4/letter/s/57b2e6/32.png) [@sam.cook](https://discuss.elastic.co/u/sam.cook)\
**Post date:** [October 21, 2019, 4:15pm UTC](https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522/1 "2019-10-21T16:15:49Z")

</div>

Hi,  
I am working with Logstash and Elasticsearch but I have a problem. In my case I want Elasticsearch to be updated with the new data added into a SQL Database, all done via Logstash.  
I set the configuration file as below:

```auto
input {
    jdbc {
        jdbc_connection_string => "JDBC-Connection-String"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        jdbc_user => "JDBC-Connection-User"
        jdbc_driver_library => "JDBC-Driver-Path"

        statement => "SELECT MyCol1 MyCol2 FROM MyTable"
        use_column_value => true
        tracking_column => "MyCol1"
        tracking_column_type => "numeric"
        clean_run => true
        schedule => "*/1 * * * *"
    }
}

output {
    elasticsearch {
    hosts => "http://localhost:9200"
    index => "MyIndex"
    document_id => "%{MyCol1}"
}
    stdout { }
}

```

As you can see, I run a SELECT into all the DB once a minute, than I add new lines found in the DB into Elasticsearch.  
It works fine, but my problem is that every time logstash executes the query, it scans all the rows into the DB (I know that it is set to do that, but I haven't found another way) and then it adds in Elasticsearch the ones that are not there yet, obviously causing bad performances.  
I am struggling to find a way that Logstash doesn't scans all the DB every time but simply adds the new rows into ElasticSearch.  
P.S. MyCol1 is the Primary Key of the SQL table.

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [October 21, 2019, 4:32pm UTC](https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522/2 "2019-10-21T16:32:11Z")

</div>

If you have either a timestamp or a sequence that allows you to add a WHERE clause that identifies new entries then you can [persist state](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#_state) about which rows have been read. But if not, you have to read the whole table every time.

---

<div class="post-metadata">

**Author:** ![sam.cook](https://avatars.discourse-cdn.com/v4/letter/s/57b2e6/32.png) [@sam.cook](https://discuss.elastic.co/u/sam.cook)\
**Post date:** [October 22, 2019, 7:40am UTC](https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522/3 "2019-10-22T07:40:43Z")

</div>

Thank you so much for your response.  
For those who have the same problem as me, I added this line to my configuration file:

```auto
last_run_metadata_path => "data\.logstash_jdbc_last_run"

```

Then I modified the query statement as it follows:

```auto
statement => "SELECT MyCol1 MyCol2 FROM MyTable WHERE MyCol1 > :sql_last_value"

```

As you can see, `:sql_last_value` is where I get the last value read from MyCol1, which is stored into `"data\.logstash_jdbc_last_run"`

---

<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:** [November 19, 2019, 7:40am UTC](https://discuss.elastic.co/t/auto-update-logstash-on-insert-on-sql-server/204522/4 "2019-11-19T07:40:49Z")

</div>

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