(Note: Asking under SQL even though the use-case is in Kibana Canvas, as the question pertains to ESSQL specifically.)
I'm working on a Canvas setup and have the following query:
SELECT kibana.alert.rule.name AS rule_name, kibana.alert.reason AS alert_reason FROM \".alerts-observability.metrics.alerts-*\" WHERE event.action = 'active' AND kibana.alert.workflow_status = 'open' AND kibana.alert.rule.name LIKE 'Remote_Office_%_Critical' AND \"@timestamp\" > NOW() - INTERVAL 60 MINUTE GROUP BY kibana.alert.rule.name, kibana.alert.reason
alert_reason always follows a similar pattern:
some text to the left for some text to the right.
I would like the query to be able to drop everything to the left up to and including the word
for and have the value of
alert_reason just be the text to the right of the word
While I know I can drop text to the left via the RIGHT function, I can't figure out how to get the
count value dynamically drop everything to the left. (Note: The
count needs to be handled dynamically as the text before and after
for can be of inconsistent length).
Looking online some other SQL implementations support things like
POSITION, but it doesn't seem like ESSQL supports anything like this. Would anyone have any ideas on how to accomplish this?
P.S. I know that runtime fields are support with ESSQL (Use runtime fields | Elasticsearch Guide [8.2] | Elastic), but they are unfortunately not supported in Canvas, which makes runtime fields not an option.