Date Column has some rows with NULL - strict_date_optional_time causes Exception

Elastic Stack Elasticsearch
1

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
image367×509 4.9 KB

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?

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"}}}}}}

                "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"
2

Hmmmm not what I see...

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"
          ]
        }
      }
    ]
  }
}
3

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?

                "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

{
    "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"
            }
          }
        ]
    }
4

Here are full samples of current state of things

csv data

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

{
    "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

{
    "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"
            }
          }
        ]
    }
5

@stephenb RE: Unable to convert [0.0] to long - Elastic Stack / Elasticsearch - Discuss the Elastic Stack

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.

        "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

"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

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"}}}}}}
6

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 POSTed 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...

1 Like
7

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

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"
1 Like
8

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.

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.

9

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...

10

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?

11

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

1 Like
12

Far frm likely

My index patterns for this data set follow

{
    "index_patterns": [
      "reams-workorder-logs-*"
    ],

The earlier data sets are clearly, other names.

I have checked double and triple checked my docker-compose.yml.

13

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

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 :slight_smile:

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

1 Like
14

Take note the lines with # are lines commented away successfully in VSCode.

docker-compose

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

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
image761×491 74 KB

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

15

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

1 Like
16

image
image1565×464 106 KB

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.

17

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

18

I believe u referring to this as header?

image
image1272×48 2.05 KB

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?

19

Yes

Does not look like an issue but just offering ideas...

Clearly still an issue somewhere :wink:

20

Ok I realised that Data Ingestion is happening but the logs are reflecting the ingestion trail in this form:

image

I believe I should actually see a neat JSON log trail of every "field", every corresponding value similar to
image

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

How did extra rows appear in my Index?