DataTable column to aggregate if field exists


First time posting here, I've been looking around but haven't found anything to point me towards the right direction to solve my issue.
I'm creating a Data Table with a Date Histogram as a bucket, and i need to display two columns.

One column is the Total amount of successful Jobs (this is a unique count of the field ['JOBID'] with ['Errorid'] being Null)
second column is the total amount of jobs with errors (this is a unique count of the filed ['JOBID'] with ['Errorid'] having a value other than null)

I've tried using a filter but it applys to the whole table so that doesn't work for me. i also tried using a split rows with two conditions exists:errorid and !(exists:errorid) but this doesn't display it in a seperate column, rather it splits the rows which is not what i want.

my end result should look something like this:

| DateTime | Successful jobs | Failed Jobs |
| Nov.22-2017 | 10 | 3 |
| Nov.21-2017 | 15 | 6 |

Any insight on how to tackle this would be highly appreciated.


i think you have two options ...
preferred if you have a lot of data and need speed: reindex your data:

  • add a new field called succesfull which has a value 1 id ErrorId is null, 0 otherwise
  • add a new field called failed which has value 1 if ErrorId is not null, 0 otherwise
    then you can simply sum on those fields

second way, should be ok if you don't have tons of data:

  • add a scripted field to your index, with painless script like
 if (doc['ErrorId'].value === 'null') return 1;
return 0;

and a similar one for checking if ErrorId is not null for counting errors

  • again, do a sum on this scripted fields

let me know if this solves your issue.

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