SQL (Kibana Canvas) - How to drop all text to the left of a string pattern

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.

Hi Ben

I think the function you're looking for is LOCATE. Does that work for you?

Cheers,
Lukas

Thanks @Lukas_Wegmann LOCATE did the trick.