difference between login and logout time

[#1]

i have one data where user is doing multiple login and logout in day and i have to find the total number of hours user was logged in system

sample data

Name Date Login_Date Time User Name Terminal Message Text kcinhjsap19 09.09.2015 09-09-2015 08:46:38 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 09.09.2015 09-09-2015 09:19:30 AAMRAA l-3334 User Logoff kcinhjsap19 09.09.2015 09-09-2015 11:42:13 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 09.09.2015 09-09-2015 12:54:25 AAMRAA l-3334 User Logoff kcinhjsap19 09.09.2015 09-09-2015 13:07:26 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 09.09.2015 09-09-2015 13:40:07 AAMRAA l-3334 User Logoff kcinhjsap19 10.09.2015 10-09-2015 09:06:00 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 10.09.2015 10-09-2015 09:40:33 AAMRAA l-3334 User Logoff kcinhjsap19 10.09.2015 10-09-2015 14:57:33 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 10.09.2015 10-09-2015 15:04:51 AAMRAA l-3334 User Logoff kcinhjsap19 11.09.2015 11-09-2015 10:20:45 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 11.09.2015 11-09-2015 10:47:52 AAMRAA l-3334 User Logoff kcinhjsap19 11.09.2015 11-09-2015 12:50:29 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 11.09.2015 11-09-2015 13:10:37 AAMRAA l-3334 User Logoff kcinhjsap19 11.09.2015 11-09-2015 15:32:42 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 11.09.2015 11-09-2015 15:54:24 AAMRAA l-3334 User Logoff kcinhjsap19 14.09.2015 14-09-2015 09:47:31 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 14.09.2015 14-09-2015 10:13:27 AAMRAA l-3334 User Logoff kcinhjsap19 14.09.2015 14-09-2015 12:19:45 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 14.09.2015 14-09-2015 12:40:53 AAMRAA l-3334 User Logoff kcinhjsap19 15.09.2015 15-09-2015 09:52:06 AAMRAA l-3334 Logon Successful (Type=A) kcinhjsap19 15.09.2015 15-09-2015 09:53:43 AAMRAA l-3334 User Logoff

[swuehl · Accepted solution]

You can start with something like

INPUT:

LOAD * INLINE [

Name Date Login_Date Time User Name Terminal Message Text

kcinhjsap19 09.09.2015 09-09-2015 08:46:38 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 09:19:30 AAMRAA l-3334 User Logoff

kcinhjsap19 09.09.2015 09-09-2015 11:42:13 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 12:54:25 AAMRAA l-3334 User Logoff

kcinhjsap19 09.09.2015 09-09-2015 13:07:26 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 09.09.2015 09-09-2015 13:40:07 AAMRAA l-3334 User Logoff

kcinhjsap19 10.09.2015 10-09-2015 09:06:00 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 10.09.2015 10-09-2015 09:40:33 AAMRAA l-3334 User Logoff

kcinhjsap19 10.09.2015 10-09-2015 14:57:33 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 10.09.2015 10-09-2015 15:04:51 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 10:20:45 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 10:47:52 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 12:50:29 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 13:10:37 AAMRAA l-3334 User Logoff

kcinhjsap19 11.09.2015 11-09-2015 15:32:42 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 11.09.2015 11-09-2015 15:54:24 AAMRAA l-3334 User Logoff

kcinhjsap19 14.09.2015 14-09-2015 09:47:31 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 14.09.2015 14-09-2015 10:13:27 AAMRAA l-3334 User Logoff

kcinhjsap19 14.09.2015 14-09-2015 12:19:45 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 14.09.2015 14-09-2015 12:40:53 AAMRAA l-3334 User Logoff

kcinhjsap19 15.09.2015 15-09-2015 09:52:06 AAMRAA l-3334 Logon Successful (Type=A)

kcinhjsap19 15.09.2015 15-09-2015 09:53:43 AAMRAA l-3334 User Logoff

] (delimiter is '\t');

RESULT:

LOAD *,

     If([Message Text] LIKE '*Logoff' and Previous([User Name])=[User Name],

               Interval(Timestamp - Previous(Timestamp)))

                    as Duration;

LOAD *, Date+Time as Timestamp RESIDENT INPUT

ORDER BY [User Name], Date, Time;

DROP TABLE INPUT;

Then aggregate the new Duration field in your chart objects:

=Interval(Sum(Duration))

[#3]

tanks lot swuehl‌ its working i am so sorry for not been clear i have to apply

1.  If same message type are repeated consecutively then consider only 1^st line and delete the rest of the line for that user coming consecutively.

this condition as well if you can tell it will be great help

[Saravanan_Desingh]

I just modified swuehl‌ code. Please check if it useful.

INPUT:
LOAD * INLINE [
Name, Date, Login_Date, Time, "User Name", Terminal, "Message Text"
kcinhjsap19, 09.09.2015, 09-09-2015, 08:46:38, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 09:19:30, AAMRAA, l-3334, User Logoff
kcinhjsap19, 09.09.2015, 09-09-2015, 11:42:13, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 12:54:25, AAMRAA, l-3334, User Logoff
kcinhjsap19, 09.09.2015, 09-09-2015, 13:07:26, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 09.09.2015, 09-09-2015, 13:40:07, AAMRAA, l-3334, User Logoff
kcinhjsap19, 10.09.2015, 10-09-2015, 09:06:00, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 10.09.2015, 10-09-2015, 09:40:33, AAMRAA, l-3334, User Logoff
kcinhjsap19, 10.09.2015, 10-09-2015, 14:57:33, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 10.09.2015, 10-09-2015, 15:04:51, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 10:20:45, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 10:47:52, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 12:50:29, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 13:10:37, AAMRAA, l-3334, User Logoff
kcinhjsap19, 11.09.2015, 11-09-2015, 15:32:42, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 11.09.2015, 11-09-2015, 15:54:24, AAMRAA, l-3334, User Logoff
kcinhjsap19, 14.09.2015, 14-09-2015, 09:47:31, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 14.09.2015, 14-09-2015, 10:13:27, AAMRAA, l-3334, User Logoff
kcinhjsap19, 14.09.2015, 14-09-2015, 12:19:45, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 14.09.2015, 14-09-2015, 12:40:53, AAMRAA, l-3334, User Logoff
kcinhjsap19, 15.09.2015, 15-09-2015, 09:52:06, AAMRAA, l-3334, Logon Successful (Type=A)
kcinhjsap19, 15.09.2015, 15-09-2015, 09:53:43, AAMRAA, l-3334, User Logoff
] (delimiter is ',');

RESULT:
//LOAD *,
//     If([Message Text] LIKE '*Logoff' and Previous([User Name])=[User Name],
//               Interval(Timestamp - Previous(Timestamp)))
//                    as Duration;
Load *,
  Interval(OutTime - InTime) As Duration;
Load
  FirstSortedValue([User Name],Time) As InUser,
  FirstSortedValue(Time,Time) As InTime,
  FirstSortedValue(Time,-Time) As OutTime,
  Date
Group By [User Name], Date
  ;
LOAD *, Date#(Date,'DD.MM.YYYY')+Time as Timestamp RESIDENT INPUT
ORDER BY [User Name], Date, Time;


DROP TABLE INPUT;

[#5]

Thanks lot. for your help