How to inner join with field subtraction on two fields part of different searches?

[batham]

Hi,

I am using inner join to form a table between 2 search, search is working fine but i want to subtract 2 fields in which one field is part of one search and another field is part of next search, I am displaying response in a table which  contains data from both search

 example

line1: datetime: , trace: 12345 , Request Received: {1}, URL:http://

line2:datetime: , trace: 12346 , Request Received: {2}, URL:http://

line3:datetime: , trace:12345 , Reponse provided: {3}

line4:datetime: ,trace:12346 , Reponse provided :{4}

In line1 and line 3 trace is common field and so is in line 1 and line 4

i have combined the result as

.... | table trace, Request,startTime
| join type=Inner trace
[ search .........
| table trace, Response,  EndTime]

Which is giving me response as below

trace      request     startTime     response     EndTime

12345   {1}                    09:18:20      {3}.              09:18:50

12346   {2}                   09:19:20       {4}.               09:20:21

I want to find out response time subtractingEndTime - startTime. 

[batham · Accepted solution]

Figured out the solution.

[richgalloway]

To subtract times, you first must convert the times into integer (epoch) form using strptime().

.... | table trace, Request,startTime
| join type=Inner trace
[ search .........
| table trace, Response,  EndTime]
| eval ST=strptime(startTime, "%H:%M:%S"), ET=strptime(EndTime, "%H:%M:%S")
| eval ResponseTime = ET - ST
| eval ResponseTime = tostring(ResponseTime, "duration")
| table trace request starTime response EndTime ResponseTime