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"

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