Splunk Search

How to distribute an event among many time buckets taking into account the duration of the event?

Path Finder

I have a group of entries that has starttime, endtime , duration and name. Some of them are concurrent
some of them not.

I would like to create a table that has time buckets of 5 seconds and shows the duration of each element in each time bucket.

For instance, for the following values:


starttime=0 endtime=10 duration=10 name=test1
starttime=3 endtime=15 duration=12 name=test1
starttime=5 endtime=8 duration=3 name=test2
starttime=9 endtime=11 duration=2 name=test2

will create a table like this:

Bucket(time) test1(list(duration in bucket)) test2(list(duration in bucket))
_time=0 5,2 0,0
_time=5 5,5 3,1
_time=10 0,5 0,1
_time=15 0,0 0,0
TOTALS = 10,12 3,2

or even better using the max function by bucket.

Bucket(time) test1(max(duration in bucket)) test2(max(duration in bucket))
_time=0 5 0
_time=5 5 3
_time=10 5 1
_time=15 0 0

I believe that the key is somewhere with the following functions:
|timechart span=5s list(duration) as dur |where dur >0
or
|timechart span=5s list(duration) by name
(where column name value >0)

But I fail to see how to divide the events in the different buckets since i only have one event and the duration and not multiple events that falls in each bucket. Can somebody help me?

1 Solution

SplunkTrust
SplunkTrust

One extra nuance, is that if an event has a duration greater than 10 seconds, it will be entirely missing from at least one of your 5second buckets, so you have to somehow avoid it not being counted for that bucket.

This sort of thing is fixable by using mvrange() and mvexpand, to fabricate extra copies of the events to fill all the spaces.

Skipping to the end, here's a full search that I think will do what you need. Again I'm assuming that you have starttime, endtime, duration and name on each event. Also that starttime and endtime are epochtime values, and duration is measured in seconds.

<your search terms>
| eval bucket_start=mvrange(start_time,end_time,5) 
| mvexpand bucket_start
| bin bucket_start span=5s
| eval duration_correction1=max(bucket_start-start_time,0)
| eval duration_correction2=max(end_time-bucket_start-5,0)
| eval duration_within_bucket=duration - duration_correction1 - duration_correction2
| stats max(duration_within_bucket) as duration by name

Walking through this, we make a field called bucket_start, and use mvrange() to assign it a multivalue value. If starttime were 1002, and endtime were 1018, this would be 1002,1007,1012,1017. Now say for a given event, it has N multivalue values for starttime. We then immediately use mvexpand to turn each of our events into N copies of the event, each with one particular value for starttime. (Weird stuff but useful for a variety of advanced concurrency cases.)

Next up, at this point we've created lots of little sets of events, but they're not all aligned on the same 0,5,10,15 fenceposts. So we can use the bin command to round our bucket_start values down to the nearest 5 seconds.

The next challenge is that for each of our 5 second buckets, we have to calculate, for that value of name, how much of the total duration was actually in that given 5 second bucket. This is easy to work out on pencil and paper and it amounts to snipping off the components of the duration that fall outside our bucket. The one that falls earlier than our bucket is durationcorrection1 and the seconds that fall later than our bucket are in durationcorrection2. Once we subtract those off we'll have "durationwithinbucket" that is actually accurate.

And then we're on the homestretch cause | stats max(duration_within_bucket) as duration by name will roll it all up and give you what you need. I think.

View solution in original post

SplunkTrust
SplunkTrust

One extra nuance, is that if an event has a duration greater than 10 seconds, it will be entirely missing from at least one of your 5second buckets, so you have to somehow avoid it not being counted for that bucket.

This sort of thing is fixable by using mvrange() and mvexpand, to fabricate extra copies of the events to fill all the spaces.

Skipping to the end, here's a full search that I think will do what you need. Again I'm assuming that you have starttime, endtime, duration and name on each event. Also that starttime and endtime are epochtime values, and duration is measured in seconds.

<your search terms>
| eval bucket_start=mvrange(start_time,end_time,5) 
| mvexpand bucket_start
| bin bucket_start span=5s
| eval duration_correction1=max(bucket_start-start_time,0)
| eval duration_correction2=max(end_time-bucket_start-5,0)
| eval duration_within_bucket=duration - duration_correction1 - duration_correction2
| stats max(duration_within_bucket) as duration by name

Walking through this, we make a field called bucket_start, and use mvrange() to assign it a multivalue value. If starttime were 1002, and endtime were 1018, this would be 1002,1007,1012,1017. Now say for a given event, it has N multivalue values for starttime. We then immediately use mvexpand to turn each of our events into N copies of the event, each with one particular value for starttime. (Weird stuff but useful for a variety of advanced concurrency cases.)

Next up, at this point we've created lots of little sets of events, but they're not all aligned on the same 0,5,10,15 fenceposts. So we can use the bin command to round our bucket_start values down to the nearest 5 seconds.

The next challenge is that for each of our 5 second buckets, we have to calculate, for that value of name, how much of the total duration was actually in that given 5 second bucket. This is easy to work out on pencil and paper and it amounts to snipping off the components of the duration that fall outside our bucket. The one that falls earlier than our bucket is durationcorrection1 and the seconds that fall later than our bucket are in durationcorrection2. Once we subtract those off we'll have "durationwithinbucket" that is actually accurate.

