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!

daks · Answer 1 · 2017-07-21T14:11:09.863

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'

ok, will try. Thanks for the info. Can you provide an example of a template? — Elliot Huffman, Jul 22 '17 at 09:50
Nvm, here is an example: https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping.html#_example_mapping — Elliot Huffman, Jul 22 '17 at 09:53
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! — Elliot Huffman, Jul 24 '17 at 07:19

score 0 · Answer 2 · answered Jul 21 '17 at 13:22

I'm guessing here, because I am not familiar with the platforms and programs you are talking about. However, in your screenshot you stated you changed the data type to Duration, but it looks like the data type is still 'number', and the 'format' is duration. If I had to continue to guess, I'd say that your platform is still trying to serialize your field there as a number, because its data type is still 'number'. Change that type to 'date' like the @timestamp field at the top of the screenshot, and see if that fixes it.

Unfortunately I do not know how to do that. Thanks for your input :-) — Elliot Huffman, Jul 22 '17 at 09:50

Elliot Huffman · Accepted Answer · 2017-07-24T11:00:21.587

0

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

edited Jul 24 '17 at 11:00

answered Jul 24 '17 at 07:18

Elliot Huffman

1,169
1
10
22

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. – daks Jul 24 '17 at 08:56
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. – Elliot Huffman Jul 24 '17 at 11:01

Date field is being presented as integer

Code:

Screen shots:

3 Answers3