Date field is being presented as integer

Question

I have an Elastic Stack server (on Hyper-v) that is ingesting data via a logstash exec command and performing analytics on it. Everything is working great except a date field that is being displayed as a number.

How do I get logstash, Elasticsearch or Kibana to recognize the field s a date instead of a number?

The data is Unix epoch time in milliseconds.

---

Code:

Data outputted by the python file is in JSON format. No real processing is taking place until it hits elasticsearch.

Logstash config:

input {
  exec {
    command => "/home/elliot/BullhornConnector.py JobOrder isOpen,webResponses,submissions,sendouts,interviews,placements,address,numOpenings,employmentType,owner,title,clientCorporation"
    interval => 60
    codec => json
    tags => ["JobOrder"]
  }
  exec {
    command => "/home/elliot/BullhornConnector.py Lead owner,leadSource,firstName,lastName,status,dateAdded"
    interval => 60
    codec => json
    tags => ["Lead"]
  }
  exec {
    command => "/home/elliot/BullhornConnector.py Opportunity owner,isOpen,dealValue,weightedDealValue,clientCorporation,status"
    interval => 60
    codec => json
    tags => ["Opportunity"]
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
  }
  stdout { codec => rubydebug }
}

---

Screen shots:

Here is a screenshot of the raw data:

Index pattern overview page:

Detailed view of the field:

Thanks!

Solution 1

I have figured it out: What you need to do is use a filter plugin in logstash, specifically the date plugin.

Here is the snippet that I added to my logstash config:

filter {
  date {
    match => [ "dateAdded", "UNIX_MS" ]
    target => "dateAddedCorrected"
  }
}

Solution 2

If I read correctly the ElasticSearch documentation https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html

JSON doesn’t have a date datatype, so dates in Elasticsearch can either be:

strings containing formatted dates, e.g. "2015-01-01" or "2015/01/01 12:10:30".
a long number representing milliseconds-since-the-epoch.
an integer representing seconds-since-the-epoch. 

So your dateAdded field represented as an "number" data type is logical: Elasticsearch simply translated a JSON number to an ES number.

If I look at my own ELK instance, I found that the "timestamp" field is represented as a "date" data type. It's done automatically by logstash.

Behind the scene, logstash manage a 'mapping template' to define ES fields data types. In your case, it naively translate the date type from JSON and in the case of the timestamp it knows that it's a date so explicitely define it .

So what you need to do is define a mapping template and use logstash to push it to ES with your data.

ES mapping doc is here https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html and Logstash can manage it with manage_template and template in elasticsearch output https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-template. An introduction to AS mapping https://www.elastic.co/blog/found-elasticsearch-mapping-introduction.

You can also look at the mapping actually being in use with

curl -XGET 'localhost:9200/<index>/_mapping?pretty'

Comments

[Elliot Huffman]

Unfortunately I do not know how to do that. Thanks for your input :-)

[Elliot Huffman]

ok, will try. Thanks for the info. Can you provide an example of a template?

[Elliot Huffman]

Nvm, here is an example: elastic.co/guide/en/elasticsearch/reference/current/…

[Elliot Huffman]

Thank you for pointing me in the right direction. It did not work for me. You can see what worked for me in my answer. Thank you again for your guidance!

[daks]

In fact, this is the way to go if you want dateAdded to be the logstash event timestamp. If it's just a date field but not your timestamp, you must use a mapping template.

[Elliot Huffman]

It turns out that this filter plugin can output to a different field than the time stamp, see the target section? That outputs to a different field that is date type.