Calculate time delta between log entries with matching ID and matching Command

[Toon_Van_Eyck]

I have some logs in my elasticsearch database formated like this:

Timestamp                           |   ID  |  Command
------------------------------------+-------+---------------------------
November 24th 2017, 14:49:00.11614  |   0   |  CONNECTION_REQUEST
November 24th 2017, 14:49:00.13510  |   1   |  CONNECTION_REQUEST
November 24th 2017, 14:49:00.18714  |   0   |  CONNECTION_COMPLETE
November 24th 2017, 14:49:00.26010  |   1   |  CONNECTION_COMPLETE
November 24th 2017, 14:50:20.7850   |   2   |  CONNECTION_REQUEST
November 24th 2017, 14:50:20.8051   |   2   |  CONNECTION_REQUEST
November 24th 2017, 14:50:20.8450   |   2   |  CONNECTION_COMPLETE

There are connection_requests and connection_completed messages that both specify an ID. It can occur that A connection_request is retransmitted before the connection_complete happened. There are also logs in between with other commands but they are not concidered here.

What I want to calculate is the time between the first connection_request and the connection_complete for each ID

e.g.:

Time_0 = November 24th 2017, 14:49:00.18714 - November 24th 2017, 14:49:00.11614 = 7100ms
Time_1 = November 24th 2017, 14:49:00.26010 - November 24th 2017, 14:49:00.13510 = 12500ms
Time_2 = November 24th 2017, 14:50:20.8450  - November 24th 2017, 14:50:20.7850  = 600ms

I can make a bucket of the CONNECTION_COMPLETE logs but then how do I get the first occurrence of CONNECTION_REQUEST with the same ID before the timestamp of the CONNECTION_COMPLETE. I don't really know what aggregator(s) to use and how to make them interact with each other

[Joe_Fleming]

You're not going to be able to do this in Kibana's visualizations, since this isn't an aggregation operation. What you need is a way to query for the first command and then query again for the second command and do an operation on the values from both. I believe pipeline aggs will allow you to do what you want, but Kibana's core visualizations don't support them.

You could change your data to enable this, and then it becomes pretty easy, but you offload the calculation to the indexing process. Basically, you store both times in a single document, and you could even calculate the delta as you update the document with the CONNECTION_COMPLETE time. But the way you are indexing data right now, this isn't possible in Kibana, and it's tricky (or maybe impossible, I'm not 100% that pipelines can do this) to query this in Elasticsearch.

[stevedearl (Steve Earl)]

Take a look at the 'Elapsed' plugin for Logstash - we're using this to do exactly what you need. As long as you have a unique correlating ID between the start and end transactions you want to calculate the time between it will work.

Cheers,
Steve