Splunk Search

How can I get time span buckets to be relative to now?

Builder

I have a query like the following that I am using to trend the number of users active in an application during a given time period. I am trying to fill a single-value item in a dashboard, with trend.

index=test sourcetype=activity_log earliest=-30m latest=now | timechart span=15m distinct_count(username) as count | sort -_time

The problem is that if I run the search at say 11:35, I get results for the 15 minute segments 11:00-11:15, 11:15-11:30, and 11:30-11:45. The trend compares the most recent two blocks, but since the last block isn't complete (it's only 11:35 and my range is from 11:30-11:45), the trend analysis is off.

How can I get the span to bucket the results so that they are relative to now? i.e if I run my search at 11:35, how can I get my results to be in buckets from 11:05-11:20 and 11:20-11:35 so that I have two equal sized buckets for trend comparison?

1 Solution

Legend

You can't do bucket times relative to now() AFAIK, although there might be some clever way.

But you can tell timechart to eliminate partial buckets:

index=test sourcetype=activity_log earliest=-30m latest=now 
| timechart partial=f span=15m distinct_count(username) as count

View solution in original post

Engager

Hi,I had a situation to your issue and found a way to resolve what I needed.

I wanted to flag the amount of errors that occured in the past week within certain categories that exceeded 100. I wanted to compare this weeks number to last weeks number. The number is chosen based off of a time range picker used by the product owner at the top.

index="your_search" |eval early=$timerange.latest$-604800| eval late=$timerange.latest$ |where _time > early AND _time< late| | stats SUM(SQLERR_CNT) as Total by PGM_NM SSID _time | where Total > 100 |stats count AS 1Week | appendcols [search index="your_search" |eval early=$timerange.latest$-604800*2| eval late=$timerange.latest$ |where _time > early AND _time< late| | stats SUM(SQLERR_CNT) as Total by PGM_NM SSID _time | where Total > 100 |stats count AS 2Week]

by creating an early time used the time range token and subtracting the epoch time of the bucket. 604800 is the epoch time for 1 week. Then I appended a second search where I made the earliest time two weeks ago. and the latest time one week ago.

As far as putting this trend into a single value visualization. I have found the difference between the two numbers. and then displayed that number as a single value. Green if errors went down, red if they went up drastically! I am still working on how to best visually display my info.

0 Karma

Legend

You can't do bucket times relative to now() AFAIK, although there might be some clever way.

But you can tell timechart to eliminate partial buckets:

index=test sourcetype=activity_log earliest=-30m latest=now 
| timechart partial=f span=15m distinct_count(username) as count

View solution in original post