How do you join independent query results?

[rohit_kothuru]

I have 2 logs like below :

2018-11-20 04:41:23,873.873 - MainThread - 49102 - INFO views -  endTime - 2018-11-20 04:41:23.870460 reqId - id 
2018-11-20 04:41:23,863.863 - MainThread - 49102 - INFO views -  reqID - id , startTime - 2018-11-20 09:41:23.234

From first statement I need reqId and endTime.

From second statement I need reqId and startTime.

I need to join both the results and retrieve startTime and endTime for each reqId and calculte the time taken.

Can someone please help me to form a query to join the results.

[renjith_nair · Accepted solution]

@rohit_kothuru ,

Give this a try,

rex field=_raw "(?i)reqId\s-\s(?<reqId>\d+)" 
| rex field=_raw "startTime - (?<startTime>\d{4}-\d{1,2}-\d{1,2} \d{2}:\d{2}:\d{2}.\d{1,9})" 
| rex field=_raw "endTime - (?<endTime>\d{4}-\d{1,2}-\d{1,2} \d{2}:\d{2}:\d{2}.\d{1,9})"
| stats last(startTime) as startTime,last(endTime) as endTime by  reqId|eval TimeTaken=strptime(endTime,"%Y-%m-%d %H:%M:%S.%9N") - strptime(startTime,"%Y-%m-%d %H:%M:%S.%9N") 

You might need to fine tune the rex part based on your actual event

[rohit_kothuru]

@renjith.nair

Thanks Renjith. This works exactly as I wanted.
Another point I wanted to add is that :
1. endTime is in EST
2. startTime is in GMT.

I need to convert the startTime from GMT to EST and then calculate the time difference in milliseconds.

I tried relative_now but was not able to achieve my case. Can you suggest on how this can be done.

Once the time difference is calculted, I need to show the timechart for the average of time taken over past 30 days with a span of 1 day.

[woodcock]

That is a different question so ask in another post.

[renjith_nair]

Considering EST is 5 hours behind GMT, try adding -0500 (hhmm) to the time

eval startTime=startTime+ "-0500" before the difference calculation and then use strptime(startTime,"%Y-%m-%d %H:%M:%S.%9N%z")

The timezone offset from UTC, in hour and minute: +hhmm or -hhmm. For example, for 5 hours before UTC the values is -0500 which is US Eastern Standard Time.

Examples:

    Use %z to specify hour and minute, for example -0500
    Use %:z to specify hour and minute separated by a colon, for example -5:00
    Use %::z to specify hour minute and second separated with colons, for example -05:00:00
    Use %:::z to specify hour only, for example -05