Query to calculate login success and failure rates

[Venkatesh_Sridharan]

Hello All,
I have a list of login events on the index which has the following fields.

ip_address: <text>
login_success: <bool>

Now I want to query the logs and get a count of number of success and failed logins from each IP address. Example output should look like the following

ip_address: 1.2.3.4
login_count: 45
failed_login: 50

ip_address: 2.5.6.8
login_count: 10
failed_login: 20

As you can see, there are totally 95 events from the IP address, 1.2.3.4 of which, 45 are success and 50 are failures. I can do a simple term aggregation to get the number 95 but I am not able to aggregate on success and failure separately. is there a way to achieve that in ES?

Let me know if I am not clear in my question. Thanks!

[mayya (Mayya Sharipova)]

You can achieve this by nesting filters aggregation inside terms aggregation on ip_address field. You query can look like something like this:

GET my_index/_search?size=0
{
  "aggs": {
    "ip_logs1": {
      "terms": {
        "field": "ip_address"
      },
      "aggs": {
        "ip_logs2": {
          "filters": {
            "filters": {
              "success": { "match": { "login_success": "true"}},
              "failure": {"match": { "login_success": "false"}}
            }
          }
        }
      }
    }
  }
}