# Unique count matching specific criteria

**URL:** <https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327>\
**Category:** Kibana\
**Created:** [May 17, 2018, 12:16pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327 "2018-05-17T12:16:40Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mikeage](https://avatars.discourse-cdn.com/v4/letter/m/dc4da7/32.png) [@mikeage](https://discuss.elastic.co/u/mikeage)\
**Post date:** [May 17, 2018, 12:16pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/1 "2018-05-17T12:16:40Z")

</div>

Hi,

I have data consisting of the following three fields: jobId, subJobId, and outcome, where each jobId has several subJobs, each with one outcome.

I want to show a graph indicating how many jobs had at least one failure outcome, and how many had only successes.

If I do a split on outcome, I get the proper information for how many subJobs passed/failed. If I add a unique count on jobId, I get closer, but a job that had both a failure and success shows up twice. A job that only had failures or only successes shows up once.

Is there an easy way to do this in Kibana?

```
Sample Data
jobId=1, subJobId=a, outcome=pass
jobId=1, subJobId=b, outcome=pass
jobId=2, subJobId=c, outcome=pass
jobId=2, subJobId=d, outcome=fail
jobId=3, subJobId=e, outcome=fail
jobId=3, subJobId=f, outcome=fail

Desired graph: (doesn't matter exactly how, let's say a vertical bar chart with a split series. Whatever)
number of exclusively successes: 1
number of one or more failures: 2
```

---

<div class="post-metadata">

**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\
**Post date:** [May 17, 2018, 12:47pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/2 "2018-05-17T12:47:04Z")

</div>

You need to store each jobid as a nested document.  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html)

An example document will be

```
{
  "jobid" : 1,
  "subjobinfo" : [
  { "subjobid" : "a" , "outcome" : "pass"},
  { "subjobid" : "b" , "outcome" : "fail"}
  ]
}

```

If you store every record as an individual document then finding the number of one of more failures will be easy but finding exclusive successes might not be possible.

---

<div class="post-metadata">

**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\
**Post date:** [May 17, 2018, 12:50pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/3 "2018-05-17T12:50:59Z")

</div>

Here is the query for number of one or more failures:

```
{
  "query" : {
    "nested": {
      "path": "subjobinfo",
      "query": {
        "match": {
          "subjobinfo.outcome": "fail"
        }
      }
    }
  },
  "aggs": {
    "oneormorefailures": {
      "cardinality": {
        "field": "jobid"
      }
    }
  }
}

```

Here is the query for exclusive successes:

```
{
  
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "subjobinfo",
            "query": {
              "match": {
                "subjobinfo.outcome": "pass"
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "nested": {
                  "path": "subjobinfo",
                  "query": {
                    "match": {
                      "subjobinfo.outcome": "fail"
                    }
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "exclusivesuccesses": {
      "cardinality": {
        "field": "jobid"
      }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![mikeage](https://avatars.discourse-cdn.com/v4/letter/m/dc4da7/32.png) [@mikeage](https://discuss.elastic.co/u/mikeage)\
**Post date:** [May 18, 2018, 7:36am UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/4 "2018-05-18T07:36:21Z")

</div>

Thank you! I can't change the reporting format, but these queries look perfect!

If you don't mind, one more question. Can I put these explicit ES queries into a Kibana graph, or are they only suitable for running directly against ES? I.e., if I wanted to do a vertical bar chart, split on the X Axis as a date histogram and with two bars, one for each query, can that be done?

---

<div class="post-metadata">

**Author:** ![RahulD](https://avatars.discourse-cdn.com/v4/letter/r/da6949/32.png) [@RahulD](https://discuss.elastic.co/u/RahulD)\
**Post date:** [May 21, 2018, 2:43pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/5 "2018-05-21T14:43:15Z")

</div>

I am sure you can do it in Kibana using filters.

---

<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:** [June 18, 2018, 2:43pm UTC](https://discuss.elastic.co/t/unique-count-matching-specific-criteria/132327/6 "2018-06-18T14:43:19Z")

</div>

Closed on June 18, 2018, 2:43pm UTC

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