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