Unique count matching specific criteria

Elastic Stack Kibana
1

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
2

You need to store each jobid as a nested document.
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.

1 Like
3

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"
      }
    }
  }
}
1 Like
4

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?

5

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

6

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