Is there a way to have a "sliding window" time span in a non real time search?

[amalober]

I need to get the amount of users per web product every 5 minutes, grouped by 15 second bins. However, I need those 15 seconds to overlap since I need a data bin every 5 seconds. So it would be like:

Seconds 1-15  product1    amount
Seconds 1-15  product2    amount
Seconds 1-15  product3    amount
Seconds 5-20  product1    amount
Seconds 5-20  product2    amount
Seconds 5-20  product3    amount
Seconds 10-25  product1    amount
Seconds 10-25  product2    amount
Seconds 10-25  product3    amount
...

I got the search for the 15 seconds bins but I cannot get around to overlap them (btw, I am really new to this, it took me some tome to get to this point):

query
|  bin  _time  span=15s 
| stats count by products _time '

I also tried timechart but couldn't find a way to add the products field to the results.
I appreciate any comments on this!

[somesoni2 · Accepted solution]

Give this a try (Splunk 6.4 or above)

your base search
| fields _time products
| bin _time span=5s
| streamstats time_window=15s min(_time) as start max(_time) as end count as amount by products
| dedup _time products 
| table _time products amount

[DalJeanis]

Be aware that your "end" is going to be the start time of the last 5 second bin, so if you want it to represent the end time of the overall 15-second window period, you'll need to adjust that.

Also, you have two periods at the beginning that have less than 15 seconds, so you'll want to throw them out, and the dedup is saving them rather than the later ones with the same start time.

The following code retains only full 15-second counts, and has a field "userpulse" that shows what the three 5-second pulses are that made up the total usercount. start and end are the start and end times of the 15-second triple-pulse. Any event happening exactly on the end boundary of the pulse will not be counted. (Events are counted if start <= _time < end.)

| makeresults count=1000 | eval timeago=(random()% 700) | eval _time=_time-timeago |eval products=if(random()%2=0,"Product1","Product2") | fields _time products 
| sort 0 _time 
| bin _time span=5s
| stats count as usercount by _time products  
| streamstats time_window=15s min(_time) as start,  max(_time) as end, sum(usercount) as usercount, count as pulsecount, list(usercount) as userpulse by products 
| where pulsecount=3
| eval _time=start, end=start+15
| fieldformat start=strftime(start, "%Y-%m-%d %H:%M:%S")
| fieldformat end=strftime(end, "%Y-%m-%d %H:%M:%S")

[amalober]

Thank you for your replies! Unfortunately, I'm on Splunk 6.2.9 so there's no time_window available.

[DalJeanis]

Then try this...

 | streamstats window=3 global=t min(_time) as start,  max(_time) as end, sum(usercount) as usercount, count as pulsecount, list(usercount) as userpulse by products 

[amalober]

Because of version limitation I had to unwrap the eval clause into two:

 '| eval _time=start | eval end=start+15 '