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.
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.