How can I do a timechart of sum(val) where events have start/end times?

[rettops]

I'm hoping that this is easy for someone with more Splunk-Fu than my meager amount.

The indexed data looks like the following. All times are Unix epoch
times (e.g. 1329105484) but to make it easier to read I'm using small
integers:

    event=A, startTime=1, endTime=6, val=7
    event=B, startTime=2, endTime=4, val=28
    event=C, startTime=3, endTime=5, val=32

I want to produce a timechart that looks something like this:

    time    sum(val)
    1       7
    2       35
    3       67
    4       67
    5       39
    6       7

That is, at time 1 only event A was between its start/end so the sum is just its val.
At time 2 both events A and B were between their start/end so the sum is A's val plus B's val.
and so on.

I have a nagging feeling that given the power of Splunk this should be
easy (and maybe even a common task) but I was unable to find any hints
on how to do it. I probably just don't know the right keywords to search.

Thanks!

[somesoni2 · Accepted solution]

Give this a try

your base search with fields _time start end value | eval sno=mvrange(0,1+(end-start)/300,1) | mvexpand sno | eval _time=start+sno*300 | timechart span=5m sum(value)

Adjust the multiplication factor '300' and span '5m' as per your need.

[rettops]

I thought I had, but I retried and it seems to be working just fine. Thanks!

[somesoni2]

Do you get proper data before "eval _time=..."? Means all the fields start,end,value,sno have values?

[rettops]

Your answer seems simpler than martin_mueller's, but I can't get it to work. I don't know Splunk well enough to tell what might be going wrong.

Simple csv:

start,end,value
1404115200,1404133200,7
1404118800,1404126000,28
1404122400,1404129600,32

Splunk chokes when it gets to 'eval _time=start+sno*300' with 'Job terminated unexpectedly'. Is something missing?

[martin_mueller]

Here's a thought, along with generated dummy data:

| gentimes start=-1 increment=4h | eval endtime = starttime + 11*3600 | rename starttime as _time | eval value = random()%100 | table _time endtime value
| eval temp = _time ." -". endtime | makemv temp | mvexpand temp
| eval value = if(temp < 0, -value, value) | eval _time = if(temp < 0, endtime, _time) | fields - temp
| sort + _time | accum value | timechart span=30m avg(value) as value | filldown value

The first bit generates data, the second creates a distinct "end" event, the third flips the value to negative if it's an "end" event and sets the timestamp to the end, the last bit accumulates the values (start) and negative values (end) and charts that. Looks like this:

[rettops]

Thanks, this seems to work well! Now to see how it performs across a few million events ...