How to find the time difference between two documents (rows in sql) in an index (table in sql) with same Id

Elastic Stack Elasticsearch
1

Hello Everyone,
I am new to Elastic Search and Kibana Query Language.
I would like to find the time difference between the two documents which have the id as common between them as like below.
TimeStamp TRACE ID
2021-03-29 20:45:15.748 START 1234
2021-03-29 20:46:15.148 END 1234

I have given my tries with SQL Join and LAG and LEAD functions. But unfortunately, ES Driver doesn't support these as of now.

It would be really helpful if someone can help with either SQL or other search mechanisms in ELK.

Regards,
Ganesh Kumar

2

You can't really do that in a NoSQL world. It's better to do that kind of join at index time.

So wait for the end of an event and compute that at index time.

There's for example this logstash plugin which can help. In case you're using logstash.

3

Hi @dadoonet ,

I am not using logstash.

Can you please let me know how to define this join while creating the index.

Regards,
Ganesh Kumar

4

Let's say you have an event like:

{
  "id": "abc",
  "type": "start",
  "date": "date1"
}

And then another like:

{
  "id": "abc",
  "type": "stop",
  "date": "date2"
}

Compute the duration xyz=date2-date1 and store in elasticsearch:

POST /events/_doc
{
  "id": "abc",
  "date_start": "date1",
  "date_end": "date2",
  "duration": xyz
}
5

Hi @dadoonet ,

Thanks for the Solution. Let me explain my complete scenario here.

I am using a Log4j appender to send my logs from my application (REST API, Which has the JSON Logging capability) to Kibana.
So basically it's real-time sync between the application and the Elastic Search / Kibana index.
As mentioned in my post earlier, I have START and END tracepoints and would like to calculate the difference between them to identify the response time.

Please let me know if there are any possibilities to achieve the same.

Thanks,
Ganesh Kumar

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.