# Date Column has some rows with NULL - strict\_date\_optional\_time causes Exception **URL:** \ **Category:** Elasticsearch\ **Created:** [December 31, 2023, 8:14pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164 "2023-12-31T20:14:27Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [December 31, 2023, 8:14pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/1 "2023-12-31T20:14:27Z")
All this time, I use `[strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S]` To parse in columns with dates. Now, I have a csv file whose columns have rows mixture of NULL and Dates. ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/b/9b9270c381c521e585a875faca259c0ef4ebf7dd.png) Because of this inconsistency, I get the document parsing exception when trying to ingest to Elasticsearch. Even when I use ` "ignore_malformed": true` on all my date-related columns in the mapping, still doesn't work. How to solve the issue? ```auto response=>{"index"=>{"status"=>400, "error"=>{"type"=>"document_parsing_exception", "reason"=>"[1:7054] failed to parse field [r_xxx_mms_ar_workorder_xt_actfinish] of type [date] in document with id 'bDJ7wYwBRHaUpJXLsOf5'. Preview of field's value: 'NULL'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [NULL] with format [strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Failed to parse with all enclosed parsers"}}}}}} ``` ```auto "r_xxx_mms_ar_workorder_xt_actfinish": { "type": "date", "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S" ``` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [December 31, 2023, 9:27pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/2 "2023-12-31T21:27:17Z")
Hmmmm not what I see... ```auto DELETE discuss-test PUT discuss-test/ { "mappings": { "properties": { "r_xxx_mms_ar_workorder_xt_actfinish": { "type": "date", "ignore_malformed": true, "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S" } } } } POST discuss-test/_doc { "r_xxx_mms_ar_workorder_xt_actfinish": "2023-12-20 00:00:00.000" } POST discuss-test/_doc { "r_xxx_mms_ar_workorder_xt_actfinish": "NULL" } GET discuss-test/_search { "_source": ["*"], "fields": ["*"] } # Results { "took": 1, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 2, "relation": "eq" }, "max_score": 1, "hits": [ { "_index": "discuss-test", "_id": "Ke_EwYwBsqdyNt92wrpK", "_score": 1, "_source": { "r_xxx_mms_ar_workorder_xt_actfinish": "2023-12-20 00:00:00.000" }, "fields": { "r_xxx_mms_ar_workorder_xt_actfinish": [ "2023-12-20T00:00:00.000Z" ] } }, { "_index": "discuss-test", "_id": "Ku_EwYwBsqdyNt92zbqC", "_score": 1, "_ignored": [ "r_xxx_mms_ar_workorder_xt_actfinish" ], "_source": { "r_xxx_mms_ar_workorder_xt_actfinish": "NULL" }, "ignored_field_values": { "r_xxx_mms_ar_workorder_xt_actfinish": [ "NULL" ] } } ] } } ``` ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 2:55am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/3 "2024-01-01T02:55:04Z")
That's strange I had already updated the mapping with `"ignore_malformed": true`. The exception is still being raised. Does order of the settings matter? ```auto "r_xxx_mms_ar_workorder_xt_actfinish": { "type": "date", "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S", "ignore_malformed": true }, ``` For the Index Pipeline, not sure if I missed out anything ```auto { "description": "Ingest pipeline created by text structure finder", "processors": [ { "csv": { "field": "message", "target_fields": [ "r_xxx_mms_ar_workorder_xt_workorderid", "r_xxx_mms_ar_workorder_xt_actfinish", "r_xxx_mms_ar_workorder_xt_actintlabhrs", "r_xxx_mms_ar_workorder_xt_actlabhrs", "r_xxx_mms_ar_workorder_xt_actoutlabhrs", "r_xxx_mms_ar_workorder_xt_actstart", "r_xxx_mms_ar_workorder_xt_acttoolcost", "r_xxx_mms_ar_workorder_xt_ala_asset_id", "r_xxx_mms_ar_workorder_xt_amcrew", "r_xxx_mms_ar_workorder_xt_ams", "r_xxx_mms_ar_workorder_xt_aos", "r_xxx_mms_ar_workorder_xt_apptrequired", "r_xxx_mms_ar_workorder_xt_assetlocpriority", "r_xxx_mms_ar_workorder_xt_assetnum", "r_xxx_mms_ar_workorder_xt_assignedownergroup", "r_xxx_mms_ar_workorder_xt_availstatusdate", "r_xxx_mms_ar_workorder_xt_backoutplan", "r_xxx_mms_ar_workorder_xt_calccalendar", "r_xxx_mms_ar_workorder_xt_calcorgid", "r_xxx_mms_ar_workorder_xt_calcpriority", "r_xxx_mms_ar_workorder_xt_calcshift", "r_xxx_mms_ar_workorder_xt_calendar", "r_xxx_mms_ar_workorder_xt_changeby", "r_xxx_mms_ar_workorder_xt_changedate", "r_xxx_mms_ar_workorder_xt_chargestore", "r_xxx_mms_ar_workorder_xt_cinum", "r_xxx_mms_ar_workorder_xt_classstructureid", "r_xxx_mms_ar_workorder_xt_commodity", "r_xxx_mms_ar_workorder_xt_commoditygroup", "r_xxx_mms_ar_workorder_xt_crewid", "r_xxx_mms_ar_workorder_xt_crewworkgroup", "r_xxx_mms_ar_workorder_xt_description", "r_xxx_mms_ar_workorder_xt_dirissuemtlstatus", "r_xxx_mms_ar_workorder_xt_disabled", "r_xxx_mms_ar_workorder_xt_downtime", "r_xxx_mms_ar_workorder_xt_environment", "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks", "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks_ops", "r_xxx_mms_ar_workorder_xt_eqpt_failure", "r_xxx_mms_ar_workorder_xt_eqpt_failure_ops", "r_xxx_mms_ar_workorder_xt_estatapprintlabhrs", "r_xxx_mms_ar_workorder_xt_estatapprlabhrs", "r_xxx_mms_ar_workorder_xt_estatapproutlabhrs", "r_xxx_mms_ar_workorder_xt_estatapprtoolcost", "r_xxx_mms_ar_workorder_xt_estdur", "r_xxx_mms_ar_workorder_xt_estintlabhrs", "r_xxx_mms_ar_workorder_xt_estlabhrs", "r_xxx_mms_ar_workorder_xt_estoutlabhrs", "r_xxx_mms_ar_workorder_xt_esttoolcost", "r_xxx_mms_ar_workorder_xt_externalrefid", "r_xxx_mms_ar_workorder_xt_faildate", "r_xxx_mms_ar_workorder_xt_failurecode", "r_xxx_mms_ar_workorder_xt_fdnum", "r_xxx_mms_ar_workorder_xt_fincntrlid", "r_xxx_mms_ar_workorder_xt_firstapprstatus", "r_xxx_mms_ar_workorder_xt_flowaction", "r_xxx_mms_ar_workorder_xt_flowactionassist", "r_xxx_mms_ar_workorder_xt_flowcontrolled", "r_xxx_mms_ar_workorder_xt_fnlconstraint", "r_xxx_mms_ar_workorder_xt_fromchainage", "r_xxx_mms_ar_workorder_xt_generatedforpo", "r_xxx_mms_ar_workorder_xt_genforpolineid", "r_xxx_mms_ar_workorder_xt_genforporevision", "r_xxx_mms_ar_workorder_xt_haschildren", "r_xxx_mms_ar_workorder_xt_hasfollowupwork", "r_xxx_mms_ar_workorder_xt_hasld", "r_xxx_mms_ar_workorder_xt_historyflag", "r_xxx_mms_ar_workorder_xt_ifce_req", "r_xxx_mms_ar_workorder_xt_ifce_sender", "r_xxx_mms_ar_workorder_xt_ignorediavail", "r_xxx_mms_ar_workorder_xt_ignoresrmavail", "r_xxx_mms_ar_workorder_xt_inaccessible_remarks", "r_xxx_mms_ar_workorder_xt_inaccessible_time", "r_xxx_mms_ar_workorder_xt_inctasksinsched", "r_xxx_mms_ar_workorder_xt_inspector", "r_xxx_mms_ar_workorder_xt_interruptible", "r_xxx_mms_ar_workorder_xt_istask", "r_xxx_mms_ar_workorder_xt_jobtaskid", "r_xxx_mms_ar_workorder_xt_jpnum", "r_xxx_mms_ar_workorder_xt_justifypriority", "r_xxx_mms_ar_workorder_xt_kpi_rpt_inputs", "r_xxx_mms_ar_workorder_xt_langcode", "r_xxx_mms_ar_workorder_xt_lastcopylinkdate", "r_xxx_mms_ar_workorder_xt_launchentryname", "r_xxx_mms_ar_workorder_xt_lead", "r_xxx_mms_ar_workorder_xt_lms", "r_xxx_mms_ar_workorder_xt_location", "r_xxx_mms_ar_workorder_xt_los", "r_xxx_mms_ar_workorder_xt_maint_act_ty", "r_xxx_mms_ar_workorder_xt_measuredate", "r_xxx_mms_ar_workorder_xt_measurementvalue", "r_xxx_mms_ar_workorder_xt_nestedjpinprocess", "r_xxx_mms_ar_workorder_xt_newchildclass", "r_xxx_mms_ar_workorder_xt_newserialnum", "r_xxx_mms_ar_workorder_xt_observation", "r_xxx_mms_ar_workorder_xt_oldserialnum", "r_xxx_mms_ar_workorder_xt_onbehalfof", "r_xxx_mms_ar_workorder_xt_ops_rpt_inputs", "r_xxx_mms_ar_workorder_xt_ops_wkly_rpt_inputs", "r_xxx_mms_ar_workorder_xt_orgid", "r_xxx_mms_ar_workorder_xt_origrecordclass", "r_xxx_mms_ar_workorder_xt_origrecordid", "r_xxx_mms_ar_workorder_xt_outtoolcost", "r_xxx_mms_ar_workorder_xt_owner", "r_xxx_mms_ar_workorder_xt_ownergroup", "r_xxx_mms_ar_workorder_xt_ownersysid", "r_xxx_mms_ar_workorder_xt_parent", "r_xxx_mms_ar_workorder_xt_parentchgsstatus", "r_xxx_mms_ar_workorder_xt_partchangeout", "r_xxx_mms_ar_workorder_xt_persongroup", "r_xxx_mms_ar_workorder_xt_pluscfrequency", "r_xxx_mms_ar_workorder_xt_pluscfrequnit", "r_xxx_mms_ar_workorder_xt_pluscismobile", "r_xxx_mms_ar_workorder_xt_pluscjprevnum", "r_xxx_mms_ar_workorder_xt_pluscloop", "r_xxx_mms_ar_workorder_xt_pluscnextdate", "r_xxx_mms_ar_workorder_xt_pluscoverduedate", "r_xxx_mms_ar_workorder_xt_pluscphyloc", "r_xxx_mms_ar_workorder_xt_pmcombpelactname", "r_xxx_mms_ar_workorder_xt_pmcombpelenabled", "r_xxx_mms_ar_workorder_xt_pmcombpelinprog", "r_xxx_mms_ar_workorder_xt_pmcomstate", "r_xxx_mms_ar_workorder_xt_pmcomtype", "r_xxx_mms_ar_workorder_xt_pmduedate", "r_xxx_mms_ar_workorder_xt_pmextdate", "r_xxx_mms_ar_workorder_xt_pmnextduedate", "r_xxx_mms_ar_workorder_xt_pmnum", "r_xxx_mms_ar_workorder_xt_pointnum", "r_xxx_mms_ar_workorder_xt_problemcode", "r_xxx_mms_ar_workorder_xt_reasonforchange", "r_xxx_mms_ar_workorder_xt_remdur", "r_xxx_mms_ar_workorder_xt_repairfacility", "r_xxx_mms_ar_workorder_xt_repfacsiteid", "r_xxx_mms_ar_workorder_xt_reportdate", "r_xxx_mms_ar_workorder_xt_reportedby", "r_xxx_mms_ar_workorder_xt_reqasstdwntime", "r_xxx_mms_ar_workorder_xt_respondby", "r_xxx_mms_ar_workorder_xt_risk", "r_xxx_mms_ar_workorder_xt_route", "r_xxx_mms_ar_workorder_xt_routestopid", "r_xxx_mms_ar_workorder_xt_rptstatus", "r_xxx_mms_ar_workorder_xt_schedfinish", "r_xxx_mms_ar_workorder_xt_schedstart", "r_xxx_mms_ar_workorder_xt_sendersysid", "r_xxx_mms_ar_workorder_xt_serviceaffect", "r_xxx_mms_ar_workorder_xt_siteid", "r_xxx_mms_ar_workorder_xt_sneconstraint", "r_xxx_mms_ar_workorder_xt_sourcesysid", "r_xxx_mms_ar_workorder_xt_status", "r_xxx_mms_ar_workorder_xt_statusdate", "r_xxx_mms_ar_workorder_xt_storeroommtlstatus", "r_xxx_mms_ar_workorder_xt_supervisor", "r_xxx_mms_ar_workorder_xt_suspendflow", "r_xxx_mms_ar_workorder_xt_sys_downtime", "r_xxx_mms_ar_workorder_xt_targcompdate", "r_xxx_mms_ar_workorder_xt_targetdesc", "r_xxx_mms_ar_workorder_xt_targstartdate", "r_xxx_mms_ar_workorder_xt_taskid", "r_xxx_mms_ar_workorder_xt_tochainage", "r_xxx_mms_ar_workorder_xt_user_schedfinish", "r_xxx_mms_ar_workorder_xt_user_schedstart", "r_xxx_mms_ar_workorder_xt_vendor", "r_xxx_mms_ar_workorder_xt_verification", "r_xxx_mms_ar_workorder_xt_whomischangefor", "r_xxx_mms_ar_workorder_xt_wo_downtime", "r_xxx_mms_ar_workorder_xt_wo_ptw_type", "r_xxx_mms_ar_workorder_xt_wo_tar_accesstype", "r_xxx_mms_ar_workorder_xt_woacceptscharges", "r_xxx_mms_ar_workorder_xt_woclass", "r_xxx_mms_ar_workorder_xt_wogroup", "r_xxx_mms_ar_workorder_xt_woisswap", "r_xxx_mms_ar_workorder_xt_wonum", "r_xxx_mms_ar_workorder_xt_wopriority", "r_xxx_mms_ar_workorder_xt_worklocation", "r_xxx_mms_ar_workorder_xt_workpackmtlstatus", "r_xxx_mms_ar_workorder_xt_worktype", "r_xxx_mms_ar_workorder_xt_wosequence", "r_xxx_mms_ar_workorder_xt_ingestion_time", "r_xxx_mms_ar_workorder_xt_flowid", "r_xxx_mms_ar_workorder_xt_dat_qlty", "r_xxx_mms_ar_workorder_xt_ingestion_date", "r_xxx_mms_ar_workorder_xt_business_date" ], "ignore_missing": false } }, { "date": { "field": "r_xxx_mms_ar_workorder_xt_reportdate", "timezone": "Asia/Singapore", "formats": [ "strict_date_optional_time", "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S" ] } }, { "date": { "field": "r_xxx_mms_ar_workorder_xt_reportdate", "timezone": "Asia/Singapore", "target_field": "r_xxx_mms_ar_workorder_xt_reportdate", "formats": [ "strict_date_optional_time", "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S" ] } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actintlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actoutlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_acttoolcost", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_ams", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_aos", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_apptrequired", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_changeby", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_chargestore", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_esttoolcost", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_workorderid", "type": "double", "ignore_missing": true } }, { "remove": { "field": "message" } } ] } ``` ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 3:51am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/4 "2024-01-01T03:51:36Z")
Here are full samples of current state of things csv data ```auto 1033545,NULL,0.0,0.0,0.0,2022-12-27 03:58:52.248,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:29:54.147,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:29:54.648,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-11-20,NULL,2023-12-01,PM000000098432,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:58:52.873,126692,0,NULL,NULL,2023 PWY-ML-IS/06,NULL,NULL,2023-11-20 00:00:00.0,2023-11-20 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:29:31.592,NULL,131758.0,0,NULL,2023-11-22 00:00:00.0,NULL,2023-11-18 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428761,0,WO00428761,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 1033546,NULL,0.0,0.0,0.0,2022-12-27 03:58:53.592,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:29:54.71,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:29:55.195,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-12-01,NULL,2023-12-12,PM000000098432,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:58:54.218,126692,0,NULL,NULL,2023 PWY-ML-IS/06,NULL,NULL,2023-12-01 00:00:00.0,2023-12-01 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:29:31.592,NULL,131758.0,0,NULL,2023-12-03 00:00:00.0,NULL,2023-11-29 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428762,0,WO00428762,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 1033547,NULL,0.0,0.0,0.0,2022-12-27 03:58:56.454,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:29:55.273,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:29:55.758,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-12-12,NULL,2023-12-23,PM000000098432,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:58:57.423,126692,0,NULL,NULL,2023 PWY-ML-IS/06,NULL,NULL,2023-12-12 00:00:00.0,2023-12-12 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:29:31.592,NULL,131758.0,0,NULL,2023-12-14 00:00:00.0,NULL,2023-12-10 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428763,0,WO00428763,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 1022097,2022-12-29 16:30:00.0,0.0,0.0,0.0,2022-12-29 15:30:00.0,0.0,NULL,NULL,0,0,0,NULL,A000000104451,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,132578,2022-12-30 16:30:47.062,0,NULL,NULL,NULL,NULL,NULL,NULL,[RCR] 3 Monthly PM For PA Stations Equipment Maintenance - DDMMMYY,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,0.5,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,1,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00000972,NULL,NULL,EN,2022-12-30 16:30:47.25,NULL,NULL,0,L000000129248,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,COM,3.0,MONTHS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-01,2022-12-01,2023-03-01,PM000000062795,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-01 17:28:29.664,129782,0,NULL,NULL,PA_DT13_3M_STNEQPT,NULL,NULL,2022-12-01 00:00:00.0,2022-12-01 00:00:00.0,NULL,0,xxx,NULL,NULL,COMP,2022-12-30 16:30:43.457,NULL,NULL,0,NULL,2023-03-31 00:00:00.0,NULL,2022-12-01 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00422876,0,WO00422876,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,PASS,20230308,20230221 1022098,2022-12-30 16:30:43.457,0.0,0.0,0.0,2022-12-01 17:28:28.836,0.0,NULL,NULL,0,0,0,NULL,A000000104451,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,132578,2022-12-30 16:30:47.5,0,NULL,NULL,NULL,NULL,NULL,NULL,xxx-COM-PA-STN-001,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,0.5,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,1,5362.0,NULL,NULL,NULL,EN,2022-12-01 17:28:30.242,NULL,NULL,0,L000000129248,0,NULL,NULL,NULL,0,ACTIVITY,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,WO00422876,1,0,NULL,NULL,NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-01 17:28:28.836,129782,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,xxx,NULL,NULL,COMP,2022-12-30 16:30:43.457,NULL,NULL,0,NULL,2022-12-01 00:30:00.0,NULL,2022-12-01 00:00:00.0,1.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,ACTIVITY,WO00422876,0,TK00622622,NULL,NULL,NULL,PM,1.0,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,PASS,20230308,20230221 1033559,NULL,0.0,0.0,0.0,2022-12-27 03:59:16.413,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:31:30.329,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx to xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:31:30.845,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-04-26,NULL,2023-05-07,PM000000098433,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:59:17.148,126692,0,NULL,NULL,2023 PWY-ML-IS/07,NULL,NULL,2023-04-26 00:00:00.0,2023-04-26 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:31:22.605,NULL,131758.0,0,NULL,2023-04-28 00:00:00.0,NULL,2023-04-24 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428775,0,WO00428775,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 1033552,NULL,0.0,0.0,0.0,2022-12-27 03:59:05.009,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:31:32.659,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx to xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:31:33.159,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-08,NULL,2023-02-19,PM000000098433,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:59:05.775,126692,0,NULL,NULL,2023 PWY-ML-IS/07,NULL,NULL,2023-02-08 00:00:00.0,2023-02-08 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:31:22.605,NULL,131758.0,0,NULL,2023-02-10 00:00:00.0,NULL,2023-02-06 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428768,0,WO00428768,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 1033553,NULL,0.0,0.0,0.0,2022-12-27 03:59:06.698,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:31:33.237,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx to xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:31:33.722,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-19,NULL,2023-03-02,PM000000098433,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 03:59:07.497,126692,0,NULL,NULL,2023 PWY-ML-IS/07,NULL,NULL,2023-02-19 00:00:00.0,2023-02-19 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:31:22.605,NULL,131758.0,0,NULL,2023-02-21 00:00:00.0,NULL,2023-02-17 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428769,0,WO00428769,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221 ``` Index-Template ```auto { "index_patterns": [ "reams-workorder-logs-*" ], "template": { "settings": { "index.mapping.ignore_malformed": true }, "mappings": { "_meta": { "created_by": "file-data-visualizer" }, "properties": { "@timestamp": { "type": "date", "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S", "ignore_malformed": true }, "r_xxx_mms_ar_workorder_xt_actfinish": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_actintlabhrs": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_actlabhrs": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_actoutlabhrs": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_actstart": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_acttoolcost": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_ala_asset_id": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_amcrew": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ams": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_aos": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_apptrequired": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_assetlocpriority": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_assetnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_assignedownergroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_availstatusdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_backoutplan": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_business_date": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_calccalendar": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_calcorgid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_calcpriority": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_calcshift": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_calendar": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_changeby": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_changedate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_chargestore": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_cinum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_classstructureid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_commodity": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_commoditygroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_crewid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_crewworkgroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_dat_qlty": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_description": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_dirissuemtlstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_disabled": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_downtime": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_environment": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks_ops": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_eqpt_failure": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_eqpt_failure_ops": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estatapprintlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estatapprlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estatapproutlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estatapprtoolcost": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estdur": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estintlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_estoutlabhrs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_esttoolcost": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_externalrefid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_faildate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_failurecode": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_fdnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_fincntrlid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_firstapprstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_flowaction": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_flowactionassist": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_flowcontrolled": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_flowid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_fnlconstraint": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_fromchainage": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_generatedforpo": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_genforpolineid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_genforporevision": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_haschildren": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_hasfollowupwork": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_hasld": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_historyflag": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ifce_req": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ifce_sender": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ignorediavail": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ignoresrmavail": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_inaccessible_remarks": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_inaccessible_time": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_inctasksinsched": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ingestion_date": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ingestion_time": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_inspector": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_interruptible": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_istask": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_jobtaskid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_jpnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_justifypriority": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_kpi_rpt_inputs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_langcode": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_lastcopylinkdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_launchentryname": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_lead": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_lms": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_location": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_los": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_maint_act_ty": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_measuredate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_measurementvalue": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_nestedjpinprocess": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_newchildclass": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_newserialnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_observation": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_oldserialnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_onbehalfof": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ops_rpt_inputs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ops_wkly_rpt_inputs": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_orgid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_origrecordclass": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_origrecordid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_outtoolcost": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_owner": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ownergroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_ownersysid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_parent": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_parentchgsstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_partchangeout": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_persongroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscfrequency": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscfrequnit": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscismobile": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscjprevnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscloop": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscnextdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscoverduedate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pluscphyloc": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmcombpelactname": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmcombpelenabled": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmcombpelinprog": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmcomstate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmcomtype": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmduedate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmextdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmnextduedate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pmnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_pointnum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_problemcode": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_reasonforchange": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_remdur": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_repairfacility": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_repfacsiteid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_reportdate": { "type": "date", "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S", "ignore_malformed": true }, "r_xxx_mms_ar_workorder_xt_reportedby": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_reqasstdwntime": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_respondby": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_risk": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_route": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_routestopid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_rptstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_schedfinish": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_schedstart": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_sendersysid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_serviceaffect": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_siteid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_sneconstraint": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_sourcesysid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_status": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_statusdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_storeroommtlstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_supervisor": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_suspendflow": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_sys_downtime": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_targcompdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_targetdesc": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_targstartdate": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_taskid": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_tochainage": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_user_schedfinish": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_user_schedstart": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_vendor": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_verification": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_whomischangefor": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wo_downtime": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wo_ptw_type": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wo_tar_accesstype": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_woacceptscharges": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_woclass": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wogroup": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_woisswap": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wonum": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wopriority": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_worklocation": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_workorderid": { "type": "double" }, "r_xxx_mms_ar_workorder_xt_workpackmtlstatus": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_worktype": { "type": "keyword" }, "r_xxx_mms_ar_workorder_xt_wosequence": { "type": "keyword" } } } } } ``` Pipeline ```auto { "description": "Ingest pipeline created by text structure finder", "processors": [ { "csv": { "field": "message", "target_fields": [ "r_xxx_mms_ar_workorder_xt_workorderid", "r_xxx_mms_ar_workorder_xt_actfinish", "r_xxx_mms_ar_workorder_xt_actintlabhrs", "r_xxx_mms_ar_workorder_xt_actlabhrs", "r_xxx_mms_ar_workorder_xt_actoutlabhrs", "r_xxx_mms_ar_workorder_xt_actstart", "r_xxx_mms_ar_workorder_xt_acttoolcost", "r_xxx_mms_ar_workorder_xt_ala_asset_id", "r_xxx_mms_ar_workorder_xt_amcrew", "r_xxx_mms_ar_workorder_xt_ams", "r_xxx_mms_ar_workorder_xt_aos", "r_xxx_mms_ar_workorder_xt_apptrequired", "r_xxx_mms_ar_workorder_xt_assetlocpriority", "r_xxx_mms_ar_workorder_xt_assetnum", "r_xxx_mms_ar_workorder_xt_assignedownergroup", "r_xxx_mms_ar_workorder_xt_availstatusdate", "r_xxx_mms_ar_workorder_xt_backoutplan", "r_xxx_mms_ar_workorder_xt_calccalendar", "r_xxx_mms_ar_workorder_xt_calcorgid", "r_xxx_mms_ar_workorder_xt_calcpriority", "r_xxx_mms_ar_workorder_xt_calcshift", "r_xxx_mms_ar_workorder_xt_calendar", "r_xxx_mms_ar_workorder_xt_changeby", "r_xxx_mms_ar_workorder_xt_changedate", "r_xxx_mms_ar_workorder_xt_chargestore", "r_xxx_mms_ar_workorder_xt_cinum", "r_xxx_mms_ar_workorder_xt_classstructureid", "r_xxx_mms_ar_workorder_xt_commodity", "r_xxx_mms_ar_workorder_xt_commoditygroup", "r_xxx_mms_ar_workorder_xt_crewid", "r_xxx_mms_ar_workorder_xt_crewworkgroup", "r_xxx_mms_ar_workorder_xt_description", "r_xxx_mms_ar_workorder_xt_dirissuemtlstatus", "r_xxx_mms_ar_workorder_xt_disabled", "r_xxx_mms_ar_workorder_xt_downtime", "r_xxx_mms_ar_workorder_xt_environment", "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks", "r_xxx_mms_ar_workorder_xt_eqpt_fail_remarks_ops", "r_xxx_mms_ar_workorder_xt_eqpt_failure", "r_xxx_mms_ar_workorder_xt_eqpt_failure_ops", "r_xxx_mms_ar_workorder_xt_estatapprintlabhrs", "r_xxx_mms_ar_workorder_xt_estatapprlabhrs", "r_xxx_mms_ar_workorder_xt_estatapproutlabhrs", "r_xxx_mms_ar_workorder_xt_estatapprtoolcost", "r_xxx_mms_ar_workorder_xt_estdur", "r_xxx_mms_ar_workorder_xt_estintlabhrs", "r_xxx_mms_ar_workorder_xt_estlabhrs", "r_xxx_mms_ar_workorder_xt_estoutlabhrs", "r_xxx_mms_ar_workorder_xt_esttoolcost", "r_xxx_mms_ar_workorder_xt_externalrefid", "r_xxx_mms_ar_workorder_xt_faildate", "r_xxx_mms_ar_workorder_xt_failurecode", "r_xxx_mms_ar_workorder_xt_fdnum", "r_xxx_mms_ar_workorder_xt_fincntrlid", "r_xxx_mms_ar_workorder_xt_firstapprstatus", "r_xxx_mms_ar_workorder_xt_flowaction", "r_xxx_mms_ar_workorder_xt_flowactionassist", "r_xxx_mms_ar_workorder_xt_flowcontrolled", "r_xxx_mms_ar_workorder_xt_fnlconstraint", "r_xxx_mms_ar_workorder_xt_fromchainage", "r_xxx_mms_ar_workorder_xt_generatedforpo", "r_xxx_mms_ar_workorder_xt_genforpolineid", "r_xxx_mms_ar_workorder_xt_genforporevision", "r_xxx_mms_ar_workorder_xt_haschildren", "r_xxx_mms_ar_workorder_xt_hasfollowupwork", "r_xxx_mms_ar_workorder_xt_hasld", "r_xxx_mms_ar_workorder_xt_historyflag", "r_xxx_mms_ar_workorder_xt_ifce_req", "r_xxx_mms_ar_workorder_xt_ifce_sender", "r_xxx_mms_ar_workorder_xt_ignorediavail", "r_xxx_mms_ar_workorder_xt_ignoresrmavail", "r_xxx_mms_ar_workorder_xt_inaccessible_remarks", "r_xxx_mms_ar_workorder_xt_inaccessible_time", "r_xxx_mms_ar_workorder_xt_inctasksinsched", "r_xxx_mms_ar_workorder_xt_inspector", "r_xxx_mms_ar_workorder_xt_interruptible", "r_xxx_mms_ar_workorder_xt_istask", "r_xxx_mms_ar_workorder_xt_jobtaskid", "r_xxx_mms_ar_workorder_xt_jpnum", "r_xxx_mms_ar_workorder_xt_justifypriority", "r_xxx_mms_ar_workorder_xt_kpi_rpt_inputs", "r_xxx_mms_ar_workorder_xt_langcode", "r_xxx_mms_ar_workorder_xt_lastcopylinkdate", "r_xxx_mms_ar_workorder_xt_launchentryname", "r_xxx_mms_ar_workorder_xt_lead", "r_xxx_mms_ar_workorder_xt_lms", "r_xxx_mms_ar_workorder_xt_location", "r_xxx_mms_ar_workorder_xt_los", "r_xxx_mms_ar_workorder_xt_maint_act_ty", "r_xxx_mms_ar_workorder_xt_measuredate", "r_xxx_mms_ar_workorder_xt_measurementvalue", "r_xxx_mms_ar_workorder_xt_nestedjpinprocess", "r_xxx_mms_ar_workorder_xt_newchildclass", "r_xxx_mms_ar_workorder_xt_newserialnum", "r_xxx_mms_ar_workorder_xt_observation", "r_xxx_mms_ar_workorder_xt_oldserialnum", "r_xxx_mms_ar_workorder_xt_onbehalfof", "r_xxx_mms_ar_workorder_xt_ops_rpt_inputs", "r_xxx_mms_ar_workorder_xt_ops_wkly_rpt_inputs", "r_xxx_mms_ar_workorder_xt_orgid", "r_xxx_mms_ar_workorder_xt_origrecordclass", "r_xxx_mms_ar_workorder_xt_origrecordid", "r_xxx_mms_ar_workorder_xt_outtoolcost", "r_xxx_mms_ar_workorder_xt_owner", "r_xxx_mms_ar_workorder_xt_ownergroup", "r_xxx_mms_ar_workorder_xt_ownersysid", "r_xxx_mms_ar_workorder_xt_parent", "r_xxx_mms_ar_workorder_xt_parentchgsstatus", "r_xxx_mms_ar_workorder_xt_partchangeout", "r_xxx_mms_ar_workorder_xt_persongroup", "r_xxx_mms_ar_workorder_xt_pluscfrequency", "r_xxx_mms_ar_workorder_xt_pluscfrequnit", "r_xxx_mms_ar_workorder_xt_pluscismobile", "r_xxx_mms_ar_workorder_xt_pluscjprevnum", "r_xxx_mms_ar_workorder_xt_pluscloop", "r_xxx_mms_ar_workorder_xt_pluscnextdate", "r_xxx_mms_ar_workorder_xt_pluscoverduedate", "r_xxx_mms_ar_workorder_xt_pluscphyloc", "r_xxx_mms_ar_workorder_xt_pmcombpelactname", "r_xxx_mms_ar_workorder_xt_pmcombpelenabled", "r_xxx_mms_ar_workorder_xt_pmcombpelinprog", "r_xxx_mms_ar_workorder_xt_pmcomstate", "r_xxx_mms_ar_workorder_xt_pmcomtype", "r_xxx_mms_ar_workorder_xt_pmduedate", "r_xxx_mms_ar_workorder_xt_pmextdate", "r_xxx_mms_ar_workorder_xt_pmnextduedate", "r_xxx_mms_ar_workorder_xt_pmnum", "r_xxx_mms_ar_workorder_xt_pointnum", "r_xxx_mms_ar_workorder_xt_problemcode", "r_xxx_mms_ar_workorder_xt_reasonforchange", "r_xxx_mms_ar_workorder_xt_remdur", "r_xxx_mms_ar_workorder_xt_repairfacility", "r_xxx_mms_ar_workorder_xt_repfacsiteid", "r_xxx_mms_ar_workorder_xt_reportdate", "r_xxx_mms_ar_workorder_xt_reportedby", "r_xxx_mms_ar_workorder_xt_reqasstdwntime", "r_xxx_mms_ar_workorder_xt_respondby", "r_xxx_mms_ar_workorder_xt_risk", "r_xxx_mms_ar_workorder_xt_route", "r_xxx_mms_ar_workorder_xt_routestopid", "r_xxx_mms_ar_workorder_xt_rptstatus", "r_xxx_mms_ar_workorder_xt_schedfinish", "r_xxx_mms_ar_workorder_xt_schedstart", "r_xxx_mms_ar_workorder_xt_sendersysid", "r_xxx_mms_ar_workorder_xt_serviceaffect", "r_xxx_mms_ar_workorder_xt_siteid", "r_xxx_mms_ar_workorder_xt_sneconstraint", "r_xxx_mms_ar_workorder_xt_sourcesysid", "r_xxx_mms_ar_workorder_xt_status", "r_xxx_mms_ar_workorder_xt_statusdate", "r_xxx_mms_ar_workorder_xt_storeroommtlstatus", "r_xxx_mms_ar_workorder_xt_supervisor", "r_xxx_mms_ar_workorder_xt_suspendflow", "r_xxx_mms_ar_workorder_xt_sys_downtime", "r_xxx_mms_ar_workorder_xt_targcompdate", "r_xxx_mms_ar_workorder_xt_targetdesc", "r_xxx_mms_ar_workorder_xt_targstartdate", "r_xxx_mms_ar_workorder_xt_taskid", "r_xxx_mms_ar_workorder_xt_tochainage", "r_xxx_mms_ar_workorder_xt_user_schedfinish", "r_xxx_mms_ar_workorder_xt_user_schedstart", "r_xxx_mms_ar_workorder_xt_vendor", "r_xxx_mms_ar_workorder_xt_verification", "r_xxx_mms_ar_workorder_xt_whomischangefor", "r_xxx_mms_ar_workorder_xt_wo_downtime", "r_xxx_mms_ar_workorder_xt_wo_ptw_type", "r_xxx_mms_ar_workorder_xt_wo_tar_accesstype", "r_xxx_mms_ar_workorder_xt_woacceptscharges", "r_xxx_mms_ar_workorder_xt_woclass", "r_xxx_mms_ar_workorder_xt_wogroup", "r_xxx_mms_ar_workorder_xt_woisswap", "r_xxx_mms_ar_workorder_xt_wonum", "r_xxx_mms_ar_workorder_xt_wopriority", "r_xxx_mms_ar_workorder_xt_worklocation", "r_xxx_mms_ar_workorder_xt_workpackmtlstatus", "r_xxx_mms_ar_workorder_xt_worktype", "r_xxx_mms_ar_workorder_xt_wosequence", "r_xxx_mms_ar_workorder_xt_ingestion_time", "r_xxx_mms_ar_workorder_xt_flowid", "r_xxx_mms_ar_workorder_xt_dat_qlty", "r_xxx_mms_ar_workorder_xt_ingestion_date", "r_xxx_mms_ar_workorder_xt_business_date" ], "ignore_missing": false } }, { "date": { "field": "r_xxx_mms_ar_workorder_xt_reportdate", "timezone": "Asia/Singapore", "formats": [ "strict_date_optional_time", "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S" ] } }, { "date": { "field": "r_xxx_mms_ar_workorder_xt_reportdate", "timezone": "Asia/Singapore", "target_field": "r_xxx_mms_ar_workorder_xt_reportdate", "formats": [ "strict_date_optional_time", "yyyy-MM-dd HH:mm:ss.SSS", "yyyy-MM-dd HH:mm:ss.SS", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm:ss.S" ] } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actintlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_actoutlabhrs", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_acttoolcost", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_ams", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_aos", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_apptrequired", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_changeby", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_chargestore", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_esttoolcost", "type": "double", "ignore_missing": true } }, { "convert": { "field": "r_xxx_mms_ar_workorder_xt_workorderid", "type": "double", "ignore_missing": true } }, { "remove": { "field": "message" } } ] } ``` ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 4:41am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/5 "2024-01-01T04:41:41Z")
@stephenb RE: [Unable to convert [0.0] to long - Elastic Stack / Elasticsearch - Discuss the Elastic Stack](https://discuss.elastic.co/t/unable-to-convert-0-0-to-long/350031/10) My reply to you i think more relevant in this thread. I can't see any difference. I ran the docker container logstash, opened the logs trail in wsl terminal. Same date time parsing error. Nothing changed. ```auto "original" => "1033718,NULL,0.0,0.0,0.0,2022-12-27 04:03:12.526,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:35:29.11,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:35:29.595,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-21,NULL,2023-03-04,PM000000098438,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 04:03:13.167,126692,0,NULL,NULL,2023 PWY-ML-IS/12,NULL,NULL,2023-02-21 00:00:00.0,2023-02-21 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:34:02.356,NULL,131758.0,0,NULL,2023-02-23 00:00:00.0,NULL,2023-02-19 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428934,0,WO00428934,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221" }, "message" => "1033718,NULL,0.0,0.0,0.0,2022-12-27 04:03:12.526,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:35:29.11,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:35:29.595,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-21,NULL,2023-03-04,PM000000098438,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 04:03:13.167,126692,0,NULL,NULL,2023 PWY-ML-IS/12,NULL,NULL,2023-02-21 00:00:00.0,2023-02-21 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:34:02.356,NULL,131758.0,0,NULL,2023-02-23 00:00:00.0,NULL,2023-02-19 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428934,0,WO00428934,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221", ``` Now, I made the rest of the date fields into `keyword` so that I don't get this date issue. Only the `@timestamp` and `r_xxx_mms_ar_workorder_xt.reportdate` column is date format. Timestamp is inherited from `r_xxx_mms_ar_workorder_xt.reportdate` as per the interface you taught me in Nov 2023. Even so, I did not change the date/time formats available. My time stamps here match `yyyy-MM-dd HH:mm:ss.SSS` ```auto "format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S", ``` And still I get a date-time parsing error over a column that I clearly redefined to `keyword` (`r_xxx_mms_ar_workorder_xt_actfinish`) and re-consoled the updated index and mapping template? This confuses me ```auto error"=>{"type"=>"document_parsing_exception", "reason"=>"[1:7102] failed to parse field [r_dtl_mms_ar_workorder_xt_actfinish] of type [date] in document with id 'hj5Cw4wBRHaUpJXLebEA'. Preview of field's value: 'NULL'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [NULL] with format [strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Failed to parse with all enclosed parsers"}}}}}} ``` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 5:20am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/6 "2024-01-01T05:20:35Z")
I put in your template but just added the `message` field I changed the pipeline / simplified it, I think you made it too complicated for no added value the converts are not needed just put the correct types in the template mappings Then I just `POST`ed a few docs... and took a look... The results look good to me. No Errors Take a look at this GIST to see all my code and results > That dates seem fine You can see I put some doubles / floats in the second message. I think this solves your `0.0` issues Which also means I am not sure what your issue is... I also tried putting in a few more date time fields as above for example `r_xxx_mms_ar_workorder_xt_actstart` and it worked fine as well I just added that to the gist... ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 6:01am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/7 "2024-01-01T06:01:55Z")
BTW I ran your test file above through filebeat and the pipeline and it all 8 lines loaded fine... This is my complete filebeat using the template and pipeline in the gist ```auto filebeat.inputs: # Each - is an input. Most options can be set at the input level, so # you can use different inputs for various configurations. # Below are the input-specific configurations. # filestream is an input for collecting log messages from files. - type: filestream # Unique ID among all inputs, an ID is required. id: my-filestream-id # Change to true to enable this input configuration. enabled: true # Paths that should be crawled and fetched. Glob based paths. paths: - /Users/sbrown/workspace/sample-data/discuss/discuss-custom-csv.csv #- c:\programdata\elasticsearch\logs\* setup.template.enabled: false setup.ilm.enabled: false setup.kibana: output.elasticsearch.hosts: ["localhost:9200"] output.elasticsearch.index: "reams-workorder-logs-test" output.elasticsearch.pipeline: "reams-workorder-logs" ``` ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 6:11am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/8 "2024-01-01T06:11:17Z")
Still facing issue. Once again the parsing exception is hit on the first column that is a date field (aka 2nd column in the whole csv) `[r_xxx_mms_ar_workorder_xt_actfinish] `. This is really strange. It works on your end. I also tried on the console - it also seem to work. Only when I do `docker-compose up -d` the logstash container, then its problematic. ```auto reason"=>"[1:7058] failed to parse field [r_xxx_mms_ar_workorder_xt_actfinish] of type [date] in document with id 'ZUChw4wBRHaUpJXLoZT3'. Preview of field's value: 'NULL'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [NULL] with format [strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Failed to parse with all enclosed parsers"}}}}}} ``` Every time I will `compose-down` and then up once more. The most updated mapping and pipeline console input has already been included. The `ignore_malformed` setting is not even being listened to this is frustrating. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 7:02am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/9 "2024-01-01T07:02:26Z")
I suspect in your compose that the correct template is not being applied...why I am.not sure... Perhaps a race condition... Or a typo or old template that is being matched etc... Why you are using logstash.. I can't remember... If you post a doc to and index and it works .. if you write to that index from any client (logstash/filebeat) it should work... ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 7:24am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/10 "2024-01-01T07:24:10Z")
Reason Im using logstash is because my data files are 100,000+ rows. Yeah i had a feeling my configuration may not be updated for a reason...but i never had this isssue until now. Made sure i save my config files before docker compose What do u mean by Race btw? ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 7:53am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/11 "2024-01-01T07:53:55Z")
> [@Ethan777100](#): > > What do u mean by Race btw? Meaning data is written before the template is written ... I am not saying it is that... But if the template/ mapping is there is will take affect. Do you have other templates? There is a concept of priorities....do you have another template that is matching the index pattern ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 3:46pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/12 "2024-01-01T15:46:47Z")
Far frm likely My index patterns for this data set follow ```auto { "index_patterns": [ "reams-workorder-logs-*" ], ``` The earlier data sets are clearly, other names. I have checked double and triple checked my docker-compose.yml. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 3:55pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/13 "2024-01-01T15:55:43Z")
Share you docker compose again? I might separate the elasticsearch from the logstash in separate compose files. Get Elasticsearch up and running make sure everything is correct... Leave it running. Then run logstash separately > [@Ethan777100](#): > > Far frm likely > > My index patterns for this data set follow Not sure what means... Just saying if during testing you put another template with same pattern that could be an issue... You have an issue...you just need to find it... And usually when you get to this point it's something we're sure couldn't happen 🙂 What I'm saying is look closely to make sure you don't have a leftover template or something like that. It seems like it's not using the correct template ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 3:58pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/14 "2024-01-01T15:58:14Z")
Take note the lines with `#` are lines commented away successfully in VSCode. docker-compose ```auto version: "3.8" # volumes: # logstashdata01: # driver: local networks: default: name: elastic external: true services: logstash: image: docker.elastic.co/logstash/logstash:${STACK_VERSION} labels: co.elastic.logs/module: logstash user: root environment: - xpack.monitoring.enabled=false volumes: - ./:/usr/share/logstash/pipeline/ #- ./ats-logs-mainline/2022/07-Jul-22:/usr/share/logstash/ats-logs-mainline/2022/07-Jul-22 #ats-logs #- ./cmd/2022/08-Aug-22/:/usr/share/logstash/cmd/2022/08-Aug-22 #cmd-logs - ./workorder:/usr/share/logstash/workorder #workorder-logs command: logstash -r -f /usr/share/logstash/pipeline/logstash.conf ports: - "5044:5044" mem_limit: ${LS_MEM_LIMIT} ``` logstash.conf ```auto input { file { #path => "/usr/share/logstash/ats-logs-mainline/2022/07-Jul-22/events2022-07-*.csv" #ats-logs #path => "/usr/share/logstash/cmd/2022/08-Aug-22/202208*.csv" #cmd-logs path => "/usr/share/logstash/workorder/reams_workorder2023*.csv" #workorder-logs start_position => "beginning" } } filter { mutate { gsub => ["message", "\r", ""] } } output { elasticsearch { #index => "ats-mainline-logs-2022-07" #ats-logs #index => "cmd-logs-2022-08" #cmd-logs index => "reams-workorder-logs-2023" #workorder-logs hosts => ["https://es01:9200"] manage_template => false user => "elastic" password => "elastic123" ssl_verification_mode=> "none" #pipeline => "ats-mainline-logs-pipeline" #ats-logs #pipeline => "cmd-logs-pipeline" #cmd-logs pipeline => "reams-workorder-pipeline" #workorder-logs } stdout{ codec => "rubydebug"} } ``` Btw Im already running logstash as sep docker container from elasticsearch/kibana. ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/2/e2475adc26daaac08f7045629d82209d54881129.png) ES/Kib have been running flawlessly since Nov 2023. NEver had an issue since. Whenever I docker-compose up/down today, its just for `logstash-standard` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 4:00pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/15 "2024-01-01T16:00:15Z")
Good on the separate running. That's good Login to Kibana and look for all the templates. Make sure there isn't any that you don't expect ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 4:02pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/16 "2024-01-01T16:02:32Z")
![image](https://us1.discourse-cdn.com/elastic/original/3X/a/7/a7bc6211c8f234171426db4fc14b6d083614960f.png) I have always kept a Tab on Index Templates since last year. I track this almost every moment I ingest new information. Here are the 3 index templates I'm using right now. The rest are "Managed" / already there. ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 4:12pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/17 "2024-01-01T16:12:37Z")
Does your file have a header row? If so take it out , clean up the index and try again see what happens. Or post 1 doc manually first and then run the file... Again there is something non-obvious you are going to need to try some different things "that shouldn't matter" but one will. And when you inspect your 3 templates there is no index pattern overlap ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 4:22pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/18 "2024-01-01T16:22:00Z")
I believe u referring to this as header? ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/b/7b6fd86f4e1adaaaf27e05746e08520103ba6a53.png) > And when you inspect your 3 templates there is no index pattern overlap ats-mainline-logs-template || ats-mainline-logs-\* cmd-logs-template || cmd-logs-\* reams-workorder-template ||reams-workorder-logs-\* clearly no overlap? ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [January 1, 2024, 4:30pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/19 "2024-01-01T16:30:04Z")
> [@Ethan777100](#): > > I believe u referring to this as header? Yes > [@Ethan777100](#): > > clearly no overlap? Does not look like an issue but just offering ideas... Clearly still an issue somewhere 😉 ---
**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\ **Post date:** [January 1, 2024, 4:44pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/20 "2024-01-01T16:44:53Z")
Ok I realised that Data Ingestion is happening but the logs are reflecting the ingestion trail in this form: ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/b/6b66f1cb2aad66fe337aa82291fa7e2c64f7d776.png) I believe I should actually see a neat JSON log trail of every "field", every corresponding value similar to ![image](https://us1.discourse-cdn.com/elastic/original/3X/5/e/5ea3b07178456036706437e6a61cbe621eb2a7d6.png) I still think something is wrong - perfect ingestion should see log result of every field mapped to every value. Meanwhile I check Discover, I realised I have 184,482 hits from this huge csv file, however my csv file only has 159,123 rows. ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/9/e9b690fee5f911ca1a563f1c5f5cc6cb8961a584.png) How did extra rows appear in my Index? [Next page](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164.md?page=2)