Can you help me date filter in a dashboard but not with _time field?

[imurpalvicky]

Hi Team,

I have a field called as "completed date time" in the format (2018-10-30 06:09:60). In my dashboard, I need the DATE TIME FILTER to apply filtering based on this field rather than the event time stamp (_time).

Appreciate your help on this.

[mstjohn_splunk]

hi @imurpalvicky,

Did the answer below solve your problem? If so, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

[niketn · Accepted solution]

@imurpalvicky If the default event time extracted while indexing the data is not based on completed date time of your events then there will be a performance impact in your dashboard as Time is one of most efficient filters that can be applied in your dashboard to restrict only required events and improve performance.

In your case you can try the following approach. Since the Completion date field is in YYYY-mm-dd HH:MM:SS format you can apply string time filter on the date field to filter required results. You would however need a broader time range still to be applied on the events (through <earliest> and <latest> so that your search does not run for All Time data).

Use an independent search to get the earliest and latest epoch time from the time picker input. Then use addinfo command to get the earliest and latest epoch time in SPL as info_min_time and info_max_time respectively. Use SPL to convert to String Time format as per the values in the Completed Date Time field. Use the Search Event Handler like <done> to set the required String Earliest and Latest time tokens. Use the tokens in the actual search to filter events falling between string earliest and latest time.

Please try the following run anywhere example which creates 3 dummy events with completed date as 2018/10,2018/11 and 2018/12 respectively.

<form>
  <label>Change Time Field</label>
  <!-- Independent search to set filter for completed date time -->
  <search>
    <query>| makeresults
| addinfo
| eval earliestTimeString=strftime(info_min_time,"%Y-%m-%d %H:%M:%S"), latestTimeString=strftime(info_max_time,"%Y-%m-%d %H:%M:%S")
    </query>
    <earliest>$tokTime.earliest$</earliest>
    <latest>$tokTime.latest$</latest>
    <done>
      <set token="tokCompletedDateTimeStringEarliest">$result.earliestTimeString$</set>
      <set token="tokCompletedDateTimeStringLatest">$result.latestTimeString$</set>
    </done>
  </search>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="time" token="tokTime" searchWhenChanged="true">
        <label></label>
        <default>
          <earliest>-30d@d</earliest>
          <latest>now</latest>
        </default>
      </input>
      <table>
        <search>
          <query>| makeresults annotate=true
| eval _raw="\"completed_date_time\"=\"2018-10-30 06:09:60\";"
| append [| makeresults annotate=true
| eval _raw="\"completed_date_time\"=\"2018-11-30 06:09:60\";"]
| append [| makeresults annotate=true
| eval _raw="\"completed_date_time\"=\"2018-12-20 06:09:60\";"]
| extract kvdelim="=" pairdelim=";"
| where completed_date_time>="$tokCompletedDateTimeStringEarliest$" AND completed_date_time<"$tokCompletedDateTimeStringLatest$" </query>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

PS: As stated earlier while this will work performance would be impacted as time picker input filter is not restricting the events hence you would be pulling all the events from index and then filtering out unwanted events.