How to Visualize Metrics Data in a Kibana Canvas Element with using ESSQL Functions

[Bu721 (Burak)]

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?

2. 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

3. 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?

4. 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?

5. 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

[thomasneirynck]

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.