Converting a string date to a Date field using scripted fields in kibana

[iam.Carrot (Aditya Sharma)]

Hi I am working on ELK stack. I have a date in the form of a string like below:

"23/Nov/2017:02:35:02 +0000"

Now I want to use scripted fields in kibana to convert the string date time to a date field.

Anyone can help me with what to put in the script? or How can I go about it?

[ppisljar (Peter Pisljar)]

I would really suggest against that if you have more than just a few records of data. You should reindex your data and make sure date time is stored correctly. Doing this with scripted fields will be painfully slow. Not 100% but i guess elastic will always have to go over every record and do conversion and then return the subset you are interested in.

that being said, if you look at painless reference: https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-api-reference.html

you can see it supports SimpleDateFormat

looking at oracle documentation: https://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

something like this should work:

 new SimpleDateFormat('dd/MMM/yyyy:HH:mm:ss Z').parse(doc['fieldname'].value); 

[iam.Carrot (Aditya Sharma)]

@ppisljar
When I use the code line I get the below error:

Error: Request to Elasticsearch failed: {"error":{"root_cause":[{"type":"script_exception","reason":"runtime error","script_stack":["org.elasticsearch.index.mapper.TextFieldMapper$TextFieldType.fielddataBuilder(TextFieldMapper.java:336)","org.elasticsearch.index.fielddata.IndexFieldDataService.getForField(IndexFieldDataService.java:111)","org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:84)","org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:81)","java.security.AccessController.doPrivileged(Native Method)","org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:81)","org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:36)","new SimpleDateFormat('dd/MMM/yyyy:HH:mm:ss Z').parse(doc['timestamp'].value);"," ^---- HERE"],"script":"new SimpleDateFormat('dd/MMM/yyyy:HH:mm:ss Z').parse(doc['timestamp'].value);","lang":"painless"}],"type":"search_phase_execution_exception","reason":"all shards failed","phase":"fetch","grouped":true,"failed_shards":[{"shard":0,"index":"logstash-20171123","node":"1Wv31tGWRwenm-3kYqPPBQ","reason":{"type":"script_exception","reason":"runtime error","script_stack":["org.elasticsearch.index.mapper.TextFieldMapper$TextFieldType.fielddataBuilder(TextFieldMapper.java:336)","org.elasticsearch.index.fielddata.IndexFieldDataService.getForField(IndexFieldDataService.java:111)","org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:84)","org.elasticsearch.search.lookup.LeafDocLookup$1.run(LeafDocLookup.java:81)","java.security.AccessController.doPrivileged(Native Method)","org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:81)","org.elasticsearch.search.lookup.LeafDocLookup.get(LeafDocLookup.java:36)","new SimpleDateFormat('dd/MMM/yyyy:HH:mm:ss Z').parse(doc['timestamp'].value);"," ^---- HERE"],"script":"new SimpleDateFormat('dd/MMM/yyyy:HH:mm:ss Z').parse(doc['timestamp'].value);","lang":"painless","caused_by":{"type":"illegal_argument_exception","reason":"Fielddata is disabled on text fields by default. Set fielddata=true on [timestamp] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead."}}}]},"status":500} at http://10.10.21.29:5601/bundles/kibana.bundle.js?v=15443:28:6898 at Function.Promise.try (http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:82:22224) at http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:82:21594 at Array.map (<anonymous>) at Function.Promise.map (http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:82:21549) at callResponseHandlers (http://10.10.21.29:5601/bundles/kibana.bundle.js?v=15443:28:6514) at http://10.10.21.29:5601/bundles/kibana.bundle.js?v=15443:27:26896 at processQueue (http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:38:23621) at http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:38:23888 at Scope.$eval (http://10.10.21.29:5601/bundles/commons.bundle.js?v=15443:39:4619)

The link to my config image is https://1drv.ms/i/s!AsbwVCO9nR4ogvRDFJ87osiOZEzhDw

[ppisljar (Peter Pisljar)]

well, of course, right now you are returning a java date, you need to convert that to unix epoch time (getTime()).

also, you should not set the format of your date in scripted field, as that will convert it to string again (leave the format at default)