How do I Redistribute duration of events when duration > 1h over a timechart with a 1h span?

[Shahindoh]

Hello,

First of all, sorry for my lack of knowledge if my question looks silly.

I have a datasource providing events as follows :

State Start Timestamp / UserName / StateName / Duration of the State in seconds / State End Timestamp

I'm trying to produce a timechart that is showing the duration in each state for each user with a 1h span so that we could see clearly the time spent in each state by the users for each hour of the day.

The issue is that a user can start a state at a given time and have a duration bigger than 1h.

For exemple, a user logs in and is available at 8:32 and it stays in "available" state during 2h.

What I get so far with a basic timechart span=1h of the states by user :

2h in 8h span

nothing in 9h span

nothing in 10h span

I would need to manipulate the query or the events in a way that will make the timechart report in this example :

28 min in 8h span

1 hour in 9h span

32 min in 10h span

as the state lasted between 8h32 and 10h32.

Here's my query today :

| eval AvailableDuration = if(State="Available",Duration,0)
| eval BusyDuration = if(State="Busy",Duration,0)
| eval CallDuration = if(State="In Call",Duration,0)

| timechart span=1h fixedrange=false useother=f limit=0

sum(CallDuration) as "In call"
sum(AvailableDuration) as "available"
sum(BusyDuration) as "Busy"

by UserName

Is there a way to redistribute the durations by manipulating data so that each hourly span is properly populated ?

Thanks in advance for your help !

[ITWhisperer · Accepted solution]

Try something like this

``` Calculate how long until top of the hour ```
| eval remaining=floor(relative_time(_time,"+1h@h")-_time)
``` Calculate how many complete hours in duration (this will be negative if duration completes within the hour) ```
| eval hours=floor((duration-remaining)/3600)
``` Calculate how much of last hour remains to complete duration ```
| eval last=duration-remaining-(hours*3600)
``` Determine how many hours to spread duration across ```
| eval maxrow=hours+1
``` Duplicate event as necessary ```
| eval row=mvrange(0,maxrow+1)
| mvexpand row
``` Reset duration depending on whether part of first hour, last hour, or complete hour ```
| eval duration=case(hours=-1,duration,row==0,remaining,row==maxrow,last,1==1,3600)
``` Drop zero duration (when duration hits hour boundary) ```
| where duration>0
``` Reset _time to beginning of event or hour ```
| eval _time=if(row==0,_time,relative_time(_time,"+".row."h@h"))

[Shahindoh]

Thanks a lot !

I just noticed one thing in your solution which is that I got issues with situations where there is a Duration < 3600 and the event is not "overflowing" on the next hour.

I added OldDuration field to keep a trace of the original even duration and put a table command to display the data after your proposition and noticed that the issue was happening for durations on a single line with the field "hours" = "-1"

So I just modified this way :

| eval Duration=case(hours=-1,OldDuration,row==0,remaining,row==maxrow,last,1==1,3600)

I think it is correct because it happens when Duration - Remaining time (in the current hour) is negative which makes sense.

Anyway thanks a lot !

[ITWhisperer]

Thanks - I have updated the solution (I don't think you need to save the original duration in a new field)

[Shahindoh]

You're absolutely right.

I didn't pay attention to that as I was using a table afterwards and wanted to see Old and new Duration calculation side by side.

But your code is not touching duration until the case condition so it's irrelevant to store old duration.