How to calculate idle time between events of a single transaction?

[p_gurav]

Hi,

I have the following session logs of users:

"2016-12-14 14:27:39" ROWNUM="6152288", ENDED_AT="2016-12-14 14:27:49.002228", USER_ID="XXXX1", PICK_UP_TIME="2016-12-14 14:27:41.633357"
"2016-12-14 14:27:40" ROWNUM="6173370", ENDED_AT="2016-12-14 14:28:06.189263", USER_ID="XXXX1", PICK_UP_TIME="2016-12-14 14:27:51.946026"
"2016-12-14 14:27:57" ROWNUM="6174967", ENDED_AT="2016-12-14 14:28:20.979682", USER_ID="XXXX1"", PICK_UP_TIME="2016-12-14 14:28:09.057467"

So I used the transaction command to find sessions of users in within a certain time period. I use | transaction USER_ID to get all sessions in one event.

I want to calculate idle time between above events i.e second(PICK_UP_TIME) - first(ENDED_AT) to get the time gap between all events in one transaction. e.g
PICK_UP_TIME(2016-12-14 14:27:51.946026) - ENDED_AT(2016-12-14 14:27:49.002228) = 2.94 sec
PICK_UP_TIME(2016-12-14 14:28:09.057467) - ENDED_AT(2016-12-14 14:28:06.189263) = 2.86 sec

Could you please help?

[cmerriman · Accepted solution]

you'll want to use streamstats instead of transaction, I think. You can't really breakout events from transaction nicely.

something like this maybe:

...|sort 0 USER_ID - _time |streamstats current=f window=1 latest(PICK_UP_TIME) as nextPICK_UP_TIME by USER_ID|table USER_ID ENDED_AT nextPICK_UP_TIME|eval idleTime=nextPICK_UP_TIME-ENDED_AT

[p_gurav]

Thanks a lot @cmerriman.