topic Average Time over two Timestamps in Getting Data In

Average Time over two Timestamps

JoeSco27 — Mon, 28 Sep 2020 14:18:30 GMT

I am trying to get the average Session duration by USER_ID, but a single USER_ID can have multiple SESSION_ID. The SESSION_ID is the unique identifier. I currently have a table that looks like this:

USER_ID------SESSION_ID ----------------------- start --------------------------- stop

UserA ------{firstSessionID} ---------- 20130710 08:15:53 -------- 20130710 08:16:15

UserB-----{secondSessionID} ----- 20130710 08:16:42 -------- 20130710 08:16:55

UserA------{ThirdSessionID} ------- 20130709 13:34:23 -------- 20130709 13:35:34

I am trying to eventually have a list of all the USER_ID and the average Session duration by USER_ID. My search for the above result looks like this:

index=loghistory SESSION_ID=* USER_ID=* DEALER_ID=* USER_ID!="N/A" |stats earliest(EVENT_TIMESTAMP) as start, latest(EVENT_TIMESTAMP) as stop by USER_ID,SESSION_ID

Thank you in advance.

Re: Average Time over two Timestamps

bmacias84 — Wed, 10 Jul 2013 14:41:21 GMT

use the Transaction command.

Re: Average Time over two Timestamps

Damien_Dallimor — Wed, 10 Jul 2013 14:56:21 GMT

Try something like this.
Converting times to epoch times , then doing the math on the epoch value :

...| eval startSession=strptime(start,"%Y%m%d %H:%M:%S") | eval endSession=strptime(stop,"%Y%m%d %H:%M:%S") | eval sessionDuration=endSession-startSession | stats avg(sessionDuration) by USER_ID

Re: Average Time over two Timestamps

JoeSco27 — Wed, 10 Jul 2013 16:33:12 GMT

This works, thank you. The time that returns is in seconds because it was converted to epoch, correct?

Re: Average Time over two Timestamps

Damien_Dallimor — Wed, 10 Jul 2013 16:36:44 GMT

Yes , seconds. You can use "strftime" to convert it back into another format.

Don't forget to accept the answer if it worked.