# Today Avg value vs Moving AVG (14 days) value to trigger an alert based on DSL query

**URL:** <https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527>\
**Category:** Kibana\
**Tags:** elastic-stack-alerting\
**Created:** [March 25, 2023, 8:16pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527 "2023-03-25T20:16:15Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![cpu](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@cpu](https://discuss.elastic.co/u/cpu)\
**Post date:** [March 25, 2023, 8:16pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/1 "2023-03-25T20:16:15Z")

</div>

Hi All,  
I'm trying to setup an alert (in Stack Management --\> Rules and Connectors -- Rules) to track when the "today" AVG value on a field is lower than the moving AVG (14 days) value for the same field.  
This check should be performed daily (1 time).  
I created an "elasticsearch query" rule with this DSL query:

```auto
{
    "size": 0,
    "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "record_created_time": {
              "gte": "now-14d"
            }
          }
        },
        {
          "term": {
            "system_virtual_name.keyword": "My set of data"
          }
        },
        {
          "exists": {
            "field": "score"
          }
        }
      ]
    }
  },
    "aggs": {
        "dates": {
            "date_histogram": {
                "field": "record_created_time",
                "calendar_interval": "day"
            },
            "aggs": {
                "the_avg": {
                    "avg": {
                        "field": "score"
                    }
                },
                "the_movavg": {
                    "moving_fn": {
                        "buckets_path": "the_avg",
                        "window": 14,
                        "script": "MovingFunctions.unweightedAvg(values)"
                    }
                },
                "final_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "TheAvg": "the_avg",
                            "TheMovAvg": "the_movavg"

                        },
                        "script": "params.TheAvg < (params.TheMovAvg == null ? 0 : params.TheMovAvg)"
                    }
                }
            }
        }
    }
}

```

The query return all the days (in the 14 days time window) when the AVG is lower than the moving AVG.

```auto
{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 3572,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "dates": {
      "buckets": [
        {
          "key_as_string": "2023-03-15T00:00:00.000Z",
          "key": 1678838400000,
          "doc_count": 249,
          "the_avg": {
            "value": 3.68
          },
          "the_movavg": {
            "value": 4.413194444444445
          }
        },
        {
          "key_as_string": "2023-03-17T00:00:00.000Z",
          "key": 1679011200000,
          "doc_count": 372,
          "the_avg": {
            "value": 3.6470588235294117
          },
          "the_movavg": {
            "value": 4.169700670498084
          }
        },
        {
          "key_as_string": "2023-03-21T00:00:00.000Z",
          "key": 1679356800000,
          "doc_count": 354,
          "the_avg": {
            "value": 3.8214285714285716
          },
          "the_movavg": {
            "value": 4.367916553246168
          }
        },
        {
          "key_as_string": "2023-03-22T00:00:00.000Z",
          "key": 1679443200000,
          "doc_count": 300,
          "the_avg": {
            "value": 3.740740740740741
          },
          "the_movavg": {
            "value": 4.299605555518968
          }
        }
      ]
    }
  }
}

```

How I can compare only the today AVG with the moving AVG in order to trigger the alert?

I tried to set

```auto
"filter": [
        {
          "range": {
            "record_created_time": {
              "gte": "now-1d"
            }
          }
        }

```

but the moving AVG seems not correctly calculated.

Is the above query the correct approach to get the goal?  
Should I need to change for another type of alert?

**Elasticsearch v8.4.3.**

Thank you so much for any help / suggestion.

Best!

---

<div class="post-metadata">

**Author:** ![iuliia.guskova](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iuliia.guskova/32/117689_2.png) [@iuliia.guskova](https://discuss.elastic.co/u/iuliia.guskova)\
**Post date:** [March 27, 2023, 1:29pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/2 "2023-03-27T13:29:56Z")

</div>

Hi, @cpu !  
For helping you best way possible I want to understand better what you are trying to achieve? As I understand moving average is a set of values. With which value you want to compare "today" AVG?

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [March 27, 2023, 5:17pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/3 "2023-03-27T17:17:21Z")

</div>

First of all, Kibana Rules does not yet fully support Elasticsearch aggregations (follow the GitHub issue here: [Elasticsearch Query Stack Alert Aggregation Support · Issue #95161 · elastic/kibana · GitHub](https://github.com/elastic/kibana/issues/95161)).

In the meantime, you could use [Watcher](https://www.elastic.co/guide/en/kibana/current/watcher-ui.html).

And, as Julia hints, there may be other approaches that may be even more apropos. Have you considered anomaly detection in ML, for example?

---

<div class="post-metadata">

**Author:** ![cpu](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@cpu](https://discuss.elastic.co/u/cpu)\
**Post date:** [March 27, 2023, 5:40pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/4 "2023-03-27T17:40:12Z")

</div>

Thanks a lot @iuliia.guskova and @richcollier for your replies!

@iuliia.guskova  
My goal is to compare the "today" avg of the field "score" with the moving avg (window: 14 days) of field "score" (same field of today avg).  
The goal is to trigger an alert if the today avg for the field "score" in lower then its moving average calculated on a time window of 14 previous days.

@richcollier  
Thanks for sharing the link for the issue you mentioned.  
During my weekend studies I considered anomaly detection in ML: my initial idea was to use custom detector and the low\_mean function...  
But my expertise isn't enough in this field...  
Any suggestion / help is very welcome...  
Thanks in advance!

---

<div class="post-metadata">

**Author:** ![iuliia.guskova](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iuliia.guskova/32/117689_2.png) [@iuliia.guskova](https://discuss.elastic.co/u/iuliia.guskova)\
**Post date:** [March 27, 2023, 7:13pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/5 "2023-03-27T19:13:05Z")

</div>

Thanks for answer me, @cpu!

Correct me if I'm wrong, you want to compare the "today" avg (I assume a value at the end of the day) with an average for 14 previous days and throw an alert when ?

I just did not get why do you need concept of `moving average` here. You can compare only 2 values and throw if one is less than another. But moving average as I understand it, it's a set of average values with a defined window. So you can't compare set with just one number. Or maybe I misunderstood you?

---

<div class="post-metadata">

**Author:** ![cpu](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@cpu](https://discuss.elastic.co/u/cpu)\
**Post date:** [March 27, 2023, 8:41pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/6 "2023-03-27T20:41:26Z")

</div>

Thanks @iuliia.guskova for your reply!

Very good point....  
I was thinking moving average was the most appropriate function for this comparison.

So your suggestion is to compare these two values:

- the average of field "score" in today documents bucket
- the average of of field "score" in the bucket with documents of last 14 days

If I understood correctly, how could I modify my query in order to get this?

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![richcollier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richcollier/32/115035_2.png) [@richcollier](https://discuss.elastic.co/u/richcollier)\
**Post date:** [March 27, 2023, 10:10pm UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/7 "2023-03-27T22:10:29Z")

</div>

Anomaly detection is super easy to use. Just create a single metric job and choose `low_mean` on the field `score`. Have it look back in time over your historical data (hopefully you have several days if not weeks of historical data) and voila!

If you want to learn more about Elastic ML - download my free e-book:  
[https://events.elastic.co/machinelearningwithelastic](https://events.elastic.co/machinelearningwithelastic)

---

<div class="post-metadata">

**Author:** ![iuliia.guskova](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iuliia.guskova/32/117689_2.png) [@iuliia.guskova](https://discuss.elastic.co/u/iuliia.guskova)\
**Post date:** [March 29, 2023, 8:31am UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/8 "2023-03-29T08:31:29Z")

</div>

Hi, again @cpu!

I've talked around. It seems you cannot accomplish what you need using ES query rule.  
But probably it possible with Anomaly detection rule if @richcollier told so.

I am not familiar with Anomaly detection rule. But you can use the book above.  
I've used chat GPT-4 to get instructions how to create one for your case and it looks helpful. So you can give a try as well.

---

<div class="post-metadata">

**Author:** ![cpu](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@cpu](https://discuss.elastic.co/u/cpu)\
**Post date:** [April 1, 2023, 9:35am UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/9 "2023-04-01T09:35:13Z")

</div>

Hi @iuliia.guskova,  
thanks for your verifications.  
I didn't thought to ask Chet GPT-4 for getting help on this... I should have a try.  
In the meanwhile I've setup a ML anomaly detection as @richcollier suggested and I'm trying it.  
Thanks for all the help!  
Really appreciated!

---

<div class="post-metadata">

**Author:** ![cpu](https://avatars.discourse-cdn.com/v4/letter/c/919ad9/32.png) [@cpu](https://discuss.elastic.co/u/cpu)\
**Post date:** [April 1, 2023, 9:38am UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/10 "2023-04-01T09:38:23Z")

</div>

Hi @richcollier  
thanks for your reply and info.  
I've setup a ML anomaly detection job as you suggested me and it was very easy.  
I've more or less 2 years of data and now I'm testing it.  
Meanwhile I've downloaded your ebook: awesome! Thanks! I've to study ES... that's my first gaol I want to achieve.  
Thanks for all the help!  
Really appreciated!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 29, 2023, 9:38am UTC](https://discuss.elastic.co/t/today-avg-value-vs-moving-avg-14-days-value-to-trigger-an-alert-based-on-dsl-query/328527/11 "2023-04-29T09:38:48Z")

</div>

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