# Logstash - set @timestamp from sql date column with JDBC input

**URL:** <https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873>\
**Category:** Logstash\
**Created:** [August 4, 2017, 11:28am UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873 "2017-08-04T11:28:23Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dlalex83](https://avatars.discourse-cdn.com/v4/letter/d/a8b319/32.png) [@dlalex83](https://discuss.elastic.co/u/dlalex83)\
**Post date:** [August 4, 2017, 11:28am UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873/1 "2017-08-04T11:28:23Z")

</div>

Hi there,

I've setup a JDBC input plugin to read off my SQL Server ErrorLog table with the output being an ES index.  
No particular mapping needed but the only requirement is to use the LogDate column as the main @timestamp.  
I've tried the date filter but it constantly fails with \_dateparsefailure. I've tried several patterns but they dont seem to be working. Reading around I've noticed that the LogDate field is already a date (which should be the reason why the date filter can't parse it). I've then tried to just use the mutate-copy to copy the logdate value into the @timestamp field but no luck either, the @timestamp is always the ingestion date.

Here's my conf:

> ```
> input
> {
> 
> jdbc
> {
> type => "errors"
> jdbc_driver_library => "/opt/bitnami/logstash/drivers/sqljdbc_6.0/enu/jre8/sqljdbc42.jar"
> jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
> jdbc_connection_string => "jdbc:sqlserver://myServer:1433;databaseName=myDB"
> jdbc_user => "myUsername"
> jdbc_password => "myPassword"
> statement => "select * from errorlog with(nolock)"
> jdbc_paging_enabled => "true"
> jdbc_page_size => "5000"
> schedule => "* * * * *"
> }
> }
> 
> filter {
> mutate {
> copy => { "{%logdate}" => "@timestamp" }
> remove_field => ["logdate"]
> }
> }
> 
> output
> {
> elasticsearch
> {
> hosts => ["127.0.0.1:9200"]
> index => "errors-%{+YYYY.MM.dd}"
> document_id => "%{logid}"
> }
> }
> 
> ```

Any 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:** [August 4, 2017, 11:38am UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873/2 "2017-08-04T11:38:03Z")

</div>

> ```
> copy => { "{%logdate}" => "@timestamp" }
> 
> ```

Wrong syntax, try this:

```
	copy => { "logdate" => "@timestamp" }

```

Otherwise try converting the `logdate` field to a string with the mutate filter's convert option before you pass it to the date filter.

---

<div class="post-metadata">

**Author:** ![dlalex83](https://avatars.discourse-cdn.com/v4/letter/d/a8b319/32.png) [@dlalex83](https://discuss.elastic.co/u/dlalex83)\
**Post date:** [August 4, 2017, 11:38am UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873/3 "2017-08-04T11:38:38Z")

</div>

Cool, will give it a go. Let u know. Thanks

---

<div class="post-metadata">

**Author:** ![dlalex83](https://avatars.discourse-cdn.com/v4/letter/d/a8b319/32.png) [@dlalex83](https://discuss.elastic.co/u/dlalex83)\
**Post date:** [August 4, 2017, 12:00pm UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873/4 "2017-08-04T12:00:42Z")

</div>

@magnusbaeck converting logdate to a string and then feed it to the date filter did the trick!  
FYI, weirdly just adjusting the syntax of the copy option of the mutate filter didn't work with the error being a NullRefPointer exception. Not sure why.

Thanks anyway for your help!

---

<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:** [September 1, 2017, 12:00pm UTC](https://discuss.elastic.co/t/logstash-set-timestamp-from-sql-date-column-with-jdbc-input/95873/5 "2017-09-01T12:00:47Z")

</div>

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