Deployment Architecture

how to not overwrite time modifiers with time bins

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

woodcock
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

DalJeanis
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

jplumsdaine22
Influencer

Good question

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...