Dashboards & Visualizations

Time picker is not working in the dashboard since the base search has earliest and latest. How to substitute earliest and latest to add the time picker into dashboard?

iamsplunker31
Path Finder

We have a dashboard and wanted to add timepicker into this but it's not working since the following base search has earliest and latest it's hard coded. I'm wondering if we have any other way to add time picker by substituting the earliest and latest with something and also improve the speed of the dahboard ? Here is the query

index=foo sourcetype=xyz earliest=-0d@d latest=now| bin _time span=5m
| stats dc(ecn) as Current by _time
| appendcols [ search index=foo sourcetype=xyz earliest=-7d@d latest=-6d@d |eval _time=_time+60*60*24*7
| bin _time span=5m |stats dc(ecn) as LastWeek by _time ]
| appendcols [search index=foo sourcetype=xyz earliest=-14d@d latest=-13d@d

| bin _time span=5m |stats dc(ecn) as TwoWeeksAgo by _time]
| appendcols [search index=foo sourcetype=xyz earliest=-21d@d latest=-20d@d

| bin _time span=5m |stats dc(ecn) as ThreeWeeksAgo by _time]
| appendcols [search index=foo sourcetype=xyz earliest=-28d@d latest=-27d@d

| bin _time span=5m |stats dc(ecn) as FourWeeksAgo by _time]
| eval AvgOfFourWeeks=(LastWeek+TwoWeeksAgo+ThreeWeeksAgo+FourWeeksAgo)/4
| eval Diff1=LastWeek-Current | eval Diff2=AvgOfFourWeeks-Current
| eval Est_Impact= Diff1-Diff2
| fields _time Current LastWeek AvgOfFourWeeks Diff1 Diff2 Est_Impact

0 Karma
1 Solution

iamsplunker31
Path Finder

This one works fine when we change the xml code for time input

Dashboard Name

<input type="time" token="time_finder" searchWhenChanged="true">
  <label>Time Picker</label>
  <default>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </default>
  <change>
    <eval token="earliestTime">if(isstr(earliest), relative_time(now(),earliest), earliest)</eval>
    <eval token="latestTime">if(isstr(latest), relative_time(now(),latest), latest)</eval>
    <eval token="earliestTime1">relative_time(earliestTime,"-7d")</eval>
    <eval token="latestTime1">relative_time(latestTime,"-7d")</eval>
    <eval token="earliestTime2">relative_time(earliestTime,"-14d")</eval>
    <eval token="latestTime2">relative_time(latestTime,"-14d")</eval>
    <eval token="earliestTime3">relative_time(earliestTime,"-21d")</eval>
    <eval token="latestTime3">relative_time(latestTime,"-21d")</eval>
    <eval token="earliestTime4">relative_time(earliestTime,"-28d")</eval>
    <eval token="latestTime4">relative_time(latestTime,"-28d")</eval>
  </change>
</input>

View solution in original post

0 Karma

iamsplunker31
Path Finder

This one works fine when we change the xml code for time input

Dashboard Name

<input type="time" token="time_finder" searchWhenChanged="true">
  <label>Time Picker</label>
  <default>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </default>
  <change>
    <eval token="earliestTime">if(isstr(earliest), relative_time(now(),earliest), earliest)</eval>
    <eval token="latestTime">if(isstr(latest), relative_time(now(),latest), latest)</eval>
    <eval token="earliestTime1">relative_time(earliestTime,"-7d")</eval>
    <eval token="latestTime1">relative_time(latestTime,"-7d")</eval>
    <eval token="earliestTime2">relative_time(earliestTime,"-14d")</eval>
    <eval token="latestTime2">relative_time(latestTime,"-14d")</eval>
    <eval token="earliestTime3">relative_time(earliestTime,"-21d")</eval>
    <eval token="latestTime3">relative_time(latestTime,"-21d")</eval>
    <eval token="earliestTime4">relative_time(earliestTime,"-28d")</eval>
    <eval token="latestTime4">relative_time(latestTime,"-28d")</eval>
  </change>
</input>
0 Karma

woodcock
Esteemed Legend

If you need the particulars of the timepicker in your search, you can add | addinfo and reference info_min_time and info_max_time.

0 Karma

iamsplunker31
Path Finder

@woodcock : Is it possible to provide the xml code /example that you can share according to my previous comments of this question?
Thank you

0 Karma

woodcock
Esteemed Legend

I do not at all understand your need so I cannot help further unless you explain more clearly.

0 Karma

iamsplunker31
Path Finder

@woodcock : Does this info help?
I have a dashbaord using the following query . The dashboard should display results of current, last 7 days ,14 days ,21 days, and 28 days in one panel. That is the reason I used earliest ,latest and appendcol
On top of it I'm looking to add a time picker to select specific day/time say yesterday or 2 days ago also improve the speed of my dashboard Performance

