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.
@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 ,
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
@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.
That is a different question so ask in another post.
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