Kibana Canvas - SQL Innerjoin & Text Colour Formatting

Hi there,

I would like to seek your help with regard to kibana visualizations.


MicrosoftTeams-image

Above shows a sample list of data & screenshot given by the end user of its expected outcome for the data uploaded into elasticsearch. The results are not logged regularly (ie no expected interval) but user will just require a display of latest test result per app code.
The alphabets are "app code". If result for the test shows "fail", the app code will be displayed in red highlighted cell. If result shows "pass", the app code will be displayed in green highlighted cell.

I have tried replicating them in simple kibana table visualization with the help of colour coding at Index Patterns for "result" field, but the display isn't what the users desired.


Instead, my supervisor suggested to use kibana canvas. Upon doing some research, I have some unanswered queries that would like to seek your expert advises.

  1. My current SQL query result shows 1 pass 1 fail each from every app code. Is there a way to display just the latest result of each app code, regardless of pass/fail? My understanding of SQL is that it will be doable with innerjoin tables, but seems like kibana canvas does not have such function?

  2. As user requires colour coding of pass/fail, I cannot find a way to enter the colours according to the text shown. Only way is via "Display" > "Text settings", which changes the font colour of the entire table.

Appreciate your help much. Thank you! :slight_smile:

Hey @noralilingyan!

I don't think a formatted table is going to be the best way for you to achieve what you're going for. Tables inside of Kibana don't have the formatting capabilities you're looking for.

I think your best shot with Canvas will be to create individual visualizations for each app code and compose them together into what looks like a single table.
Here's a repo full of canvas examples that I think can help you get to something that would work for you: https://github.com/alexfrancoeur/kibana_canvas_examples

If you do want to keep it all within a single table, I think you'll need to look into Vega to have the formatting control that you're looking for. Here's our vega docs and some table-based vega visualization examples.