Hi All,
(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
The value 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 for.
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 CHARINDEX or 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.