Splunk Search

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

ErikaE
Communicator

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

1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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
Communicator

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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") 
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

ErikaE
Communicator

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

ErikaE
Communicator

Works great, unless the selection is all time.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer to manage all times as well.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

ErikaE
Communicator

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

ErikaE
Communicator

This would go in the XML code for the dashboard?

0 Karma

woodcock
Esteemed Legend

Yes, exactly.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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