Failed visualize data from Elastic SQL query in Canvas Bar Chart

Elastic Stack Kibana
,
1

I create a canvas board and would like to visualize data in "realtime".

My board:

image
image.png846×555 28.1 KB

My Datasource SQL query:
SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE
FROM "runtime_kibana_stream"
WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03'
GROUP BY TIMESTAMP, SENSOR
ORDER BY TIMESTAMP DESC

Datasource preview ist correct:
image

But my bar chart show me not the LAST value of the data.

Idea where ist the Problem?

thx
Otto

2

Hey @ofitz, what do you have selected for the Y-axis? Using the following shows me the expected data:

43%20AM
Screen Shot 2019-05-29 at 9.46.43 AM.png3368×1624 368 KB

3

Hi Brandon, if you get the FIRST value on Y-axis and add new Value to knife01 with new timestamp which is smaller then 5, the bar chart dont visualized it. The bar chart stays on 5 @ knife01 :frowning:

4

Would you mind providing a screen-shot of what you're seeing because I'm afraid I'm misunderstanding what you're describing. The ORDER BY TIMESTAMP DESC will always make the most recent value for "knife01" show up.

5

thx Brandon, to get the last value failed on stream processing on kafka after reboot the server ist works, and with the FIRST value on y-axis i get the actually value :slightly_smiling_face:

The next problem is with the x-axis which i get. The name of the sensors have no static position on the x-axis. in this Video you can see that. In the middle of the video you can see the sensor names and bars are change the position. I would like static postion on x-axis and variable y-axis

Any idea?

By the way, can i change each bar chart color? or change the color of one bar is over of a specific value?

6

Try this expression and see if it produces the correct results you're looking for:

filters
| essql 
  query="SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE
FROM \"runtime_kibana_stream\"
WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03'
GROUP BY TIMESTAMP, SENSOR
ORDER BY TIMESTAMP DESC"
| ply by=SENSOR fn={math "first(VALUE)" | as "VALUE"}
| pointseries x=SENSOR y=VALUE
| plot
| render

What the ply function is doing here is it's grouping your datatable by SENSOR and applying the fn subexpression to each group. Here the fn is using TinyMath to grab the first value in the VALUE column, which grabs the newest row since your datasource is sorted in descending order by TIMESTAMP.

It should update with date as new documents come in.

Side note: I don't think the GROUP BY TIMESTAMP, SENSOR is necessary in your ESSQL query unless you expect to have multiple documents for the same exact timestamp per sensor.

7

Hi Catherine_Liu, i tried your expression, but the position on the x.axis continues change.

in this screenshot you can see my streamingdata to ES:

image
image.png2408×1688 448 KB

my expression filter are:

image
image.png837×609 33.1 KB

I need the expression GROUP BY TIMESTAMP, SENSOR without this expression i get this:

image
image.png960×597 46.9 KB

8

To maintain the same order of values in the x-axis, try adding a sort function in between ply and pointseries to sort the SENSOR field in asc order.

filters
| essql 
  query="SELECT TIMESTAMP, SENSOR, LAST(VALUE) VALUE
FROM \"runtime_kibana_stream\"
WHERE SENSOR='knife01' OR SENSOR='knife02' OR SENSOR='knife03'
GROUP BY TIMESTAMP, SENSOR
ORDER BY TIMESTAMP DESC"
| ply by=SENSOR fn={math "first(VALUE)" | as "VALUE"}
| sort by=SENSOR
| pointseries x=SENSOR y=VALUE
| plot
| render
9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.