# Query to calculate login success and failure rates

**URL:** <https://discuss.elastic.co/t/query-to-calculate-login-success-and-failure-rates/270787>\
**Category:** Elasticsearch\
**Created:** [April 21, 2021, 5:38am UTC](https://discuss.elastic.co/t/query-to-calculate-login-success-and-failure-rates/270787 "2021-04-21T05:38:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Venkatesh\_Sridharan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatesh_sridharan/32/87469_2.png) [@Venkatesh\_Sridharan](https://discuss.elastic.co/u/Venkatesh_Sridharan)\
**Post date:** [April 21, 2021, 5:38am UTC](https://discuss.elastic.co/t/query-to-calculate-login-success-and-failure-rates/270787/1 "2021-04-21T05:38:03Z")

</div>

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

```auto
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

```auto
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!

---

<div class="post-metadata">

**Author:** ![mayya](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mayya/32/83147_2.png) [@mayya](https://discuss.elastic.co/u/mayya)\
**Post date:** [April 26, 2021, 12:45pm UTC](https://discuss.elastic.co/t/query-to-calculate-login-success-and-failure-rates/270787/2 "2021-04-26T12:45:21Z")

</div>

You can achieve this by nesting [filters aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filters-aggregation.html) inside terms aggregation on `ip_address` field. You query can look like something like this:

```auto
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"}}
            }
          }
        }
      }
    }
  }
}

```

---

<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:** [May 24, 2021, 12:46pm UTC](https://discuss.elastic.co/t/query-to-calculate-login-success-and-failure-rates/270787/3 "2021-05-24T12:46:14Z")

</div>

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