Calculating statistics using sub-aggregate filter

[Ruthu_Ramya]

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:

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

[Nico-DF]

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)

[Ruthu_Ramya]

@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

[Nico-DF]

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

• 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) and then you'll have to write some script to update the document accordingly.

[Ruthu_Ramya]

@Nico-DF,
I will try the 1st of your proposed solutions and see how it works out for me. Thanks.

Regards,
Ruthu

[Ruthu_Ramya]

@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

Regards,
Ruthu