And then we're on the homestretch cause | stats max(duration_within_bucket) as duration by name will roll it all up and give you what you need. I think.

View solution in original post

Path Finder

Dear @sideview, your reply bring me to the good path but still i'm not in the final result with it. What i've done until now is the following:

|table ELEMENT_NAME ELEMENT_ALIAS DESCRIPTION Duration START_TIME END_TIME
|eval bucket_time_size=1800
|eval start_bucket_time=START_TIME-(START_TIME%bucket_time_size)
|eval end_bucket_time=END_TIME-(END_TIME%bucket_time_size)
|eval bucket_start=mvrange(start_bucket_time,end_bucket_time,bucket_time_size)
|mvexpand bucket_start
|eval _time=bucket_start
|eval duration_bucket=if(_time<=START_TIME,if(_time+bucket_time_size>END_TIME,END_TIME-START_TIME,(_time+bucket_time_size)-START_TIME),if(_time+bucket_time_size>END_TIME,END_TIME-_time,bucket_time_size))
|eval availability=duration_bucket/bucket_time_size
|timechart span=1800s avg(availability) as duration_bucket by ELEMENT_ALIAS
|fillnull value=1    

The first think is I define the bucket in a variable(so i can change it whenever i want) . The second thing is to align all the buckets to the same time, in this way is easier to compare if two events were in the same "bucket" . I do the same with the end bucket. Aligning the buckets is important. Otherwise you can't compare different elements and you can't really see what is in "parallel".

0 Karma

SplunkTrust
SplunkTrust

nadid - my answer was already aligning the buckets, using the bin command. That was rounding all the bucket starts and ends to the same increments.

Also there is no need to assign the new times to "_time" to calculate concurrency, unless you want to use the concurrency command, and here we do not. Using _time instead of a more descriptive variable name makes the calculations harder to follow.

Your calculatoin for duration_bucket seems to be a little off, at least by my pencil and paper math. The way I was calculating it seems cleaner and without all the "if" statements. | eval duration_within_bucket=duration - duration_correction1 - duration_correction2

Lastly, I think I see what you're trying to do when you create the availability field, but taking the average of this field is a little bit unexpected based on your original question. This makes me think that maybe the reason you didn't like my answer was because I didn't really know what you are trying to get in the end.

My feeling is that maybe when you tried to rename all the fields in my answer to match your actual fields, one got missed somehow and you started changing structural parts around. I hope this helps.

0 Karma

New Member

The original post was very close, but was often giving me too few buckets.

For example, with 15s starting at time 2s, I want 4 buckets: [2,5), [5,10), [10,15), [15-17). The code as written gave me 3.

For my 1 minute buckets, the addition below resolved the problem.

| eval bucketstart=mvrange(**relativetime(starttime, "-0m@m")**,endtime,60)

0 Karma

Path Finder

Then we do the magic of mvrange with the new created variables. (Thanks for this command, is really useful!)
And after i expand the events to have one event for each bucket. Since i want to see the concurrent elements, I assign the new times to _time.
Now we have all the events ordered by _time that are aligned in buckets.
For each bucket now we calculate the duration of each event. Basically there we see how much is occupied the buckets by this event.
And finally i try to get the percentage of availability of the duration on the bucket. And finally for each range we do the avg or the max function of the availability and we fill the table with 1s.

0 Karma

Path Finder

What i get from this is, if you want to measure the concurrency of elements with a duration you should do:
* Align the buckets to a periods that you like

 |eval bucket_time_size=1800
 |eval start_bucket_time=START_TIME-(START_TIME%bucket_time_size)
 |eval end_bucket_time=END_TIME-(END_TIME%bucket_time_size)

Create the ranges in a variable of the events for the bucket size you defined

 |eval bucket_start=mvrange(start_bucket_time,end_bucket_time,bucket_time_size)

Expand the variable (and the events)

 |mvexpand bucket_start

Assign the _time to the new expanded field.

 |eval _time=bucket_start

With that you will have what are the concurrent elements in a certain frame of time. Calculating how much is really used in each bucket will be like the calculation of in the previous answer of duration_bucket.

0 Karma

Motivator

So you can calculate concurrency, but that's not exactly what you're asking for I think.

... | concurrency duration=duration start=start_time output=concurrent 

This will add a field to each event showing the number of concurrent events as of that events timestamp. You can then pipe that to timechart and see concurrency over time.

0 Karma

Path Finder

The concurrency function tells me how many concurrent elements are, but does not really allows me to check the duration of the concurrent elements. I've tried to play with it but i was not able to obtain anything suitable. In any case i really appreciate the answer 🙂

0 Karma

Builder

timestamp is missing in raw events.

0 Karma

Path Finder

Dear @satishdange,

I was assuming that one of the first commands would be |eval _time=start_time|timechart span=5s list(duration) as dur |where dur >0
In this way _time is defined.
Digging in answers.splunk.com I've found another question really similar http://answers.splunk.com/answers/122260/timechart-of-event-with-duration.html that ask something like : Restaurant have log on every seat when customer sit down and stands up. Every customer uses different time when they are eating. I want know how many customer we have in specific 15min timewindow in graph.
(at the end is really similar question).

0 Karma