Difference between timestamps, within sessions

[#1]

Hi everyone,

I have Audit log data with Timestamps and Users, and I have calculated a Session ID as well. Looks like this:

Session	Timestamp	User	Message
1	2015-07-15 11:19:52	local\John	Apply Server\BM1356-45
1	2015-07-15 11:20:09	local\John	Year 2014
2	2015-07-15 11:20:20	local\Mary	PasdgasdgOMM SYS FAC
1	2015-07-15 11:21:46	local\John	Year 2013
1	2015-07-15 11:21:48	local\John	Year 2014
3	2015-07-15 11:22:22	local\Joe	Activated sheet Document\SH04
3	2015-07-15 11:22:22	local\Joe	Apply Server\BM1350-45
6	2015-07-15 11:45:26	local\Joe	asdfs
6	2015-07-15 11:45:46	local\Joe	Sheet Object Document\TB04
7	2015-07-15 12:03:10	local\John	Clear All
7	2015-07-15 12:03:34	local\John	Coat 255

My question is, within Sessions, I want to calculate a new "Session Time" column that takes the difference between the current timestamp and the first timestamp in the session. So Session 1 would get:

Session Time	Session	Timestamp
0:00	1	2015-07-15 11:19:52
0:17	1	2015-07-15 11:20:09
1:54	1	2015-07-15 11:21:46
1:56	1	2015-07-15 11:21:48

Does anyone know how I could do this? Thanks!

[swuehl · Accepted solution]

This should do the session time:

Audit:

LOAD 

     Timestamp,

     Document, 

     User,

     Message

FROM

[LogDemoNew.txt]

(txt, utf8, embedded labels, delimiter is '\t', msq);

SessionID:

LOAD

if(peek(User)<> User or peek(Timestamp)-Timestamp > interval#('10','mm'), rangesum(1, Peek(Session)), Peek(Session) ) as Session,

Timestamp,

User,

Document,

Message

RESIDENT Audit

ORDER BY User, Timestamp desc;

DROP TABLE Audit;

SessionTime:

LOAD *, if(Peek(Session) <> Session, 0, interval(rangesum( Timestamp, -peek(Timestamp), peek(SessionTime)),'hh:mm:ss')) as SessionTime

RESIDENT SessionID

ORDER BY User,Timestamp asc;

DROP TABLE SessionID;

SessionIDResorted:

NOCONCATENATE

LOAD Autonumber(Session) as Session,

  SessionTime,

  Timestamp,

  User,

  Document,

  Message

RESIDENT SessionTime

ORDER BY Timestamp;

DROP TABLE SessionTime;

[#3]

Thank you Swuehl!!!