# How to normalize for time in Kibana **URL:** \ **Category:** Kibana\ **Created:** [April 30, 2020, 7:08pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624 "2020-04-30T19:08:21Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![vstevenson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vstevenson/32/54354_2.png) [@vstevenson](https://discuss.elastic.co/u/vstevenson)\ **Post date:** [April 30, 2020, 7:08pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/1 "2020-04-30T19:08:22Z")
In my index, I am tracking the performance of widgets. I have the install date of each widget. I would like to determine which widgets have had the most number of parts replaced on them per time, accounting for the fact that not all widgets were installed at the same time. How would I enter the formula: (total number of parts replaced on a widget)/(number of days that widget has been in service) in Kibana? Thanks! ---
**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\ **Post date:** [April 30, 2020, 7:18pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/2 "2020-04-30T19:18:47Z")
It's a really interesting question. I think we can do this with Timelion, but I would like to ask how many widgets you have and if the installation date is on each document or if it is stored in a single document. Do you have few simple documents to play with? ---
**Author:** ![vstevenson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vstevenson/32/54354_2.png) [@vstevenson](https://discuss.elastic.co/u/vstevenson)\ **Post date:** [April 30, 2020, 7:21pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/3 "2020-04-30T19:21:55Z")
Hi Luca, there are about 50 widgets. Each document contains that widget's install date. Is this something that can be done in Kibana? I was looking at the Average Bucket Aggregation - but I'm having a hard time understanding. ---
**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\ **Post date:** [April 30, 2020, 7:45pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/4 "2020-04-30T19:45:28Z")
If not directly feasible on Kibana I'll propose to use transform Jobs. I can't right now but I'll come back on this post. What version are you on? ---
**Author:** ![vstevenson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vstevenson/32/54354_2.png) [@vstevenson](https://discuss.elastic.co/u/vstevenson)\ **Post date:** [April 30, 2020, 7:55pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/5 "2020-04-30T19:55:52Z")
I am on version 6.8. Thanks! ---
**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\ **Post date:** [April 30, 2020, 11:18pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/6 "2020-04-30T23:18:23Z")
# Demo data ```auto PUT widget?include_type_name=true { "mappings": { "_doc": { "properties": { "widget_id": { "type": "long" }, "installation_date": { "type": "date" }, "@timestamp": { "type": "date" }, "replaced_parts": { "type": "short" } } } } } PUT widget/_doc/1 { "widget_id": 1, "installation_date": "2020-03-27T07:47:16.316Z", "@timestamp": "2020-04-27T07:47:16.316Z", "replaced_parts": 0 } PUT widget/_doc/2 { "widget_id": 2, "installation_date": "2019-08-07T07:47:16.316Z", "@timestamp": "2020-04-27T07:47:16.316Z", "replaced_parts": 0 } PUT widget/_doc/3 { "widget_id": 3, "installation_date": "2018-08-07T07:47:16.316Z", "@timestamp": "2020-04-27T07:47:16.316Z", "replaced_parts": 0 } PUT widget/_doc/4 { "widget_id": 1, "installation_date": "2020-04-27T07:47:16.316Z", "@timestamp": "2020-04-27T07:47:16.316Z", "replaced_parts": 1 } PUT widget/_doc/5 { "widget_id": 2, "installation_date": "2019-08-07T07:47:16.316Z", "@timestamp": "2019-12-27T07:47:16.316Z", "replaced_parts": 1 } PUT widget/_doc/6 { "widget_id": 3, "installation_date": "2018-08-07T07:47:16.316Z", "@timestamp": "2019-04-27T07:47:16.316Z", "replaced_parts": 2 } PUT widget/_doc/7 { "widget_id": 1, "installation_date": "2020-04-27T07:47:16.316Z", "@timestamp": "2020-04-27T07:47:16.316Z", "replaced_parts": 0 } PUT widget/_doc/8 { "widget_id": 2, "installation_date": "2019-08-07T07:47:16.316Z", "@timestamp": "2020-01-27T07:47:16.316Z", "replaced_parts": 1 } PUT widget/_doc/9 { "widget_id": 3, "installation_date": "2018-08-07T07:47:16.316Z", "@timestamp": "2019-04-27T07:47:16.316Z", "replaced_parts": 3 } ``` # Elasticsearch DSL ```auto GET widget/_search { "size": 0, "aggs": { "widget": { "terms": { "field": "widget_id", "size": 100 }, "aggs": { "installation_date": { "min": { "field": "installation_date" } }, "repaired_parts": { "sum": { "field": "replaced_parts" } }, "ratio": { "bucket_script": { "buckets_path": { "installation_date": "installation_date", "repaired_parts": "repaired_parts" }, "script": """ ZonedDateTime now = ZonedDateTime.ofInstant(Instant.ofEpochMilli(new Date().getTime()), ZoneId.of('Z')); ZonedDateTime installation_date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(params.installation_date.longValue()), ZoneId.of('Z')); return params.repaired_parts / Duration.between(installation_date, now).toDays(); """ } } } } } } ``` ## Result ```auto { "took" : 2, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "skipped" : 0, "failed" : 0 }, "hits" : { "total" : 9, "max_score" : 0.0, "hits" : [] }, "aggregations" : { "widget" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 1, "doc_count" : 3, "installation_date" : { "value" : 1.585295236316E12, "value_as_string" : "2020-03-27T07:47:16.316Z" }, "repaired_parts" : { "value" : 1.0 }, "ratio" : { "value" : 0.029411764705882353 } }, { "key" : 2, "doc_count" : 3, "installation_date" : { "value" : 1.565164036316E12, "value_as_string" : "2019-08-07T07:47:16.316Z" }, "repaired_parts" : { "value" : 2.0 }, "ratio" : { "value" : 0.00749063670411985 } }, { "key" : 3, "doc_count" : 3, "installation_date" : { "value" : 1.533628036316E12, "value_as_string" : "2018-08-07T07:47:16.316Z" }, "repaired_parts" : { "value" : 5.0 }, "ratio" : { "value" : 0.007911392405063292 } } ] } } } ``` # Kibana 6.8 ## Table If you have to display a table, the closest result I got is the following, which requires defining a scripted field I named `installation_since_days` in the index pattern: ```auto ZonedDateTime now = ZonedDateTime.ofInstant(Instant.ofEpochMilli(new Date().getTime()), ZoneId.of('Z')); ZonedDateTime installation_date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(doc['installation_date'][0].getMillis()), ZoneId.of('Z')); return Duration.between(installation_date, now).toDays(); ``` You will not get the ratio, but the raw numbers. ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/d/9db7252a8394313da349d11c34eed38cec4f0f3c.png) The [enhanced table plugin](https://github.com/fbaligand/kibana-enhanced-table) by @fbaligand should help you compute the ratio. ## Vega ```auto { "$schema": "https://vega.github.io/schema/vega-lite/v2.json", "data": { "url": { "%context%": true, "index": "widget", "body": { "size": 0, "aggs": { "widget": { "terms": {"field": "widget_id", "size": 100}, "aggs": { "installation_date": {"min": {"field": "installation_date"}}, "repaired_parts": {"sum": {"field": "replaced_parts"}}, "ratio": { "bucket_script": { "buckets_path": { "installation_date": "installation_date", "repaired_parts": "repaired_parts" }, "script": "ZonedDateTime now = ZonedDateTime.ofInstant(Instant.ofEpochMilli(new Date().getTime()), ZoneId.of('Z'));ZonedDateTime installation_date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(params.installation_date.longValue()), ZoneId.of('Z')); return params.repaired_parts / Duration.between(installation_date, now).toDays();" } } } } } } }, "format": {"property": "aggregations.widget.buckets"} }, "mark": "bar", "encoding": { "x": {"field": "key", "type": "ordinal", "axis": {"title": false}}, "y": {"field": "ratio.value", "type": "quantitative", "axis": {"title": "Ratio"}} } } ``` Visual result: ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/f/4feb23289ff64e1449dedfe444664a03361f790a.png) # Elasticsearch SQL Variant Elasticsearch SQL requires a Basic license. This works only with 7.5+ because the `DATE_DIFF` was introduced on 7.5. ```auto GET _sql?format=txt { "query": "SELECT widget_id, SUM(replaced_parts)/DATE_DIFF('days', MIN(installation_date), TODAY()) AS ratio FROM widget GROUP BY widget_id" } ``` ## Result ```auto widget_id | ratio ---------------+-------------------- 1 |0.030303030303030304 2 |0.007518796992481203 3 |0.00792393026941363 ``` This enables you to use [Canvas](https://www.elastic.co/guide/en/kibana/current/canvas.html)! ## Canvas As a Table: ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/a/7a1316dba384d1a60f5512f28d25a036eb34032f.png) Expression code: ```auto filters | essql query="SELECT widget_id, SUM(replaced_parts)/DATE_DIFF('days', MIN(installation_date), TODAY()) AS ratio FROM widget GROUP BY widget_id" | table | render ``` Or as a histogram: ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/2/a27181791e71cfd4d2375b7b2c20a24dff399e34.png) Expression code: ```auto filters | essql query="SELECT CAST(widget_id AS INTEGER), SUM(replaced_parts)/DATE_DIFF('days', MIN(installation_date), TODAY()) AS ratio FROM widget GROUP BY widget_id" | alterColumn "widget_id" type="string" | pointseries x="widget_id" y="ratio" color="widget_id" | plot defaultStyle={seriesStyle bars=0.5} legend=false | render ``` # Other solutions ## TSVB (with installation\_date as long) Another solution might exist with TSVB, but it requires to index the `installation_date` as `long`, expressed in `epoch` as `min`/`max` aggregations work only on `numeric` values. This is an Ingest pipeline to index the `installation_date` converting it into a `long` into the field `installation_date_long` while indexing (you have to pass `?pipeline=convert_to_long`): ```auto PUT _ingest/pipeline/convert_to_long { "description": "converts the date to epochmilli (long)", "processors": [ { "script": { "lang": "painless", "source": "ctx.installation_date_long = ZonedDateTime.parse(ctx.installation_date).toInstant().toEpochMilli();" } } ] } ``` It would work both for 6.8 and 7.x. ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/c/4ca2920c754994697930d5d9e63da3db0d734133.png) ![image](https://us1.discourse-cdn.com/elastic/original/3X/1/d/1dee6812b04b5ede2d90766deb23abb364d3ca5b.png) ## Transform Job On 7.2+ we introduced [Dataframes / Transform Jobs](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/put-transform.html). It is a way to define a continuous job which turns out time-based data into [entity centric indices](https://www.elastic.co/elasticon/2015/sf/building-entity-centric-indexes). In your case, the entity is the `widget`. ```auto POST _transform/_preview { "source": { "index": [ "widget" ] }, "pivot": { "group_by": { "widget_id": { "terms": { "field": "widget_id" } } }, "aggregations": { "installation_date": { "min": { "field": "installation_date" } }, "repaired_parts": { "sum": { "field": "replaced_parts" } }, "ratio" : { "bucket_script": { "buckets_path": { "installation_date": "installation_date", "repaired_parts": "repaired_parts" }, "script": """ ZonedDateTime now = ZonedDateTime.ofInstant(Instant.ofEpochMilli(new Date().getTime()), ZoneId.of('Z')); ZonedDateTime installation_date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(params.installation_date.longValue()), ZoneId.of('Z')); return params.repaired_parts / Duration.between(installation_date, now).toDays(); """ } } } } } ``` Full transform: ```auto PUT _transform/widget_process { "source": { "index": [ "widget" ] }, "pivot": { "group_by": { "widget_id": { "terms": { "field": "widget_id" } } }, "aggregations": { "installation_date": { "min": { "field": "installation_date" } }, "repaired_parts": { "sum": { "field": "replaced_parts" } }, "ratio": { "bucket_script": { "buckets_path": { "installation_date": "installation_date", "repaired_parts": "repaired_parts" }, "script": "ZonedDateTime now = ZonedDateTime.ofInstant(Instant.ofEpochMilli(new Date().getTime()), ZoneId.of('Z')); ZonedDateTime installation_date = ZonedDateTime.ofInstant(Instant.ofEpochMilli(params.installation_date.longValue()), ZoneId.of('Z')); return params.repaired_parts / Duration.between(installation_date, now).toDays();" } } } }, "dest": { "index": "widget_entity" }, "sync": { "time": { "field": "@timestamp", "delay": "1h" } } } ``` ---
**Author:** ![vstevenson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vstevenson/32/54354_2.png) [@vstevenson](https://discuss.elastic.co/u/vstevenson)\ **Post date:** [May 1, 2020, 3:47pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/7 "2020-05-01T15:47:00Z")
Thank you Luca, this looks super cool! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 29, 2020, 3:47pm UTC](https://discuss.elastic.co/t/how-to-normalize-for-time-in-kibana/230624/8 "2020-05-29T15:47:01Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.