index=foo sourcetype=xyz earliest=-0d@d latest=now| bin _time span=5m
| stats dc(ecn) as Current by _time
| appendcols [ search index=foo sourcetype=xyz earliest=-7d@d latest=-6d@d |eval _time=_time+60*60*24*7
| bin _time span=5m |stats dc(ecn) as LastWeek by _time ]
| appendcols [search index=foo sourcetype=xyz earliest=-14d@d latest=-13d@d
| bin _time span=5m |stats dc(ecn) as TwoWeeksAgo by _time]
| appendcols [search index=foo sourcetype=xyz earliest=-21d@d latest=-20d@d
| bin _time span=5m |stats dc(ecn) as ThreeWeeksAgo by _time]
| appendcols [search index=foo sourcetype=xyz earliest=-28d@d latest=-27d@d
| bin _time span=5m |stats dc(ecn) as FourWeeksAgo by _time]
| eval AvgOfFourWeeks_SameDay=(LastWeek+TwoWeeksAgo+ThreeWeeksAgo+FourWeeksAgo)/4
| eval Diff1=LastWeek-Current | eval Diff2=AvgOfFourWeeks-Current
| eval Est_Impact= Diff1-Diff2
| fields _time Current LastWeek AvgOfFourWeeks_SameDay Diff1 Diff2 Est_Impact

Thank you

0 Karma

woodcock
Esteemed Legend

Like this:

1: Remove the earliest= and latest= from the search SPL.
2: Click Edit on the GUI.
3: Click + Add Input and select Time.
4: Go to the panel that needs to be controlled by the Timepicker and click on Edit search (the magnifying glass icon).
5: In the Time Range setting, click on the options and select Shared Time Picker
6: Click Apply
7: Click Save
8: PROFIT!

0 Karma

iamsplunker31
Path Finder

Hi @woodcock, Thank you for your reply

The dashboard should display results of current, last 7 days ,14 days ,21 days, and 28 days in one panel. That is the reason I used earliest/latest and appendcol

On top of it I'm looking to add a time picker to select specific day/time say yesterday or 2 days ago
Thanks

0 Karma

aaronbarry73
Path Finder

Hi @iamsplunker31, you'll want to add a time picker for the user to be able to choose the day. Then you can explicitly reference the time picker using the tokens, but add relative time modifiers after each token as needed, like this.

<form>
  <label>Test Search</label>
  <fieldset submitButton="false">
    <input type="time" token="time">
      <label></label>
      <default>
        <earliest>-d@d</earliest>
        <latest>@d</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=foo sourcetype=xyz | bin _time span=5m | stats dc(ecn) as Current by _time 
          | appendcols [search index=foo sourcetype=xyz earliest=$time.earliest$-7d@d latest=$time.latest$-7d@d | bin _time span=5m | stats dc(ecn) as LastWeek by _time] 
          | appendcols [search index=foo sourcetype=xyz earliest=$time.earliest$-14d@d latest=$time.latest$-14d@d | bin _time span=5m | stats dc(ecn) as TwoWeeksAgo by _time] 
          | appendcols [search index=foo sourcetype=xyz earliest=$time.earliest$-21d@d latest=$time.latest$-21d@d | bin _time span=5m | stats dc(ecn) as ThreeWeeksAgo by _time] 
          | appendcols [search index=foo sourcetype=xyz earliest=$time.earliest$-28d@d latest=$time.latest$-28d@d | bin _time span=5m | stats dc(ecn) as FourWeeksAgo by _time] 
          | eval AvgOfFourWeeks=(LastWeek+TwoWeeksAgo+ThreeWeeksAgo+FourWeeksAgo)/4 
          | eval Diff1=LastWeek-Current | eval Diff2=AvgOfFourWeeks-Current 
          | eval Est_Impact= Diff1-Diff2</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

This is the simplest option. You could improve on the efficiency of this search by combining the appendcols into a single base search. However, you'd have to use streamstats or some other way to differentiate between the weeks so you can do math between them. I'll think on this one some more.

0 Karma

iamsplunker31
Path Finder

Hi @aaronbarry73 , Thank you for your reply.

The dashboard should display results of current, last 7 days ,14 days ,21 days, and 28 days in one panel. That is the reason I used earliest/latest and appendcol

On top of it I'm looking to add a time picker to select specific day/time say yesterday or 2 days ago. Initially I tried adding the token in the xml code like this.

$time.earliest$
$time.latest$

but it's not working. If we remove the earliest and latest in the base search we will not see the results for respective days.
I was wondering we need to convert the earliest/latest time into strp time in the search. I'm looking for modification of my query and improve my dashboard speed
Thanks

0 Karma

aaronbarry73
Path Finder

Hi @iamsplunker31, you're right, my answer was lacking. I made some edits to account for the different time ranges needed throughout the search. Does this help?

0 Karma

iamsplunker31
Path Finder

Hi @aaronbarry73 , I tried using the code above, while loading the dashboard it's throwing an error
Invalid Value "1570680000"-7d@d for time term latest

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