Can a Kibana Canvas Markdown Element Display a Total of a Column?

[devbot]

I have a Markdown Element on a Kibana canvas with the Expression Syntax:

    filters group="default"
    | essql 
      query="SELECT host.hostname, (last(system.memory.actual.used.bytes, \"@timestamp\")/1073741824 as memoryused, last(system.memory.total, \"@timestamp\")/1073741824 as memory from \"metricbeat*\" where system.memory.actual.used.bytes is not null group by host.hostname"
    | head 1
    | mapColumn "memoryused" expression={getCell "memoryused" | math expression="value" | formatNumber "0,0"}
    | mapColumn "memory" expression={getCell "memory" | math expression="value" | formatNumber "0,0"}
    | markdown "
    {{#each rows}}
     **{{memoryused}}** of **{{memory}}** GB
    {{/each}}" 
      font={font family="Roboto, -apple-system, BlinkMacSystemFont, 'Segoe UI', 'Helvetica Neue', Arial, sans-serif, 'Apple Color Emoji', 'Segoe UI Emoji', 'Segoe UI Symbol'" size=16 align="center" color="#000000" weight="normal" underline=false italic=false}
    | render containerStyle={containerStyle}

The essql query used here returns:

host.hostname | memoryused | memory

ip1 | 2 | 10

ip2 | 10 | 100

ip3 | 20 | 100

The markdown element as it is currently will display whatever the first row of the result is, so usually:

"2 of 10 GB"

What can I change in this markdown element about the expression syntax, or query, to get the total of these 2 columns and have the end result display:

"32 of 210 GB"

[afecteau (Andre Fecteau)]

Definitely interesting in finding out how to do this too! On top of that, would like to know how to take say your 32 GB of 210GB used, and divide them to get a % of memory used.

[corey.robertson]

You can use staticColumn to add a new column to hold the sum values and then access that in your markdown.

Something like

essql "...."
| staticColumn name="totalUsed" value={math "sum(memoryused)"}
| markdown content="{{rows.[0].totalUsed}}"

[devbot]

This worked. Exactly what I needed, thank you so much!

I also found that I needed to remove "| head 1 " from the expression syntax for this as well.