elasticsearch - How to set up percolator to return when an aggregation value hits a certain threshold?

Question

Take the following aggregation query as an example:

{
  "query": {
    "match_all": {}
  },
  "aggs": {
    "groupBy": {
      "terms": {
        "field": "CustomerName"
      },
      "aggs": {
        "points_sum": {
          "stats": {
            "field": "TransactionAmount"
          }
        }
      }
    }
  },
  "size": 0
}

I am interested in knowing when any CustomerName has an average TransactionAmount (stats.avg) that is above some threshold for all of that customer's purchases, as soon as I index a document that would put my average above that threshold. It seems like percolator is designed for matching documents to rules, more or less, but I can't find any good examples of using percolator to match rules that are based on aggregation results.

Is this possible? Is percolator the best solution here? Is there another/better solution? Thanks in advance

Answer 1

You can use the Watcher commercial product for that and define the following watch:

PUT _watcher/watch/transaction_alert
{
  "trigger": {
    "schedule": {
      "interval": "1m"
    }
  },
  "input": {
    "search": {
      "request": {
        "indices": "transactions",
        "types": "transaction",
        "body": {
          "query": {
            "match_all": {}
          },
          "size": 0,
          "aggs": {
            "groupBy": {
              "terms": {
                "field": "CustomerName"
              },
              "aggs": {
                "points_sum": {
                  "stats": {
                    "field": "TransactionAmount"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  "condition": {
    "script": {
      "inline": "return ctx.payload.aggregations.groupBy.buckets.findAll{ cust -> cust.points_sum.avg >= 200}"
    }
  },
  "actions": {
    "send_email": { 
      "email": {
        "to": "<username>@<domainname>", 
        "subject": "Customer Notification - Transaction > 200",
        "body": "The attached customers have a transaction average above $200"
        "attachments" : {
           "data.yml" : {
              "data" : {
                 "format" : "yaml" 
              }
           }
        }
      }
    }
  }
}

UPDATE

To sum up:

• Watcher is a commercial product
• ElastAlert doesn't support it (yet) and requires some effort to make it work

There's another much simpler and cheaper way to achieve this using Logstash. Even though the elasticsearch input plugin doesn't support aggregations, it is possible to use the http_poller input plugin in order to send an aggregation query to Elasticsearch at regular intervals. Then using a filter you can check if the desired threshold is attained or not, and finally, alert someone by email if that's the case using the email output plugin.

The configuration basically goes like this (note that your above aggregation query needs to be URL-encoded and sent to ES using the source=... parameter). Also note that I've modified your query to sort the buckets according to points_sum.avg (desc)

input {
  http_poller {
    urls => {
      test1 => 'http://localhost:9200/your-index/_search?source=%7B%22query%22%3A%7B%22match_all%22%3A%7B%7D%7D%2C%22aggs%22%3A%7B%22groupBy%22%3A%7B%22terms%22%3A%7B%22field%22%3A%22CustomerName%22%2C%22order%22%3A%7B%22points_sum.avg%22%3A%22desc%22%7D%7D%2C%22aggs%22%3A%7B%22points_sum%22%3A%7B%22stats%22%3A%7B%22field%22%3A%22TransactionAmount%22%7D%7D%7D%7D%7D%2C%22size%22%3A0%7D'
   }
   # checking every 10 seconds
   interval => 10
   codec => "json"
  }
}
filter {
  split {
    field => "[aggregations][groupBy][buckets]" 
  }
}
output {
  if [aggregations][groupBy][buckets][points_sum][avg] > 200 {
    email {
      to => "<username>@<domainname>"
      subject => "Customer Notification - Transaction > 200",
      body => "The customer %{[aggregations][groupBy][buckets][key]} has a transaction average above $200"
    }
  }
}

Agreed, this is a very simplistic implementation, but it should be working and you can build upon it to make it smarter, with Logstash and your imagination the limit is the sky ;-)

UPDATE 2

Another node.js tool call elasticwatch could also be leveraged to do this.