# Calculating statistics using sub-aggregate filter **URL:** \ **Category:** Kibana\ **Created:** [May 24, 2017, 8:25am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917 "2017-05-24T08:25:45Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Ruthu\_Ramya](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@Ruthu\_Ramya](https://discuss.elastic.co/u/Ruthu_Ramya)\ **Post date:** [May 24, 2017, 8:25am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/1 "2017-05-24T08:25:46Z")
Hi, From the log below, I need to calculate the 'Status' of flights per 'terminal'. > Jan 30,14:49:42.080 INFO Terminal1: FlightA is ONTIME > Jan 30,14:49:43.080 INFO Terminal1: FlightB is ONTIME > Jan 30,14:49:45.080 INFO Terminal1: FlightC is ONTIME > Jan 30,14:49:48.080 INFO Terminal1: FlightD is DELAYED > Jan 30,14:49:52.080 INFO Terminal2: FlightE is DELAYED > Jan 30,14:49:55.080 INFO Terminal2: FlightF is DELAYED > Jan 30,14:49:56.080 INFO Terminal2: FlightG is ONTIME > Jan 30,14:49:59.080 INFO Terminal2: FlightH is CANCELLED Expected result: > ``` > Terminal | ONTIME | DELAYED | CANCELLED | > Terminal1 | 3 | 1 | 0 | > Terminal2 | 1 | 2 | 1 | > > ``` Data parsed into ES: ![](https://us1.discourse-cdn.com/elastic/original/3X/6/1/61a8186551cb3d4a9fc1941667f1b66145e88148.png) Filter used: > ``` > filter { > grok { > add_tag => ["valid"] > match => { "message" => "%{MONTH:[@metadata][month]} %{MONTHDAY:[@metadata][day]},%{TIME:[@metadata][time]}%{SPACE}%{LOGLEVEL:log_level}%{SPACE}%{GREEDYDATA:log_message}" } > add_field => {"log_timestamp" => "%{[@metadata][month]} %{[@metadata][day]},%{[@metadata][time]}"} > } > grok { > match => { "log_message" => ["%{WORD:AirTerminal}: %{WORD:Flight} is %{WORD:Status}"] } > add_tag => ["valid"] > } > > ``` > > } I am not sure how I can get such statistics. I do not know if using aggregate filter is appropriate & also how to use the aggregate filter here in this case. Any help please.... Thank you. Regards, Ruthu ---
**Author:** ![Nico-DF](https://avatars.discourse-cdn.com/v4/letter/n/ed8c4c/32.png) [@Nico-DF](https://discuss.elastic.co/u/Nico-DF)\ **Post date:** [May 24, 2017, 8:36am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/2 "2017-05-24T08:36:30Z")
Which use do you have for your expected result? Depending on it, this aggregation might not be usefull (for instance, if you want to represent it on kibana, a pie chart with split and count might do the trick). I have some idea to achieve your result (I think) but if you explain which use you will have for this, you'll receive the more appropriate response. (from me or someone else) ---
**Author:** ![Ruthu\_Ramya](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@Ruthu\_Ramya](https://discuss.elastic.co/u/Ruthu_Ramya)\ **Post date:** [May 24, 2017, 8:44am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/3 "2017-05-24T08:44:08Z")
@Nico-DF, I want to represent the above shown tabular sample(Expected result) in the metrics table of Kibana. If I can get it somehow in Kibana, that should help me. PS: I tried to write a formula in timelion of Kibana to try and get the count, but I was not successful with the syntax. Regards, Ruthu ---
**Author:** ![Nico-DF](https://avatars.discourse-cdn.com/v4/letter/n/ed8c4c/32.png) [@Nico-DF](https://discuss.elastic.co/u/Nico-DF)\ **Post date:** [May 24, 2017, 8:52am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/4 "2017-05-24T08:52:10Z")
If it's for a data table, Indeed, you can't (at least if you can, I don't know how) do it. From there, I can propose you 2 solutions: - If your kibana version supports it and if you know some JS/HTML (or at least, willing to learn it a bit), there's this plugin that's great for these kind of things (Already used it for calculating elapsed times, counts, averages, etc): [https://github.com/PhaedrusTheGreek/transform\_vis](https://github.com/PhaedrusTheGreek/transform_vis) - Or you do it indeed in Logstash. I don't think aggregate is the right thing to do as you want to update it for some long time period (probably always up-to-date, at least, monthly). For this, I recommend you to use the doc\_id option in elasticsearch output. It allows you to update a document according to its id. You can find some documentation in this website ([https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-document\_id](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html#plugins-outputs-elasticsearch-document_id)) and then you'll have to write some script to update the document accordingly. ---
**Author:** ![Ruthu\_Ramya](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@Ruthu\_Ramya](https://discuss.elastic.co/u/Ruthu_Ramya)\ **Post date:** [May 25, 2017, 4:37am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/5 "2017-05-25T04:37:05Z")
@Nico-DF, I will try the 1st of your proposed solutions and see how it works out for me. Thanks. Regards, Ruthu ---
**Author:** ![Ruthu\_Ramya](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@Ruthu\_Ramya](https://discuss.elastic.co/u/Ruthu_Ramya)\ **Post date:** [June 2, 2017, 10:21am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/6 "2017-06-02T10:21:06Z")
@Nico-DF, Your proposed solution was useful. I found out other way to achieve the statistics in kibana and thought of sharing the same here. I have upgraded to v5.4.0 and as you see below, it has a new aggregation "Max Bucket". Once it is selected, it gives an option to select a sub-aggregation. So, in this case, I take the max of Status:ONTIME and display it per AirTerminal. Here AirTerminal forms the buckets ![](https://us1.discourse-cdn.com/elastic/original/3X/4/6/469098d1f198319576803525903ab3a0de410e2c.png) Regards, Ruthu ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 30, 2017, 10:21am UTC](https://discuss.elastic.co/t/calculating-statistics-using-sub-aggregate-filter/86917/7 "2017-06-30T10:21:09Z")
Closed on June 30, 2017, 10:21am UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.