# How to Visualize Metrics Data in a Kibana Canvas Element with using ESSQL Functions **URL:** \ **Category:** Elasticsearch\ **Created:** [August 6, 2020, 4:40pm UTC](https://discuss.elastic.co/t/how-to-visualize-metrics-data-in-a-kibana-canvas-element-with-using-essql-functions/244067 "2020-08-06T16:40:25Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Bu721](https://avatars.discourse-cdn.com/v4/letter/b/35a633/32.png) [@Bu721](https://discuss.elastic.co/u/Bu721)\ **Post date:** [August 6, 2020, 4:40pm UTC](https://discuss.elastic.co/t/how-to-visualize-metrics-data-in-a-kibana-canvas-element-with-using-essql-functions/244067/1 "2020-08-06T16:40:25Z")
HI! I am currently working on a metrics visualization in a Kibana Canvas Element. My goal is to calculate the current throughput of the processing process of items in an array and render the returned value into a Canvas-Metrics-Element. Concrete: In every new Elasticsearch document the size of the array field can decrease or increase in every subsequent document. Because the size of the items list can also increase in size I want to define a query that ignores already processed items. **My Problem: Is it possible to calculate it with using only out-of-the-box query functions like combining multiple queries that can be used as one query to render the calculated value in a Kibana Canvas Element?** In this context I have 5 issues: 1. Usage of array functions like COUNT in ESSQL 2. Usage of functions like INTERSECTION of SQL in ESSQL 3. Reuse of Sub-Queries in ESSQL 4. Usage multiple indices in ESSQL 5. Realization of this calculation using Canvas Expression Language **---------------------------------------------------------------------------------------------------------** 1. First I am going to get the counted items of the document of the earliest time of the considered periode (e.g. LAST 1 HOUR): `SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp ASC LIMIT 1` - **Here I also want to use an ESSQL array function on the field 'items', that counts all items in that array. Which ESSQL function can realize this?** 1. Same for the counted items of the document of the latest time of the considered periode (1 HOUR): `SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1` 2. Next Subquery: Get the count of items of the intersected Set of items between the set of the earliest and latest set of items. `SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp ASC LIMIT 1 INTERSECT SELECT items FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1 ` - **Are there any similar functions in ESSQL like INTERSECTION I can use to get the intersected set of two array fields?** 1. Get the difference value of the counted items of the earliest document and the counted items of the latest document of the considered periode. (Query 1) - (Query 2) - **How can I reuse subqueries in ESSQL?** 1. Reuse all defined subqueries (Query 1 - 4) in **one ESSQ Query** or other query methods to calculate e.g. like the throughput via subqueries and render the output in an Kibana Canvas. I am happy to hear any other methods to make these kind of calculations work using subqueries. Most important: It has to work in a Kibana Canvas Environment. - **Is it possible to realize this calculation with this given index (see index mapping) with using the Canvas Expression Language?** Also I thought about using something like this: ``` SELECT A.size - B.size AS Throughput_per_Second FROM ( SELECT size FROM index_name WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1 ) A , ( SELECT size FROM index_name WHERE WHERE timestamp < NOW() AND timestamp > NOW() - INTERVAL 1 HOUR AND ORDER BY timestamp ASC LIMIT 1 ) B WHERE A.size >= B.size ``` - But unfortunately multiple indices aren't supported in ESSQL. **- Are there any other similar ways to use multiple indices/tables in an ESSQL query?** The mapping of the only index: ``` { "mappings": { "_doc": { "properties": { "items": { "type": "long" }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "size": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "timestamp": { "type": "date" } } } } } ``` Kind Regards, Burak ---
**Author:** ![thomasneirynck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thomasneirynck/32/23313_2.png) [@thomasneirynck](https://discuss.elastic.co/u/thomasneirynck)\ **Post date:** [August 6, 2020, 8:16pm UTC](https://discuss.elastic.co/t/how-to-visualize-metrics-data-in-a-kibana-canvas-element-with-using-essql-functions/244067/2 "2020-08-06T20:16:40Z")
hi @Bu721, Since these are pretty much all questions about using ESSQL, I am going to move this to the Elasticsearch channel, since you might have more luck there getting an answer. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 3, 2020, 8:16pm UTC](https://discuss.elastic.co/t/how-to-visualize-metrics-data-in-a-kibana-canvas-element-with-using-essql-functions/244067/3 "2020-09-03T20:16:48Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.