Hi, I'm trying to get the total duration of events for each user from access logs with time gap.
sample event:
_time user
2021-06-30 00:00:26 user1
22021-06-30 01:00:26 user1
22021-06-30 01:00:26 user1
22021-06-30 01:20:26 user1
and then there are no events for 4 hours
22021-06-30 05:00:26 user1
22021-06-30 05:30:26 user1
22021-06-30 06:02:26 user1
I'm trying to calculate the total duration of day. Any ideas how to achieve this?
You can get diff between events using this.
| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff
If you want to filter differences which are more than 3 hours then add this.
| where diff< (3*60*60)
You can validate your results by executing one by one step.
By adding below you can get total duration per day.
| bin _time span=d
| stats sum(diff) as total_duration by _time
This is full search. You can change as per your requirement.
YOUR_SEARCH
| fields _time user
| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff
| where diff< (3*60*60)
| bin _time span=d
| stats sum(diff) as total_duration by _time,user
My Sample Search :
| makeresults | eval _raw="time,user
2021-06-30 00:00:26,user1
2021-06-30 01:00:26,user1
2021-06-30 01:00:26,user1
2021-06-30 01:20:26,user1
2021-06-30 05:00:26,user1
2021-06-30 05:30:26,user1
2021-06-30 06:02:26,user1"| multikv forceheader=1
| eval _time= strptime(time,"%Y-%m-%d %H:%M:%S")
| fields _time user
| streamstats earliest(_time) as pre_time window=2 by user
| eval p_time= strftime(pre_time,"%Y-%m-%d %H:%M:%S")
| eval diff=_time - pre_time
| fields _time user diff
| where diff< (3*60*60)
| bin _time span=d
| stats sum(diff) as total_duration by _time,user
Thanks
KV
▄︻̷̿┻̿═━一
If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.