Unique count matching specific criteria

(Mike Miller) #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

(Rahul Desai) #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.

(Rahul Desai) #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"
}
}
}
}

(Mike Miller) #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?

(Rahul Desai) #5

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

(system) #6

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