Can 2 queries be used in a Metric or Markdown element, with if logic in the display?

Elastic Stack Kibana
#1

I have a Metric element on Kibana Canvas with the following expression syntax:

    filters group="default"

    | essql

    query="SELECT COUNT(*) as count FROM \"index1*\" "

    | markdown "

    {{#each rows}}{{count}}{{/each}}

    "

    font={font size=36 align="center"}

    | render containerStyle={containerStyle}

Would it be possible to add a 2nd query and use if logic in the markdown syntax, for example use another query

"SELECT COUNT(*) as count FROM \"index2*\" "

and only display the markdown if count from index2 is greater than 0?

#2

Sure:

kibana 
| filters
| var_set name="index1" value={essql query="SELECT COUNT(*) as count FROM kibana_sample_data_logs" | getCell "count"}
| var_set name="index2" value={essql query="SELECT COUNT(*) as count FROM kibana_sample_data_ecommerce" | getCell "count"}
| if {var "index1" | lt {var "index2"}} then={markdown "Index 1 greater {{#each rows}}{{count}}{{/each}}" | render} else={var "index2" | metric label="Index 2 count" | render}
#3

When I try this in a metric element I get the error "cannot read property 'query' of null"

#4

Are you using Kibana 7.7 or higher? What exactly is your expression?

#5

7.4 currently. And I used that exact expression that you posted (except for my index).

#6

The var_set and var feature is not available until 7.7, so that's probably why you are seeing this error. You might be able to copy+paste the queries into multiple places, but it will cause multiple requests to get executed.

#7

By multiple places do you mean different elements? Are there examples anywhere of a metric or markdown element referencing 2 queries before 7.7?

#8

You can copy paste the SQL query into multiple places in the expression, instead of using variables. Or you can upgrade

#9

Sorry for being so dense, but what exactly would that look like if you were to do it with your example above and no variable usage? I'm really struggling with expression syntax.

#10 
kibana 
| filters
| essql query="SELECT COUNT(*) as count FROM kibana_sample_data_logs"
| getCell "count"
| if {context | lt {essql query="SELECT COUNT(*) as count FROM kibana_sample_data_ecommerce" | getCell "count"}} then={markdown "Index 1 greater {{#each rows}}{{count}}{{/each}}" | render} else={essql query="SELECT COUNT(*) as count FROM kibana_sample_data_ecommerce" | getCell "count" | metric label="Index 2 count" | render}
1 Like
#11

Thanks Wylie. I've gained a better understanding of expression syntax. When I try that query or others like it where I'm putting the getcell result of a query inside sub expression like the one for ltabove, I'm getting "Can not cast 'number' to any of 'filter'"

#12

I think you need to change lt {essql to lt {filters | essql to fix that erro

1 Like
#13

Worked like a charm. Thanks so much!!!