How to find duration between multiple timestamps of different users?

[limalbert]

Hi all,

The boundary of the logs: date and user. Total logs is more than 1000 logs.

1. How should I list the date? I am thinking to use stats and list the timestamps per date and user. User will have multiple timestamps, varies from 2 to 10 timestamps per day. Sample format like below. currentDate  User    list(time) 3/30/17    user1   9:00:00, 10:00:00, 11:00:00 3/30/17    user2   9:00:00, 12:00:00, 14:00:00 3/31/17    user1   7:00:00, 14:00:00, 19:00:00 3/31/17    user2   13:00:00, 17:00:00, 20:00:00 ...
2. Then, on a new column, find duration of the first and the second timestamps, second to third timestamps, etc. Sample format like below. currentDate  User   list(time)            Duration in hour 3/30/17    user1   9:00:00, 10:00:00, 11:00:00      1, 1 3/30/17    user2   9:00:00, 12:00:00, 14:00:00      3, 2 3/31/17    user1   7:00:00, 17:00:00, 19:00:00      10, 2 3/31/17    user2   13:00:00, 17:00:00, 20:00:00      4, 3 ...
3. Lastly, count users who have duration of 10 hours.

[somesoni2 · Accepted solution]

Assuming you got field _time with full timestamp and field User, give this a try. Query 3 before last where clause actually gives data for all three. You just need to add appropriate table command afterwards.

For 1

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| stats list(currentTime) as "List Times" by currentDate User

For 2

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" by currentDate User

For 3

your base search with field _time and User
| eval currentDate=strftime(_time,"%m/%d/%y")
| eval currentTime=strftime(_time,"%H:%M:%S")
| sort 0 currentDate User currentTime
| streamstats current=f window=1 values(_time) as prev_time by currentDate User
| eval duration=round((_time-prev_time)/3600)
| stats list(currentTime) as "List Times" list(duration) as "Duration in hours" sum(duration) as "Total Duration"  by currentDate User | where 'Total Duration'>10 
| stats count

[limalbert]

Hi,

Do you mind explaining the sort and streamstats that you did there? It works, but I still don't understand what you did.

Thanks!

[somesoni2]

The sort command does sort the event based on specified field, so for every day, every users, it'll list the events in ascending order of time for that day. This is required so that in streamstats, I would be able to grab the value of timestamp of first event in the second event (you can just see what it does by executing search before streamstats, see how the data looks and then add streamstats command). Basically if you're data was like this (after sort)

_time user
00:00 A
01:00 A
02:00 A

Steamstats will give output like this (adding a new column to result set

_time user prev_time
00:00 A <<it will be blank/null>>
01:00 A 00:00
02:00 A 01:00