# Computing session durations from timestamps scattered over two documents **URL:** \ **Category:** Kibana\ **Created:** [May 25, 2020, 10:14am UTC](https://discuss.elastic.co/t/computing-session-durations-from-timestamps-scattered-over-two-documents/234127 "2020-05-25T10:14:18Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![seanowl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanowl/32/69184_2.png) [@seanowl](https://discuss.elastic.co/u/seanowl)\ **Post date:** [May 25, 2020, 10:14am UTC](https://discuss.elastic.co/t/computing-session-durations-from-timestamps-scattered-over-two-documents/234127/1 "2020-05-25T10:14:18Z")
# Background Clients connect to my Websocket-Server. I have logs for both `join` & `disconnect` events in the following format (logs redacted): ```auto { "_index": "filebeat-7.7.0-2020.05.22-000001", "_type": "_doc", "_id": "5CAqS3IBb2WkqqHd1BPf", "_version": 1, "_score": null, "_source": { "@timestamp": "2020-05-25T09:29:48.389Z", "message_decoded": { "data": { "uuid": "b45739b9-8323-43e5-b8f1-50abb67962e9" }, "type": "join" }, "fields": { "@timestamp": [ "2020-05-25T09:29:48.389Z" ] }, } ``` The `disconnect`-log looks similar. Importantly, I have the `@timestamp` and `uuid` fields. # What I'm trying to do I would like to compute the average session duration for my clients. Filtering for any `uuid` yields two documents (`uuid` is session unique), the difference between the two `@timestamp`s is what I am trying to compute. I want to plot session durations in a line chart / vertical bar chart. I also want to compute the average session duration. # What I have come up with so far I started by bucketing `uuid`s using the `Terms`-aggregator. This creates buckets of size `2`. Next, I need to somehow reduce each bucket into an average session duration. However, I cannot seem to find a `metric` that does this in the Kibana interface. I have looked into the following options: 1. Use a `painless`-script to compute the date difference. However, I am unsure how to use a script to compute over an entire bucket. The examples I find compute run a script per document. I have seen that I can include scripts as custom `JSON` inside Kibana, but am still unsure as to what that script would look like. 2. Use [Bucket Script Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/search-aggregations-pipeline-bucket-script-aggregation.html#search-aggregations-pipeline-bucket-script-aggregation). However it says in the [documentation](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/search-aggregations-pipeline-bucket-script-aggregation.html#search-aggregations-pipeline-bucket-script-aggregation): > The specified metric must be numeric and the script must return a numeric value. # My question What is the easiest way to compute session durations, as outlined above? Could anybody point me in the right direction here? ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [May 26, 2020, 8:29pm UTC](https://discuss.elastic.co/t/computing-session-durations-from-timestamps-scattered-over-two-documents/234127/2 "2020-05-26T20:29:30Z")
Hi, have a look at [data transforms](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/transforms.html). Your direction towards using painless is right, with transform you can create sessions out of events and you use painless to extract. e.g. session duration. A similar example is discussed in this [webinar](https://www.elastic.co/webinars/introducing-data-frame-transforms-for-elastic-machine-learning). I think you should be able to take the example code provided and customize it to your needs. (The webinar might be a bit old, lots of new functionality has been added meanwhile, therefore take a look into the more recent docs, too) Hope that helps! ---
**Author:** ![seanowl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seanowl/32/69184_2.png) [@seanowl](https://discuss.elastic.co/u/seanowl)\ **Post date:** [May 27, 2020, 12:40pm UTC](https://discuss.elastic.co/t/computing-session-durations-from-timestamps-scattered-over-two-documents/234127/3 "2020-05-27T12:40:31Z")
Hey Hendrik, thanks a lot for your answer, you pointed in exactly the right direction! The webinar also proved to be very helpful. I ended up using a data transform (which I already love!) and some scripting to compute session durations. Now for some reason, the Kibana interface didn't let me specify the script from the "Advanced Pivot Editor" (I might have done something wrong), but using the "Dev tools", creating the continuous data transform worked flawlessly. For reference, I am pasting the API-call here. ```auto PUT _transform/sessions_continuous { "source": { "index": [ "filebeat-7.7.0-2020.05.22-000001" ] }, "pivot": { "group_by": { "message_decoded.data.uuid": { "terms": { "field": "message_decoded.data.uuid" } } }, "aggregations": { "@timestamp_max": { "max": { "field": "@timestamp" } }, "@timestamp_min": { "min": { "field": "@timestamp" } }, "duration": { "bucket_script": { "buckets_path": { "min": "@timestamp_min", "max": "@timestamp_max" }, "script": "(params.max - params.min)/1000" } } } }, "description": "Session durations", "dest": { "index": "continuous_sessions" }, "frequency": "10s", "sync": { "time": { "field": "@timestamp", "delay": "10s" } } } ``` Again, thanks a lot for your help! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 24, 2020, 12:40pm UTC](https://discuss.elastic.co/t/computing-session-durations-from-timestamps-scattered-over-two-documents/234127/4 "2020-06-24T12:40:33Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.