Splunk Search

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

mayurkadam24
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

somesoni2
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

mayurkadam24
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

mayurkadam24
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

somesoni2
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, transaction_id, request_in, response_out

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

mayurkadam24
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

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