Failed to Parse Date Field

Elastic Stack Logstash
1

I have some CSV files I'm trying to ingest using Logstash's CVS filter plugin. I'm needing the "Date" column to represent the event time field in Kibana (rather than @timestamp, which just tells me when I imported the CSV). In the CSV file, the date column is formatted as follows:
Date
9/17/2021
9/16/2021
9/15/2021
9/15/2021
9/14/2021
9/13/2021
9/13/2021
9/13/2021
9/7/2021
9/7/2021
9/2/2021
9/1/2021
9/1/2021
9/1/2021
8/31/2021

My logstash filter block looks like this:

filter {
  csv {
    columns => [ "Date", "Description", "Withdrawals", "Deposits", "Balance" ]
  }
  mutate {
    gsub => [
      "Withdrawals", "[$,]", "",
	  "Deposits", "[$,]", "",
	  "Balance", "[$,]", ""
    ]
  }
}

...and I have an index template with the following mapping:

{
  "properties": {
    "Deposits": {
      "scaling_factor": 100,
      "type": "scaled_float"
    },
    "Withdrawals": {
      "scaling_factor": 100,
      "type": "scaled_float"
    },
    "Balance": {
      "scaling_factor": 100,
      "type": "scaled_float"
    },
    "Date": {
      "type": "date"
    }
  }
}

...when I do not map the Date field as date, it is imported as text and not available as the definitive time field. However when I use the template above, I get the following WARNING:

[2021-09-19T12:32:55,126][WARN ][logstash.outputs.elasticsearch][pipeline-name][xxxxxxxxxxxxxx] Could not index event to Elasticsearch. {:status=>400, :action=>["index", {:_id=>nil, :_index=>"pipeline-name-2021.09.19", :routing=>nil}, {"Balance"=>"10.00", "host"=>"ComputerName", "message"=>""06/28/2021","DETAILS"\r", "Date"=>"06/28/2021", "Withdrawals"=>"10.00", "Deposits"=>"", "path"=>"C:/Path/To/File.csv", "@timestamp"=>2021-09-19T16:32:50.236Z, "Description"=>"DESCRIPTION", "Category"=>"category", "@version"=>"1"}], :response=>{"index"=>{"_index"=>"index-name-2021.09.19", "_type"=>"_doc", "_id"=>"xxxxxxxxxxxxx", "status"=>400, "error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [Date] of type [date] in document with id 'xxxxxxxxxxx'. Preview of field's value: '06/28/2021'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [06/28/2021] with format [strict_date_optional_time||epoch_millis]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Failed to parse with all enclosed parsers"}}}}}}

I have tried using the "convert" setting in the CSV filter plugin to identify the "Date" field as a "date" type, but that yield the same outcome.

2

You could parse the field using a date filter to change the [date] field into a LogStash::Timestamp object. That will be sent to elasticsearch as a number of milliseconds since the epoch.

"failed to parse date field [06/28/2021] with format [strict_date_optional_time||epoch_millis]"

The default parsers for a field configured as a "date" in elasticsearch can handle milliseconds since the epoch.

If you do not want to do the conversion in logstash then configure a date parser as part of your elasticsearch mapping. I think it would be

"Date": {
  "type": "date",
  "format": "M/d/y"
}

You would need to read up on DateTimeFormatter to decide whether you might want u or Y instead of y and whether you want to use yyyy instead of y.

1 Like
3

Thank you so much, @Badger - that did the trick!

I added the date filter plugin as you recommended. However, the default target is @timestamp, which resulted in every row of the csv generating its own index - not what I intended. So I did the following instead:

date {
    match => [ "Date", "M/d/yyyy" ]
    target => "Date"
  }

...then set chose the Date field instead of @timestamp when creating the index pattern. This worked perfectly, so thank you again for pointing me in the right direction.

4

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