# Calculate time delta between log entries with matching ID and matching Command **URL:** \ **Category:** Kibana\ **Created:** [November 28, 2017, 4:17pm UTC](https://discuss.elastic.co/t/calculate-time-delta-between-log-entries-with-matching-id-and-matching-command/109435 "2017-11-28T16:17:18Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Toon\_Van\_Eyck](https://avatars.discourse-cdn.com/v4/letter/t/34f0e0/32.png) [@Toon\_Van\_Eyck](https://discuss.elastic.co/u/Toon_Van_Eyck)\ **Post date:** [November 28, 2017, 4:17pm UTC](https://discuss.elastic.co/t/calculate-time-delta-between-log-entries-with-matching-id-and-matching-command/109435/1 "2017-11-28T16:17:18Z")
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 ---
**Author:** ![Joe\_Fleming](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/joe_fleming/32/3561_2.png) [@Joe\_Fleming](https://discuss.elastic.co/u/Joe_Fleming)\ **Post date:** [November 28, 2017, 7:23pm UTC](https://discuss.elastic.co/t/calculate-time-delta-between-log-entries-with-matching-id-and-matching-command/109435/2 "2017-11-28T19:23:58Z")
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](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html) 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. ---
**Author:** ![stevedearl](https://avatars.discourse-cdn.com/v4/letter/s/48db29/32.png) [@stevedearl](https://discuss.elastic.co/u/stevedearl)\ **Post date:** [November 30, 2017, 3:01pm UTC](https://discuss.elastic.co/t/calculate-time-delta-between-log-entries-with-matching-id-and-matching-command/109435/3 "2017-11-30T15:01:11Z")
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 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 28, 2017, 3:05pm UTC](https://discuss.elastic.co/t/calculate-time-delta-between-log-entries-with-matching-id-and-matching-command/109435/4 "2017-12-28T15:05:49Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.