SQL Like join on elasticsearch - Help

Hi,

I need help on the elastic search query where it can join on the transaction id on same index. I know the joins are available/allowed on elastic search. Below is the example of my elasticsearch index. (Test index)

id         | Time  |     type                     

1877198725|125 |Service1
1811113526|172 |Service2(capture)
1811113526|266 |Service1
2073365217|500 |Service1
2123976099|1894 |Service1
732021456 |94 |Service2(capture)
732021456 |156 |Service1
788687662 |312 |Service1
1761756905|766 |Service1
1209151210|47 |Service2(capture)
309425654 |189 |Service1
1405235319|265 |Service1
675231293 |334 |Service2(capture)
675231293 |412 |Service1
1514112479|156 |Service2(capture)

Now I want to do the query to aggregate the timing (time column) only on the Id's which are matching. These logs are coming from two different log's and I am putting it in the same index. Not sure what would be the best approach to put service1 logs in a different index (let's say index1) and service2 logs in a different index (index2). But I feel even if I put in the different index there will be still a problem with Join i.e. aggregating on time only if id's are same.

It's something like select id, time from test where id=(select id from test where = 'Service2(capture)')

I hope I am able to explain the problem that I am facing.

Can someone please help with this?

From what I know, SQL commands are available in the paid version of ES:
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-commands.html

If you are using the free version, I don't know of a way that the operation is possible. What you can do is to use aggregate plugins in your stack wherever you are parsing your logs (Logstash or fluentd).

You may be also able to do it in a graph or table in Kibana.

Thanks @ Mohammad_Etemad for your quick reply, I am using free version of it. I tried it in Kibana but the problem that I am facing is where id's are not same so for example, it is showing the graph of below id's aggregate time for which I don't want.

2073365217|500 |Service1
2123976099|1894 |Service1
788687662 |312 |Service1
1761756905|766 |Service1
1209151210|47 |Service2(capture)
309425654 |189 |Service1
1405235319|265 |Service1

Even in logstash I am not able to get aggregate working as on it as I need to find the max time from it. If you have some idea you can share some way of doing it.

find the max time out of below where id's are matching.

1811113526|172 |Service2(capture)
1811113526|266 |Service1

I hope this clarifies the problem.

@mindbee Im not following, sorry. Why would you need the max time? I thought you are trying to aggregate the time.

@Mohammad_Etemad sorry for confusion the service1 goes to service2 and they both have their individual time in there log files in and then again service2 returns the response to service1 logs where it shows the total time taken for the transaction.

So basically log will look like
From service1 --> 123| start |0| service1 --> server1 service1.log file
From service1 --> 123| called|1| service1 --> server1 service1.log file
From service2 --> 123| start |1| service2 -- > server2 service2.log file
From service2 --> 123 | called|3|service2 --> server2 service1.log file
From service --> 123 | complete|5|service2(capture) --> server2 service1.log file
From service1 --> 123|complete|7|service1 --> server1 service1.log file

So total time taken for entire transaction is 7 seconds, so I want to capture 7 seconds as the time taken for completing the entire transaction.

The will be some id's which will be in server1 logs and server2 logs but don't want to show the graph for the values (times) where the id are not same in both server1 and server2 logs.

The only thing common is id for correlating it. Can this be achieved?

Anyone can help with this?

Hi,

I am able to get the results using below query.

POST _sql?format=txt
{
    "query": "SELECT transactionID.keyword,  max(responseTime)  FROM \"test-logs\" GROUP BY transactionID.keyword HAVING count(transactionID.keyword)=2 "
}

This may help if some is facing issue in future.

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