Splunk Search

Generate timechart from one event with known duration

Explorer

I have events where I know what the _time is(obviously). _time lets me know the end of the event. I also have data for the duration of these events(in seconds).

e.g.
_time=2019-04-10T23:59:59.973+02:00 (in epoch time 1554933599 if I take the milliseconds out)
duration=7198 (almost 2 hours)
So I can calculate when the event started by subtracting the duration from the epoch time value (which would be 1554926401 without the milliseconds)

I want to create a timechart with 15 minute intervals where I add 1 to the total if the event was occurring at that moment.
For this case it would be:

_time                        exists
2019-04-10 21:00:00          0
2019-04-10 21:15:00          0
2019-04-10 21:30:00          0
2019-04-10 21:45:00          0
2019-04-10 22:00:00          1
2019-04-10 22:15:00          1
2019-04-10 22:30:00          1
2019-04-10 22:45:00          1
2019-04-10 23:00:00          1
2019-04-10 23:15:00          1
2019-04-10 23:30:00          1
2019-04-10 23:45:00          1
2019-04-11 00:00:00          0
2019-04-11 00:15:00          0

I'm at a loss as to how I should go about generating such results from a single event. I want to generate data for each event in this manner, and finally find sum(exists) by _time.

My final result should look like this:

_time                        sum(exists)
2019-04-10 21:00:00          454
2019-04-10 21:15:00          497
2019-04-10 21:30:00          552
2019-04-10 21:45:00          547
2019-04-10 22:00:00          686
2019-04-10 22:15:00          891
2019-04-10 22:30:00          903
2019-04-10 22:45:00          906
2019-04-10 23:00:00          815
2019-04-10 23:15:00          725
2019-04-10 23:30:00          677
2019-04-10 23:45:00          605
2019-04-11 00:00:00          568
2019-04-11 00:15:00          474

Any idea would be appreciated. Thank you for your time!

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this a try (900 is for 15 minute bucketing)

your current search with fields _time and duration (seconds)
| eval duration_range=mvrange(0, duration + duration%900,900)
| mvexpand duration_range 
| eval _time=_time-duration_range
| timechart span=15 count

View solution in original post

SplunkTrust
SplunkTrust

Give this a try (900 is for 15 minute bucketing)

your current search with fields _time and duration (seconds)
| eval duration_range=mvrange(0, duration + duration%900,900)
| mvexpand duration_range 
| eval _time=_time-duration_range
| timechart span=15 count

View solution in original post

Explorer

Yesssss, that did it!

mvrange and mvexpand were the commands I was not familiar with, but I see how they do exactly what I wanted to do.

Only thing I needed to change was span=15min, since only 15 is for seconds.

Thank you very much for your help, cheers!

0 Karma