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)
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.
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.
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.
@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?
POST _sql?format=txt
{
"query": "SELECT transactionID.keyword, max(responseTime) FROM \"test-logs\" GROUP BY transactionID.keyword HAVING count(transactionID.keyword)=2 "
}
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.