How to start a timechart on a dashboard on Monday without earliest and latest?

[ErikaE]

I have a timechart on a dashboard that sums Things by Description* with a span of a week. Since my first Thing event is on a Thursday, my week seems to run Thursday to Thursday on the timechart. If a different chart on the same dashboard has its first Thing event on a Tuesday, it will start on Tuesday.

I've looked at other questions that used earliest, and tried something like earliest=-3y@w1, which does snap everything to Monday. Unfortunately, it also plots a bunch of blank space to the left of all my data in the timechart. I can work around that by using chart instead of timechart:

source=* earliest=-3y@w1 | reverse | bin _time span=1w | eval time=strftime(_time, "%m-%d-%y") | chart sum(Thing) by time,Description

Which will work great for all time, but will not work if there is a time picker on the dash board, since the earliest in the search will override the time picker.

Also, the chart renders with white space between columns for Description, time combos where there are no Things. I'd like to get rid of that white space, without stacking the columns.

*I am only able to provide generalized examples of my data

[somesoni2 · Accepted solution]

If you want to snap your timerange from monday (@w1) but still want to respect the user selection via time range picker, try something like this (using a subsearch to generate earliest based on time range picker)

source=* [| gentimes start=-1 | addinfo | eval earliest=relative_time(info_min_time,"@w1") | table earliest ] | reverse | bin _time span=1w | eval time=strftime(_time, "%m-%d-%y") | chart sum(Thing) by time,Description

Where,
gentimes - just to add one single row placeholder
addinfo - adds timerange picker values (search timerange) in epoch to the search result. Main fields added is info_min_time(earliest) and info_max_time(latest)

Update
To handle all times

Since with all time, earliest will be 0 (epoch lowest value supported by Splunk ) i.e. Thu, 01 Jan 1970 GMT, relative time @w1 would not exist. Try this workaround for the same (to consider next monday Mon, 05 Jan 1970

source=* [| gentimes start=-1 | addinfo | eval info_min_time=if(info_min_time=0,604800,info_min_time)| eval earliest=relative_time(info_min_time,"@w1") | table earliest ] | reverse | bin _time span=1w | eval time=strftime(_time, "%m-%d-%y") | chart sum(Thing) by time,Description

[ErikaE]

The update does not appear to be working for all time. Previous month, previous year, etc still start weeks on Monday with the updated code.

I can't get the gentimes search to work on its own in a search, so I can't check the if statements. Any suggestions there? Do I need to debug in a dashboard panel?

[somesoni2]

You can run the search like this as regular search

| gentimes start=-1 | addinfo | eval info_min_time=if(info_min_time=0,43200,info_min_time)| eval earliest=relative_time(info_min_time,"@w1") 

[somesoni2]

I think the timezone of yours may be making the adjustment 43200 not sufficient enough to get to first monday. So I updated 43200 (5 days) with 604800 (7 days) to be sure. I tested and it works fine. Answer updated.

[ErikaE]

Thank you so much for your help. This now works great for chart visualizations, but does not work for timecharts. In that case, you'd be better off using a small window of time and earliest like @woodcock suggested, or his custom time picker code.

[woodcock]

I should have thought of that (addinfo + relative_time); very nice.

[ErikaE]

Works great, unless the selection is all time.

[somesoni2]

Try the updated answer to manage all times as well.

[woodcock]

The reason that earliest=-3y@w1 plots a bunch of blank space to the left of all your data in the timechart is because of the -3y part which means go back 3 years! Change it to something more reasonable like -1m@w1 which goes back 1 month and maybe that is all you need. The snap-to Monday part is the @w1 portion. Also, you surely don't need to use reverse.

[ErikaE]

This is exactly the problem -- I want to look at the history from an arbitrary start date to an arbitrary end date, or by relative time pickers like previous week, previous month, previous quarter. If I have something like -1m@w1 and my user picks previous quarter, they'll only see one month of data.

-3y is long but will ensure that I never mislead myself by overriding the time picker with something hidden under the hood in the search string.

[woodcock]

Why don't you skip the time-picker and have your own drop down with the values you like that maps to the first part of the earliest value? You could have:

<input type="dropdown" token="span_token">
  <label>Span Picker</label>
  <choice value="-1w">Last Week</choice>
  <choice value="-1mon">Last Month</choice>
  <choice value="-1q">Last Quarter</choice>
  <choice value="-1y">Last Year</choice>
  <default>Last Week</default>
</input>

Then you use earliest=$span_token$@w1 in your search.

[ErikaE]

This would go in the XML code for the dashboard?

[woodcock]

Yes, exactly.