topic Re: How do you join independent query results? in Alerting

How do you join independent query results?

rohit_kothuru — Tue, 20 Nov 2018 10:26:49 GMT

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.

Re: How do you join independent query results?

renjith_nair — Tue, 20 Nov 2018 15:40:57 GMT

@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

Re: How do you join independent query results?

rohit_kothuru — Wed, 21 Nov 2018 13:19:09 GMT

@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.

Re: How do you join independent query results?

renjith_nair — Thu, 22 Nov 2018 09:18:41 GMT

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

Re: How do you join independent query results?

woodcock — Mon, 03 Dec 2018 22:23:47 GMT

That is a different question so ask in another post.