Dashboards & Visualizations

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

Shahindoh
Explorer

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 !

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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"))

 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Explorer

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 !

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Shahindoh
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...