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

[mayurkadam24]

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.

[somesoni2]

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) 

[mayurkadam24]

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

[mayurkadam24]

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.

[somesoni2]

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

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

[mayurkadam24]

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 

[somesoni2]

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")