How to create sub-count column by term in Kibana datatable

Question

I am try to customize the data table from the data in Elasticsearch.

Suppose I got a field "Department" which can be "Dept A" or "Dept B" or "Dept C" etc... But I can only show the total count of all the records instead of getting sub-total value by using the department field.

Refer to the following table:

Only the column "Total" is correct. My task is to achieve the figure under "Dept A" and "Other Dept".

Is there any filter which can apply on the Metric? Or any other ways to do it?

Please tell if you need further information.

Update -----------------------------------------------

After searching, I find a workaround to do it:

First create two scripted fields in Kibana like the following:

Scripted field name: sf_dept_A

Lang: painless

Script:

if (doc["department"].value.equals("Dept A"))
    return 1;
else
    return 0;

Scripted field name: sf_other_dept

Lang: painless

Script:

if (doc["department"].value.equals("Dept A") == false)
    return 1;
else
    return 0;

After create the above two scripted fields, go to create a datatable, just add mertics of the sum of scripted fields,

Add metrics

• Aggregation: Sum

• Field: sf_dept_A

• Custom Label: Dept A

Add metrics

• Aggregation: Sum
• Field: sf_dept_A
• Custom Label: Dept A

Add metrics

• Aggregation: Count
• Custom Label: Total

In this way, the count of different departments can be separated by columns. But this should require much more resources and I have to create many fields if I have many departments.

Answer 1

In recent versions of Kibana, you can use the "JSON Input" to do this on-the-fly, in a given Visualization, without needing to scripted fields:

• In your Visualization, create a "Sum" metric (on any field, I think - the field actually gets ignored in the end...?)
• Under that Sum Metric, click the "Advanced" link, revealing the "JSON Input" textbox
• In that textbox, type something like: { "script": { "inline": "doc['SomeField'].value == 'SomeValue' ? 1 : 0", "lang": "painless" } }

And there you have it! You now have a conditional count metric. The default label will be "Sum of (whatever field you selected)", so you'll want to set a more accurate custom label on the metric, like "Count (SomeValue)".

This will still be slow/expensive to run, but as I understand it there are a couple of advantage over the solution you've appended in your question:

1. You don't need to create all those scripted fields in advance, you can define the scripts for the exact purpose you need, when you need
2. Creating scripted fields carries query-execution-time overhead on many (all?) queries, whereas this approach has the overhead only when you'e running this specific visualization

Answer 2

Under your Split Rows you could simply apply filters.

Let's say if you're having a field called Department within every single record, you may go ahead and have two filters as:

Department:"Dept A"

Department:"Dept B"

It could look something like this in Kibana.

OR

You could use Terms aggregation in order to differentiate the results according to the respective values of the field. Which should ideally show the count for each department. But then I don't think you could show the count, department wise separated by columns though as you've shown above in the screenshot.

Whereas you can see the count for each department along with the timestamp you've selected, which should look something like this:

Timestamp     Departments         Count
01/01/17        Depa A              1
01/01/17        Depa B              2 
02/01/17        Depa C              1
02/01/17        Depa B              6 

Hope this helps!