How to obtain the time difference between Host connection and disconnection?

[edrivera3]

Hi

I have a log file and I want to know how much time passed between HOST connection and disconnection. In the log, the connection message always appears as "as7_trans CONNECT" and the disconnection always appears as "262 DISCON".

I would like to obtain a table with host, transaction and total time.

Log file example (9 events):

host    trans.  timestamp             message  

host1   27609   2015-07-14 15:47:10   as7_trans CONNECT
host1   27609   2015-07-14 15:47:10   220 GET_SVR_INFO
host1   27609   2015-07-14 15:48:10   220 Response 0 DPI_ACKNOWLEDGE
host1   27609   2015-07-14 15:48:10   262 DISCON
host1   28792   2015-02-23 10:47:05   Dirserver host1 XXX.XXX.XXX.XX 
host1   27611   2015-07-14 15:47:13   as7_trans CONNECT
host1   27611   2015-07-14 15:47:13   220 GET_SVR_INFO
host1   27611   2015-07-14 15:49:17   220 Response 0 DPI_ACKNOWLEDGE
host1   27611   2015-07-14 15:49:17   262 DISCON

I appreciate your help.

[jeremiahc4 · Accepted solution]

I know there's a better way to do this with stats, streamstats, or something like that but I've always used the transaction command which is more expensive on CPU. Something like the below;

<search yielding above results> | transaction host,trans startswith="*CONNECT" endswith="*DISCON"

Ref: http://docs.splunk.com/Documentation/Splunk/6.2.4/Search/Abouttransactions

[jeremiahc4]

Forgot to mention that transaction creates a "duration" field which is in seconds if I recall correctly.

[edrivera3]

Yes I know. I answered my own question before your response, but I did the same thing that you proposed. That's why I accepted your answer. Thanks.

... | transaction startswith=(message="as7_trans CONNECT") endswith=(message="262 DISCON") | Duration transaction host