Average Time over two Timestamps

[JoeSco27]

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.

[Damien_Dallimor · Accepted solution]

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

[Damien_Dallimor]

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

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

[JoeSco27]

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

[bmacias84]

use the Transaction command.