Splunk Search
Highlighted

How to edit my search to find the average time duration between 2 events of same transaction id?

New Member

Below is sample transaction id having multiple events of which 2 specific events are as follows:

{  
Date_time: 22/02/2017 18:55:51
transaction_id: 87873
component: ula
redirect-url="http://someURL"
logpoint: response_out
}

{  
Date_time: 22/02/2017 18:55:56
transaction_id: 87873
component: ula
request_uri="/xyz"
logpoint: request_in
}

If there are millions of such different transactions id's each having the same above 2 events then i need to calculate the avg. time duration between these 2 specific events for same transaction id.

I have prepared below search but not sure if this gives me the avg. duration between the 2 events of same transaction_id:

index=ABC | transaction startswith=(component=ula AND logpoint="response_out" AND redirect-url="http://someURL") 
endswith=(component=ula AND logpoint="request_in" AND request-uri="/xyz")|dedup transaction_id | timechart span=1h avg(duration)

Another requirement is to find data in tabular format giving the time duration between these 2 specific events against the transaction_id in a log of million transaction ids ordered by maximum duration in descending order:
Eg.

transaction_id              duration(in sec)
87873                             5
4323344                        3
7676442                        2
5645625                        1
......
5657354                       0.5

Appreciate a quick response for the 2 searches requested.

0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

SplunkTrust
SplunkTrust

I would try like this (assuming the field extractions are all working as expected)
Requirement 2 - tabular listing of transaction and duration

index=ABC logpoint="request_in" OR logpoint="response_out"
| chart values(_time) over transaction_id by logpoint
| eval duration=coalesce(response_out-request_in,"In Process")

Requirement 1 -

index=ABC logpoint="request_in" OR logpoint="response_out"
| chart values(_time) over transaction_id by logpoint
| eval _time=request_in
| eval duration=coalesce(response_out-request_in,"In Process")
| timechart  avg(duration) 
0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

New Member

Hi Somesh,

I'm getting below error in splunk for the query shared by you:

Error in 'eval' command: The expression is malformed. Expected ).
The search job has failed due to an error. You may be able view the job in the Job Inspector.

0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

SplunkTrust
SplunkTrust

What do you get a fields (in statistics tab) when you run following? If the field extraction are working fine, you should get fields, transactionid, requestin, response_out

index=ABC logpoint="request_in" OR logpoint="response_out"
 | chart values(_time) over transaction_id by logpoint
0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

New Member

For Requirement 2, I'm getting O/P in below format which is not the expected O/P. The exepected O/P is given in the question raised. Can you please check if the query is correct ?

usecase-id        duration                          request-in               response-out   

00a9047f-1ea3-4e0e-80e0-d2125125aeec   In Process  1492093827.552             1492093827.793
                                                                                                                                                         1492093827.552              1492093827.793 
0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

SplunkTrust
SplunkTrust

Try this for requirement 2

index=ABC logpoint="request_in" OR logpoint="response_out"
 | stats max(_time) as end min(-_time) as start by transaction_id
 | eval duration=coalesce(end-start,"In Process")
0 Karma
Highlighted

Re: How to edit my search to find the average time duration between 2 events of same transaction id?

New Member

For Requirement 1, I'm getting O/P in below format which is not the expected O/P. The exepected O/P is given in the question raised. Can you please check if the query is correct ?

usecase-id duration request-in response-out

00a9047f-1ea3-4e0e-80e0-d2125125aeec In Process 1492093827.552 1492093827.793
1492093827.552 1492093827.793

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.