Running an ESQL query periodally and output the result into an index

[lizozom (Liza Katz)]

I have built an ESQL query that calculates today's inventory of products.
I want to run it daily and output the result into a different index.

1. I thought of using Transforms, could I use an ESQL query there?
2. If not, I thought of using Watcher or TaskManager, will any of these support ESQL?
3. If not, is there another way that I'm missing to achieve this?

Thank you

[xeraa]

Something like ES|QL : Summary indexing command needed · Issue #101176 · elastic/elasticsearch · GitHub, right?

We want to have that but the details will be discussed after the GA release of ES|QL, which is the current priority (and shouldn't be that far off).

[lizozom (Liza Katz)]

@xeraa

We want to have that but the details will be discussed after the GA release of ES|QL, which is the current priority (and shouldn't be that far off).

Absolutely!

I see the issue suggests this as an ESQL command but for example ESQL + Watcher + a re-index action would achieve a similar result.

But I'm sure that there are multiple ways to do it.

Looking forward to see where it goes!

I'd ask you to keep flexibility in mind when you build this feature

[xeraa]

If you are desperate then maybe, but I'd consider all these hacks with Watcher pretty much a dead end that should be rewritten another way sooner than later

[Sunile_Manjee]

Full transparency I haven't tested this. Just curious. Have you tried to create a rule using your ESQL along with triggering a index event? Within the index event, use context_hits

[lizozom (Liza Katz)]

@Sunile_Manjee

Full transparency I haven't tested this. Just curious. Have you tried to create a rule using your ESQL along with triggering a index event? Within the index event, use context_hits

I will check this out! Interesting!

[lizozom (Liza Katz)]

xeraa
I actually ended up doing something a bit different

1. I created a regular Transform that calculates today's inventory
2. I ran a simple Watcher that copies those entries to a new Data stream. See this Q\A for reference. It adds the inventory date as an extra field to each document, hence creating an inventory history index.
3. I then used ES|QL to query the inventory index.

So at this point, supporting ESQL to create that inventory data stream would be a cool addition, but no desperate measures needed to be taken!

Thanks

[lizozom (Liza Katz)]

Sunile_Manjee
I'm trying this out and the main thing that prevents me from achieving what I want, is that the document indexed in the action needs to be a new document it seems. But I would like to index the same documents the ESQL query returned.

[Sunile_Manjee]

@Sunile_Manjee

context_hits

Isn't that what would reside within context_hits ? The ESQL query result. Again I haven't tried this but from my initial analysis, seems interesting.