Alerting

How do you join independent query results?

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

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

Tags (1)
0 Karma
1 Solution

renjith_nair
SplunkTrust
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!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
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!
0 Karma

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

0 Karma

woodcock
Esteemed Legend

That is a different question so ask in another post.

0 Karma

renjith_nair
SplunkTrust
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!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...