Archive
Highlighted

how to not overwrite time modifiers with time bins

Path Finder

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.

Highlighted

Re: how to not overwrite time modifiers with time bins

Influencer

Good question

0 Karma
Highlighted

Re: how to not overwrite time modifiers with time bins

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: how to not overwrite time modifiers with time bins

Esteemed Legend

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
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.