Failed to Parse Date Field

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.

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

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.

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