_dateparsefailure for date filtering on database date column value

[ggajanan]

Hi,

I am using jdbc input and want to match @timestamp with the updatedttm column value extracted from the query execution. It is not working and also failing with _dateparsefailure.

Following is the config from logstash conf file

input {
   jdbc {
      type => "recon"
      jdbc_driver_library => "C:\tools\ELK\logstash\jdbc-drivers\db2jcc4.jar"
      jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
      jdbc_connection_string => "jdbc:db2://localhost:50000/DATABASE"
      jdbc_user => "user"
      jdbc_password => "pass"
      jdbc_validate_connection => true
      schedule => "* * * * *"
      statement => "select name, ky1, updatedttm from table1 updatedttm > :sql_last_value"
      last_run_metadata_path => "C:\tools\ELK\logstash\logstash_jdbc_last_run"
   }
}

filter {
    date {
  	match => ["updatedttm", "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]
	target => "@timestamp"
   }
}

output {
        stdout {
            codec => rubydebug
        }
}

following is the output from the execution and @timestamp does not match with updatedttm.

{
  "name" => "test1",
  "@timestamp" => 2017-04-11T05:53:00.500Z,
  "ky1" => 648503,
  "@version" => "1",
  "updatedttm" => 2016-02-18T10:02:19.190Z,
  "type" => "recon",
  "tags" => [
    [0] "_dateparsefailure"
  ]
}

Can you please suggest how can I correct this?

Thank you!

[magnusbaeck (Magnus Bäck)]

If the date filter fails it'll tell you why in the Logstash log.

[ggajanan]

Thanks!

The following mutate fixed it.

filter {
    mutate {
       convert => [ "updatedttm", "string" ]
    }
    date {
  	   match => ["updatedttm", "yyyy-MM-dd'T'HH:mm:ss.SSSZ"]
	   target => "@timestamp"
   }
}