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

Elastic Stack Kibana
1

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

2

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).

1 Like
3

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

4

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 :sweat_smile:

5

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

image
image1096×1526 87.1 KB

image
image1106×1832 269 KB

6

I will check this out! Interesting!

7

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 :slight_smile:

8

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.
image

9

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.
image

10

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