Date parse failure - jdbc

Elastic Stack Logstash
1

Hi,

I've tried to index some docs with logstash using jdbc. But it always fail because it is not able to index date with format yyyyMMdd

input {
jdbc {
jdbc_connection_string => "jdbc:sqlserver://localhost;databaseName=BD;integratedSecurity=false;"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_driver_library => "/root/sqljdbc42.jar"
statement => "SELECT *
FROM table"
}
}

filter {
mutate {
copy => { "movimientoid" => "[@metadata][_id]"}
remove_field => ["@timestamp", "movimientoid"]
}
}

output {
elasticsearch {
hosts => "http://localhost:9200"
index => "table"
document_id => "%{[@metadata][_id]}"
}
#stdout{ }
}

But I always received the same error:

[WARN ] 2020-04-27 18:32:49.989 [[main]>worker0] elasticsearch - Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>"25725798", :_index=>"movimientos", :_type=>"_doc", :routing=>nil}, #LogStash::Event:0x4cc06d9e], :response=>{"index"=>{"_index"=>"movimientos", "_type"=>"_doc", "_id"=>"25725798", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [fecha] of type [date] in document with id '25725798'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [2009-04-21T22:00:00.000Z] with format [basic_date]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"date_time_parse_exception: Text '2009-04-21T22:00:00.000Z' could not be parsed at index 4"}}}}}}

I don't know how can i resolve this. Could someone help me??

Furthermore, the SQL query returns the field fecha with yyyy-MM-dd format.

Thanks.

2

elasticsearch is expecting a basic_date (yyyyMMdd), which I think has to be due to an index template. If a table contains a date/time type then the jdbc filter will have converted that into a Logstash::Timestamp. The elasticsearch output will have formated that as 2009-04-21T22:00:00.000Z.

You can probably fix this by changing the index template to expect date_time instead of basic_date.

3

Could i change the default timestamp to basic date?

4

You could use ruby and strftime to format a field, possibly event @timestamp with basic_date format. However, there is code in logstash that expects @timestamp to be a Logstash::Timestamp, so changing its type may break things.

5

Thanks @Badger One doubt more, if finally I record the dates with the default format (2009-04-21T22:00:00.000Z.), could I search in my index using basic_date format in a range query?

6

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