how to not overwrite time modifiers with time bins

[matthewb4]

Let's say it is currently 4:37 pm. I want to write a query that bins _time by 1 hour and where the last time bucket includes events between 3:37pm and the current time (4:37pm).

However, the bin command in the following query seems to overwrite the @m time modifier and makes all of the time buckets start at minute 0 (ie 2:00pm - 3:00pm, 3:00pm - 4:00pm, ...).

... ... ... earliest=-12h@m | bin _time span=1h | stats count by field

This makes the last bucket be 4:00pm - 5:00pm which means it only has 37 minutes worth of data while all other buckets have 60 minutes. I want to be able to compare counts between this last bucket and the previous buckets but it won't work if the time sample sizes aren't all the same. How can I change the query so that the last time bucket would be 3:47pm - 4:37pm? Any help is greatly appreciated.

[woodcock]

Just lie to Splunk about your time value, like this:

... earliest=-12h@m | eval _time = _time - (60 * tonumber(strftime(now(),"%M"))) | bin _time span=1h | stats count BY field

[DalJeanis]

Here's how I generated some pseudo-random test data -

| gentimes start="1/25/2017:13:59:21" increment=97m 
| append [| gentimes start="1/25/2017:13:28:52" increment=41m ]
| append [| gentimes start="1/25/2017:13:33:18" increment=17m ]
| eval _time = starttime
| table _time 
| sort 0 _time

Here's how I created bins starting with the minute of the lowest results. Just create a new field deltaTime with the time shifted by the minute value of the lowest results, then bin that, then raise up the bins by the same amount you lowered them.

| eventstats min(_time) as minTime
| eval minTime = relative_time(minTime,"@m")
| eval deltaTime = _time - minTime
| bin deltaTime as deltaBin span=1h
| eval deltaBin = deltaBin + minTime

| eval deltaBinF = strftime(deltaBin,"%Y-%m-%d %H:%M:%S")
| table _time deltaBin deltaBinF

The last two lines are just to present the results for review for testing.

[jplumsdaine22]

Good question