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:
{
"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.
{
"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
"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!