Why my date format is not working?

pk.241011 · November 3, 2019, 11:46pm

Here is the field I get from filebeat:

PC_Local_Time_1and the value it has is 2019-10-15T10:54:27.447Z.

I tried to convert it to Date field in Elasticsearch pipeline as below:

{
      "date": {
        "field": "PC_Local_Time_1",
        "target_field": "Conv_PC_Local_Time_1", 
        "formats": ["yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
      }
}

But in the end it gets logged into the elasticsearch as text.

I took potshots at it by putting this in the filebeat.yml.

setup.template.append_fields:
- name: Conv_PC_Local_Time_1
  type: date

Still nothing happened. It still is getting mapped as text.

spinscale · November 4, 2019, 10:09am

did you (re)create that index after doing the append_fields change? Can you share a reproducible example, including the ingest pipeline, and your filebeat configuration in order to reproduce?

pk.241011 · November 4, 2019, 10:51am

Will take me sometime to create a small working example. Will update it as soon as I have one.

Meanwhile as the answer to first question, during testing I delete the whole index as this is the only way to get rid of the mapping unless filebeat is doing something which I do not yet know.

BTW, the Elasticsearch version: 7.2.0

spinscale · November 4, 2019, 1:40pm

did you also delete the filebeat index template while testing?

pk.241011 · November 5, 2019, 2:54am

I deleted the index first and then the filebeat template:

DELETE demo-2019.11.05-000001
DELETE /_template/filebeat-7.2.0

Still the same error after indexing it.

My filebeat config:

setup.template.append_fields:
- name: PC_Local_Time_2_temp
  type: date

filebeat.inputs:
- paths:
    -  C:\Data\Projects\Demo\**\*.csv
  input_type: log
  multiline.pattern: '^\D'
  multiline.negate: true
  multiline.match: after  

output.elasticsearch:
 hosts: ["http://localhost:9200"]
 pipeline: demo_pipe
  
setup.ilm.enabled: auto
setup.ilm.rollover_alias: "demo"
setup.ilm.pattern: "{now/d}-000001"
  
logging.level: info
logging.to_files: true
logging.files:
 path: C:\filebeatStuff\logs
 name: filebeat
 keepfiles: 7
 permissions: 0644

Pipeline is too long to be put here but the relevant section is this:

    {
      "date": {
        "field": "PC_Local_Time_2",
        "target_field": "PC_Local_Time_2_temp", 
        "formats": ["yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
      }
    }

spinscale · November 5, 2019, 10:16am

So I used your filebeat config, ran filebeat setup and tried the following

PUT _ingest/pipeline/demo-pipeline
{
  "processors": [
    {
      "date": {
        "field": "PC_Local_Time_2",
        "target_field": "PC_Local_Time_2_temp", 
        "formats": ["yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
      }
    }
  ]
}

GET _template/demo?filter_path=**.PC_Local_Time_2_temp

PUT demo/_doc/1?pipeline=demo-pipeline
{
  "PC_Local_Time_2" : "2019-02-28T12:34:56.789Z"
}

# this shows the pipeline temp field
GET demo/_doc/1

Is it possible that your documents read from filebeat are lacking the required field to be enriched?

pk.241011 · November 5, 2019, 11:43am

I was trying to create a small working sample for you. In the process I am a bit more lost than before. Essentially what is happening is that if I create a fresh index then actually things work. But any change later on and I start getting strings. I am at loss of words.

A small csv file called tester.csv as source of data:

12225,2015-10-15T11:07:39.776Z
33342,2016-12-11T11:01:22.454Z

A simple pipeline called demo_pipeline to break the csv:

PUT _ingest/pipeline/demo_pipeline
{
  "description": "demo pipeline",
  "processors": [
    {
      "split": {
        "field": "message",
        "separator": ",",
        "target_field": "splitdata"
      }
    },
    {
      "script": {
        "lang": "painless",
        "source": """
                  ctx.ID = ctx.splitdata[0];
                  ctx.PC_Local_Time_2 = ctx.splitdata[1]
                  """
      }
    },
    {
      "date": {
        "field": "PC_Local_Time_2",
        "target_field": "PC_Local_Time_2", 
        "formats": ["yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
      }
    },
    {
      "remove": {
        "ignore_missing": true, 
        "field": [
          "splitdata"
          ]
      }
    }
  ]
}

And the filebeat_demo.yml:

setup.template.append_fields:
- name: PC_Local_Time_2
  type: date

filebeat.inputs:
- paths:
  -  C:\Data\tester.csv

  input_type: log

output.elasticsearch:
 hosts: ["http://localhost:9200"]
 pipeline: demo_pipeline
  
setup.ilm.enabled: auto
setup.ilm.rollover_alias: "try"
setup.ilm.pattern: "{now/d}-000001"
  
logging.level: info
logging.to_files: true
logging.files:
 path: C:\filebeatStuff\logs
 name: filebeat
 keepfiles: 7
 permissions: 0644

Just to test out the pipeline in the console:

GET _ingest/pipeline/demo_pipeline/_simulate
{
  "docs": [
    {
      "_source": {
        "message": "12,2019-10-17T11:07:39.776Z"
      }
    }
  ]
}

Result:

 {
  "docs" : [
    {
      "doc" : {
        "_index" : "_index",
        "_type" : "_doc",
        "_id" : "_id",
        "_source" : {
          "PC_Local_Time_2" : "2019-10-17T11:07:39.776Z",
          "ID" : "12",
          "message" : "12,2019-10-17T11:07:39.776Z"
        },
        "_ingest" : {
          "timestamp" : "2019-11-05T11:26:22.910Z"
        }
      }
    }
  ]
}

Then the actual run. And it works.

Then I make a small change in pipeline. I put in a different field as target. And this is without deleting the filebeat template.

{
      "date": {
        "field": "PC_Local_Time_2",
        "target_field": "PC_Local_Time_conv",
        "formats": ["yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"]
      }
 },

In the yml, I change the second line:

setup.template.append_fields:
- name: PC_Local_Time_conv
  type: date

And now on running the same thing:

I delete the index and the template this time before trying again.

This succeeds
DELETE try-2019.11.05-000001

This fails as I had already deleted this during experimentation before.
DELETE /_template/filebeat-7.2.0

And the result is same.

Not sure how helpful the details have been.

spinscale · November 6, 2019, 9:49am

can you share the mapping from the try index? I would like to keep kibana out of the equation and use standard requests for anything in order to reduce the problem space.

pk.241011 · November 6, 2019, 12:11pm

It is a 4k lines long mapping !! Can't fit it in here. Any sections of mapping you will be interested in? I can cut that out and paste it here.

spinscale · November 7, 2019, 8:45am

Hey,

see the filter_path example in my snippet above to reduce the JSON being returned.

--Alex

pk.241011 · November 7, 2019, 11:16am

I think this is what you were looking for?

GET /try/_mapping/field/PC_Local_Time_2

{
  "try-2019.11.05-000001" : {
    "mappings" : {
      "PC_Local_Time_2" : {
        "full_name" : "PC_Local_Time_2",
        "mapping" : {
          "PC_Local_Time_2" : {
            "type" : "date"
          }
        }
      }
    }
  }
}

And

GET /try/_mapping/field/PC_Local_Time_conv

{
  "try-2019.11.05-000001" : {
    "mappings" : {
      "PC_Local_Time_conv" : {
        "full_name" : "PC_Local_Time_conv",
        "mapping" : {
          "PC_Local_Time_conv" : {
            "type" : "keyword",
            "ignore_above" : 1024
          }
        }
      }
    }
  }
}

spinscale · November 7, 2019, 3:07pm

what is the index template looking like for those two fields?

system · December 5, 2019, 3:07pm

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

Topic		Replies	Views
Import CSV with date fields not usable as timestamp field Beats filebeat	7	626	June 12, 2023
Problem with JSON date types Beats	3	402	September 10, 2019
Using index template to read a field as date Beats filebeat	5	4215	September 19, 2018
Filebeat sending json date field as text Beats filebeat	12	2977	November 12, 2018
Filebeats creating the incorrect datatype in index Beats filebeat	5	663	October 3, 2019

Why my date format is not working?

Related topics