# How to execute dynamic queries with visualisations **URL:** \ **Category:** Kibana\ **Created:** [August 17, 2021, 8:10am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640 "2021-08-17T08:10:35Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![akashk](https://avatars.discourse-cdn.com/v4/letter/a/ad7895/32.png) [@akashk](https://discuss.elastic.co/u/akashk)\ **Post date:** [August 17, 2021, 8:10am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/1 "2021-08-17T08:10:35Z")
Hi, I am new to ELK and I am facing one issue in creating visualisations. So we have two different sources(log files) from where we are reading the logs and storing triggeredTime and pickedTime of application event in elasticsearch. Both triggeredTime and pickedTime doesn't belong to single document but both entry contains same eventId. For example: Document1 is like { eventId : EV123 status: TRIGGERED triggeredTime : 2021.08.11 12:01:02:0536 } Document2 is like { eventId : EV123 status: PICKED pickedTime : 2021.08.11 12:02:03:0456 } I have created data table visualisation like below, eventId | status | triggeredTime | pickedTime Now, I want one more column in data table which will be inQueueTime which will give me the time of how long that particular event has been there in pending queue. InQueueTime = pikedTime - triggeredTime How to create this custom column in data table which will give me inQueueTime of event on the fly ? OR Is there any other visualisation which I can use to calculate inQueueTime and create a dashboard out of it. ---
**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\ **Post date:** [August 19, 2021, 7:20am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/2 "2021-08-19T07:20:02Z")
Hey! If I understand correctly, you have created one index pattern which has documents with different fields. Correct? Is it a time-based index pattern? Moreover, can you also share your datatable configuration and your kibana version? ---
**Author:** ![akashk](https://avatars.discourse-cdn.com/v4/letter/a/ad7895/32.png) [@akashk](https://discuss.elastic.co/u/akashk)\ **Post date:** [August 19, 2021, 8:04am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/3 "2021-08-19T08:04:48Z")
Yes it is a time-based index pattern with documents having different fields.My Kibana version is 7.5.1 For data table I am simply performing aggregations on terms and splitting the rows like For example siteAddress and appName in below config .Likewise we also have pikedTime and triggeredTime but both belongs to different document as both pickedTime and triggeredTime coming from different sources(log files) in elasticsearch. "aggs": { "2": { "terms": { "field": "siteAddress.keyword", "order": { "\_key": "desc" }, "size": 5 }, "aggs": { "3": { "terms": { "field": "appName.keyword", "order": { "\_key": "desc" }, "size": 5 }, ---
**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\ **Post date:** [August 19, 2021, 8:28am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/4 "2021-08-19T08:28:34Z")
If you had all the information per document you could use scripted fields to do this calculation but as it is right now I don't think that this is possible. I think that there is another discuss post about it [Calculating one field from different documents](https://discuss.elastic.co/t/calculating-one-field-from-different-documents/119185) ---
**Author:** ![Felix\_Roessel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/felix_roessel/32/41623_2.png) [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)\ **Post date:** [August 19, 2021, 11:39am UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/5 "2021-08-19T11:39:53Z")
Wouldn't this be possible using the new Lens functions? ---
**Author:** ![Stratoula\_Kalafateli](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stratoula_kalafateli/32/70923_2.png) [@Stratoula\_Kalafateli](https://discuss.elastic.co/u/Stratoula_Kalafateli)\ **Post date:** [August 19, 2021, 12:28pm UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/6 "2021-08-19T12:28:37Z")
No, still the same problem. The user wants a calculation between two different docs. ---
**Author:** ![akashk](https://avatars.discourse-cdn.com/v4/letter/a/ad7895/32.png) [@akashk](https://discuss.elastic.co/u/akashk)\ **Post date:** [August 19, 2021, 1:25pm UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/7 "2021-08-19T13:25:24Z")
@Stratoula_Kalafateli @Felix_Roessel Can we acheive this using tranform to create entity centric index but again the question remains same how to join two separate documents to calculate difference in two fields in transform. ---
**Author:** ![nsouth](https://avatars.discourse-cdn.com/v4/letter/n/ecccb3/32.png) [@nsouth](https://discuss.elastic.co/u/nsouth)\ **Post date:** [August 19, 2021, 3:25pm UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/8 "2021-08-19T15:25:10Z")
Just to add to the discussion, I've recently been looking into options for doing calculations across events/documents. I've come across the following options, although I don't have a wealth of direct experience in any of them. 1. [Logstash Aggregate filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html) - Can piece together related events and saves a single event after the final event has been detected (or timed out). Has some scaling concerns. 2. [Logstash Elasticsearch filter and elasticsearch output](https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html#plugins-filters-elasticsearch-index) - Logstash can query elasticsearch for a previously-ingested event/document, use its fields to calculate something new, and then update the original document. 3. [Elasticsearch Transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) - After events have been ingested, transform them into an entity-centric index using the Transforms feature. I'm not sure how much delay you can expect from this post-processing, but Transforms can run in continuous mode, so in theory it can be fairly minimal. Anyone, feel free to correct me on anything. There's also some good discussion here: > [@Using painless to calculate durations](https://discuss.elastic.co/t/using-painless-to-calculate-durations/119495/7): > > Actually, the default according to documentation is 1800 seconds (30 minutes). Mine needs to span 24 hours as some of the events I am tracking are long running. ---
**Author:** ![nsouth](https://avatars.discourse-cdn.com/v4/letter/n/ecccb3/32.png) [@nsouth](https://discuss.elastic.co/u/nsouth)\ **Post date:** [August 23, 2021, 1:13pm UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/9 "2021-08-23T13:13:58Z")
I wanted to add an additional note for option #2 I listed. Using the Elasticsearch filter plugin also has scale concerns. If events are being processed in parallel, then you might not be able to guarantee that the "previous" event is already in Elasticsearch to be queried and updated. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 20, 2021, 1:14pm UTC](https://discuss.elastic.co/t/how-to-execute-dynamic-queries-with-visualisations/281640/10 "2021-09-20T13:14:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.