## How do you join independent query results?

New Member

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.

Tags (1)
1 Solution
SplunkTrust

@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

Happy Splunking!
SplunkTrust

@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

Happy Splunking!
New Member

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

Esteemed Legend

That is a different question so ask in another post.

SplunkTrust

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
``````
Happy Splunking!
Get Updates on the Splunk Community!

#### Observability Cloud | AWS PrivateLink Enabled for Splunk Observability Cloud

We’ve enabled AWS PrivateLink for Observability Cloud, giving you an additional inbound connection to send ...

#### Index This | A sphere has three, a circle has two, and a point has zero. What is it?

September 2023 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...