# Use logstash\_checksum as tracking\_column

**URL:** <https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269>\
**Category:** Logstash\
**Created:** [November 25, 2019, 9:49am UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269 "2019-11-25T09:49:57Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![WalterLeff](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@WalterLeff](https://discuss.elastic.co/u/WalterLeff)\
**Post date:** [November 25, 2019, 9:49am UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/1 "2019-11-25T09:49:57Z")

</div>

Hi all

Logstash creates perfectly a checksum from my row data

```
{
  "_index": "rel",
  "_type": "_doc",
  "_id": "2",
  "_version": 5,
  "_score": 0,
  "_source": {
   
    "datecreated": "2016-06-09T00:00:00.000Z",
    "datemodified": "2019-11-20T00:00:00.000Z",
    "modifiedby": null,
    "@timestamp": "2019-11-25T09:04:02.730Z",
    "rel_id": 4,
    "ts_id": null,
     "md_id": null,
    "createdby": "SYSTEM",
    "ng": false,
    "logstash_checksum": "5694ec39969161d4b74d716001f81c0b",
    "id": 2,
    "isactive": true,
    "@version": "1"
  },

```

But i get an error when i'm trying to get this checksum to work as tracking column for updated rows

```
[main] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: Incorrect syntax near '}'.>}

```

this is my conf file

```
input {  
jdbc {
           
    # Query for testing purpose
    statement => "SELECT * from gln where {logstash_checksum} > :sql_last_value"
    tracking_column => "{logstash_checksum}"
    #tracking_column_type => "binary"
    
    use_column_value => true   
    #clean_run => true        
    schedule => "* * * * *"
}
}
filter{
    checksum {
   algorithm => 'md5' 
  }
    # fingerprint{}
}
output {  
    elasticsearch {
        hosts => ["localhost:9200"]
        index => "rel"
        document_id => "%{id}"    
    }
stdout { codec => rubydebug }
}

```

any idea what i'm doing wrong ?

thx in advance

---

<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:** [November 25, 2019, 3:05pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/2 "2019-11-25T15:05:10Z")

</div>

Does the logstash\_checksum column exist in the database? If so, remove the {}. If not, you cannot reference it in the input. I would expect it to get added by the checksum filter.

There is no reason to use md5. It is broken and has been for nearly two decades. Continuing to use it to detect accidental duplication just encourages folks to continue to use it for cryptographic purposes for which it is completely unfit. Why not use the default algorithm?

---

<div class="post-metadata">

**Author:** ![WalterLeff](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@WalterLeff](https://discuss.elastic.co/u/WalterLeff)\
**Post date:** [November 25, 2019, 3:10pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/3 "2019-11-25T15:10:22Z")

</div>

Hi Badger

the logstash\_checksum does not exist in my table. I just want to have a way where i can import my sql data to elastic and that I can check whether there is an update on a row or not, so that not all my data has to be pushed to elastic search everytime. Are there examples on how to manage that ?

As for the algorithm, i was just trying something.

thx for the response

---

<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:** [November 25, 2019, 3:18pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/4 "2019-11-25T15:18:13Z")

</div>

If there is no tracking column in the database then you are going to have to fetch all of the data repeatedly and detect duplicates. You could do that with a checksum filter (or in any version of logstash from the last few years, a fingerprint filter) and then set the document\_id on the elasticsearch output to the checksum.

---

<div class="post-metadata">

**Author:** ![WalterLeff](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@WalterLeff](https://discuss.elastic.co/u/WalterLeff)\
**Post date:** [November 25, 2019, 4:25pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/5 "2019-11-25T16:25:48Z")

</div>

Isn't there a huge load on your db doing it this way ? what if my tables have millions of rows and they all have to be fetched just to check whether there is a change or not ?

or am i missing something ?

---

<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:** [November 25, 2019, 4:33pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/6 "2019-11-25T16:33:26Z")

</div>

> [@WalterLeff](#):
>
> Isn't there a huge load on your db doing it this way ?

Yes. But if there is no tracking column in the database there is no alternative. If you have a column that is a timestamp or sequence that tracks updates then use it, and you will only fetch updated rows.

---

<div class="post-metadata">

**Author:** ![WalterLeff](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@WalterLeff](https://discuss.elastic.co/u/WalterLeff)\
**Post date:** [November 25, 2019, 4:40pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/7 "2019-11-25T16:40:48Z")

</div>

I have a column in my table that is a timestamp in binary, but logstash imports it like this

`<Sequel::SQL::Blob:0x9b18 bytes=8 content="\u0000\u0000\u0000\u0000\u0001\xE6\u0016\xEF"`  
instead of  
`00000000000000000001E60016xEF"`

any idea why this happens ?

---

<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:** [November 25, 2019, 4:48pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/8 "2019-11-25T16:48:37Z")

</div>

No. You should create a new question about that.

---

<div class="post-metadata">

**Author:** ![WalterLeff](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@WalterLeff](https://discuss.elastic.co/u/WalterLeff)\
**Post date:** [November 25, 2019, 4:51pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/9 "2019-11-25T16:51:22Z")

</div>

I did

Anyway thanks for the patience and help

> [@Updated data with tracking column for sql server binary timestamp](https://discuss.elastic.co/t/updated-data-with-tracking-column-for-sql-server-binary-timestamp/208700):
>
> Hi all, I have a table with a version column which is a timestamp as a binary. This is changed every time there is a change/update in the row. I can transmit this table to elastic but my format is not really correct \<Sequel::SQL::Blob:0x9b18 bytes=8 content="\u0000\u0000\u0000\u0000\u0001\xE6\u0016\xEF" instead of 00000000000000000001E60016xEF" is there a way to correctly import this binary column and set it as a tracking column so when there is a change is this field logstash only updat…

---

<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:** [December 23, 2019, 4:51pm UTC](https://discuss.elastic.co/t/use-logstash-checksum-as-tracking-column/209269/10 "2019-12-23T16:51:25Z")

</div>

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