Canvas : is it possible to send varaibles to esql, edocs query argument or is it a limitation in canvas

Will canvas support to pass the dropdown control values as variables to esql, esdocs query

| esdocs index="appscan*" fields="url_type_uniqref.keyword, fversion.keyword" query="version :$var"

If you just need a single value, you can do this via filters. Basically, add a dropdown filter, bind it to the version field, and then on the sql element use filters | esdocs .... The field value will be applied as a filter along with anything else you've added to esdocs.

You'll likely want to query for the values in the dropdown too, and SQL is likely the easiest way to do that. Something like SELECT DISTINCT(version.keyword) AS version FROM "appscan*", then set the valueColumn to version and the filterColumn to fversion.keyword. It will show the distinct values of that field in the dropdown, and selecting a value there will apply the version filter to the field.

Thanks Fleming ,
Already I hade a sql way working in the same as you said .. but I need 2 version filters( dropdown) and needs to be passed to the sql .. Will can this be achieved?

Hey Joe,
I think DISTINCT is not yet supported in elasticsearch sql querries.
And I have a question if you please : I want to display a metric along with a dropdown filter : like for example to show the costs ... if it's on "any", I get a value. And if it's on an other field, I am getting 0.
Thanks in advance

I suspect your question is related to your other question at Canvas : Need to parse the values of 2 filters to the essql.

In short, there's not currently a way to apply filters as an OR argument in a query. What this means is that if you create two different filters and use them in the expression, they will be used as an AND condition. So this means something like filters | essql ...isn't an option currently.

The alternative method would be to somehow use the filter values in the sql query itself, something like essql {string "SELECT ... WHERE " {filters | to string}} .... The idea is that you could capture the filter values and just use them in the query string. This is also a non-option as there's no way to map a filter's value into a string value, so it can't be substituted into the query string.

This is all a pretty round-about way to tell you you can't do what you're trying to do in Canvas, at least not today. I'll admit that this does seem like a perfectly natural request though, and I'd recommend opening an issue about it on Kibana's issue tracker. Supporting OR in filters would enabled what you want, without having to modify the query directly, and that seems like a feature we should add, and soon.

Thanks Joe_Fleming

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