topic Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"? in Splunk Search

How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

goodsellt — Wed, 01 Jun 2016 13:55:04 GMT

Hello!

I've been playing around with the timechart command and spanning, however, there is an issue I'm having when I'm trying to use it to match a chart I'm defining with the last 7 days timespan.

I'm trying to have timechart span in such as way that its current period is the same as the last 7 days command, while it is able to go back X number of these periods to build a trend off of. I've been unable to find a combination of span variables and time offsets that matches the time snap of the last 7 days window.

Has anyone with experience doing something like this have a solution that allows you to more precisely define the time snapping?

Edit:
Apologies for the difficulty in understanding this question, I've included the below to help explain what I am getting at:

When I use timechart count span=7d, I receive the following output:

However, when I use time chart count span=1d, I receive the following output:

I've marked above in Red the dates which makeup 2016-06-04 in output with the larger span, and so on. What I'm looking for is a way to reverse that calculation, so that instead of starting at the 'earliest' date and making 7 day spans, I'd like it to start at the 'latest' date and make the spans going backwards.

I hope this has made it more clear!

Second edit:

I've attempted to do the double binning solution MuS provided, however the bins are still being created from earliest to latest, instead of latest to earliest which is my desired outcome.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

woodcock — Wed, 01 Jun 2016 14:38:56 GMT

What is wrong with span=7d?

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

gabriel_vasseur — Thu, 02 Jun 2016 12:21:59 GMT

Apologies but I've read your question twice but I still don't understand it... 😄 Can you maybe add screenshots and search commands and earliest/latests etc to explain what you get and what you want again?

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

goodsellt — Mon, 06 Jun 2016 22:42:36 GMT

I've updated the original post with some clarifications I hope should help.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

MuS — Mon, 06 Jun 2016 22:52:14 GMT

Hi goodsellt,

Splunk by default shows the earliest events at top like in this run everywhere command.
If you want to reverse the order simply add a |reverse or | sort - _time at the end of your search:

Hope this helps ...

cheers, MuS

Update:

Finally I understand what your question is all about 😉 So, if you want to such kind of time spanning, you must do some double bucketing of _time.

First you will need to get your event by day (For this run everywhere example I used hours but this can be easily changed to days)

 index=_internal kb=* kbps=* earliest=-28h@h latest=-1h@h | bin _time span=1h | stats sum(kb) AS perHour by _time

This will give you results per hour, note the latest in the base search - this is how to tell Splunk where to start the second _time bucketing. To get the second bucketing starting with the oldest event, we have to use reverse (not very efficient I know) and use the time chart against this event set

| reverse | timechart span=7h values(perHour) AS val_perHour sum(perHour) AS Total

This time we will use the span=7h to group events over 7 hours.

The final search looks like this:

index=_internal kb=* kbps=* earliest=-28h@h latest=-1h@h 
| bin _time span=1h 
| stats sum(kb) AS perHour by _time 
| reverse 
| timechart span=7h values(perHour) AS val_perHour sum(perHour) AS Total

And the result is this:

If you want to take this to the next level and have some kind of dynamic span based on the time range picker, check out this answer https://answers.splunk.com/answers/390574/how-to-create-a-search-that-shows-a-trending-value.html

cheers, MuS

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

woodcock — Tue, 07 Jun 2016 01:41:54 GMT

I see, so the fundamental question is this:

How does Splunk select the endpoints for any window (e.g. which day will be `day 1` and which day will be `day 7` when `span=7d` is specified) and how can one influence this to be different.

This is a good question.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

gabriel_vasseur — Tue, 07 Jun 2016 07:19:36 GMT

Thanks that's much clearer! Interesting question.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

gabriel_vasseur — Tue, 07 Jun 2016 07:23:21 GMT

It would be interesting indeed to know how one can influence the alignment of span. I have monthly graphs with a span of 4h that used to start on the 1st of the month at 0:00, but now that we moved to British Summer Time (GMT+1) they start on the last day of the previous month at 21:00!

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

woodcock — Tue, 07 Jun 2016 12:27:00 GMT

In your case, you can control this with your user Time zone setting.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

goodsellt — Tue, 07 Jun 2016 13:02:46 GMT

Thanks for the response!

However, this only changes the order it is displayed in, versus my desired goal of reversing the order the spans are generated and calculated.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

MuS — Tue, 07 Jun 2016 21:35:59 GMT

aaaah, now I get it 🙂 took me some time to understand it but I think I have the answer somewhere .... pls wait.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

MuS — Wed, 08 Jun 2016 02:40:03 GMT

Update ping, see my answer to get some ideas on how this can be done - cheers, MuS

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

goodsellt — Wed, 15 Jun 2016 20:47:26 GMT

Hello! I tried out the solution you provided a few ways, however it does not appear that double binning the time affects the starting points for any of the bins. I'm still continuing to have the bins only form from earliest to latest.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

MuS — Sun, 19 Jun 2016 21:52:25 GMT

@goodsellt, maybe you should check out the start-end option of the bin command:

<start-end>
Syntax: start=<num> | end=<num>
Description: Sets the minimum and maximum extents for numerical bins. The data in the field is analyzed and the beginning and ending values are determined. The start and end arguments are used when a span value is not specified.
You can use the start or end arguments only to expand the range, not to shorten the range. For example, if the field represents seconds the values are from 0-59. If you specify a span of 10, then the bins are calculated in increments of 10. The bins are 0-9, 10-19, 20-29, and so forth. If you do not specify a span, but specify end=1000, the bins are calculated based on the actual beginning value and 1000 as the end value.
If you set end=10 and the values are >10, the end argument has no effect.

http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Bin#Bin_options

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

josephinemho — Mon, 22 Jan 2018 23:06:29 GMT

Hi, I'm also having a similar problem.

I'm trying to compare count from the past 7 days vs. count from the past 7 days (7 days ago). However, when I look at the totals and compare it with the per day count, it doesn't add up.

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

cgodon — Tue, 10 Sep 2019 14:37:10 GMT

hello,
I have exactly the same need.
Did someone finally come up with a solution ?
Thanks,
Cyril

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

k_malik — Thu, 19 Sep 2019 15:00:56 GMT

I am having the same concern

Re: How do I modify the span for timechart to match a chart I'm defining with a time range of "last 7 days"?

ITWhisperer — Wed, 19 Aug 2020 15:14:43 GMT

I faced a similar issue and resolved it by creating an "inverse" timestamp and using that to bucket events on instead of _time. For example:

... \| eval repoch=relative_time(now(),"+0d@d-1d")	Pick a latest time (I was interested in values upto and including yesterday)
\| eval retime=repoch-_time	Calculate time prior to your new epoch
\| bin retime span=7d	Set bin based on new time (this gets 7 days prior going backwards)
\| stats dc(_time) as days, sum(count) as count by Column, retime	I was interest in counts over 7 days.
\| where days = 7 \| ...	I then only took complete weeks going forward

Obviously the date can be recovered by subtracting from repoch again if you need it.