topic How to filter a dashboard search based on a date field that is not the default _time field? in Splunk Search

How to filter a dashboard search based on a date field that is not the default _time field?

jwch — Tue, 29 Sep 2020 09:18:05 GMT

Hello,

I am having trouble setting up a dashboard to filter based on a date field which isn't the default _time field.

{{search....}} | eval _time = strptime(CreatedDate, "%Y-%m-%d %H:%M:%S.%3N") | table _time CreatedDate DER_LastUpdated | sort CreatedDate asc

I've tried to use eval to create a new field which would act as the default time dimension for the table, which looks like it works as the output table correctly shows _time value as equal to the CreatedDate value. The problem, however, is when I place this search on a dashboard with a time input that continues to filter based on the DER_LastUpdated field rather than the new _time field. My question becomes, how can I filter tables, etc. on a dashboard on time values which aren't the timestamp values stipulated when creating a dbinput?

Re: How to filter a dashboard search based on a date field that is not the default _time field?

lguinn2 — Mon, 04 Apr 2016 20:43:11 GMT

I don't think you can do what you want this way. When you edit _time using eval - the search is already complete. The time range picker only applies to the first search in the pipeline.

AFAIK, the only way to do what you want is:

yoursearchhere earliest=-TTT 
| eval cDate = strptime(CreatedDate, "%Y-%m-%d %H:%M:%S.%3N") 
| where cDate >= realEarliestTime AND cDate <= realLatestTime

In the initial search, you must search across a timerange that is guaranteed to pick up everything that you might want - so "TTT" may represent a long time.

I wasn't sure where/how you are getting the start and end times that you want to use, so I just used "realEarliestTime" and "realLatestTime" for the example

Re: How to filter a dashboard search based on a date field that is not the default _time field?

jwch — Tue, 05 Apr 2016 09:23:11 GMT

Thanks very much, Iguinn!

I understand the search function a bit better thanks to your post. I've followed your steps (I think) and have am now met with a new error in the where command, specifically: "Error in 'where' command: the expression is malformed. A comparison operator is missing"

I think the where command is pretty simply stating the eval field cDate needs to exist between two token fields which are used in the dashboard input, but can you spot the issue with the below xml?

<input type="time" token="_time_tok" searchWhenChanged="true">
  <label></label>
  <default>
    <earliest>0</earliest>
    <latest></latest>
  </default>
</input>


<panel>
  <table>
    <title>test</title>
    <search>
      <query>index=... sourcetype=... earliest=-6mon@ | eval cDate = strftime(strptime(CreatedDate, "%Y-%m-d %H:%M:%S.%3N"), "%Y-%m-%d %H:%M:%S.%3N") | where cDate &gt;= $_time_tok.earliest$ AND cDate &lt;= $_time_tok.latest$
      <earliest>$_time_tok.earliest$</earliest>
      <latest>$_time_tok.latest$</latest>
    </search>

Re: How to filter a dashboard search based on a date field that is not the default _time field?

lguinn2 — Wed, 06 Apr 2016 19:15:07 GMT

Yes, tokens (like $_time_tok.earliest$ ) are replaced with simple substitution. The tokens are "-1d", etc. not actual timestamps.

So this should work:

<query>index=... sourcetype=... earliest=-6mon@ 
| eval cDate =strptime(CreatedDate, "%Y-%m-d %H:%M:%S.%3N")
| eval earliest = relative_time(now(),"$_time_tok.earliest$")
| eval latest=  relative_time(now(),"$_time_tok.latest$")
| where cDate &gt;= earliest AND cDate &lt;= latest
</query>

Re: How to filter a dashboard search based on a date field that is not the default _time field?

Yunagi — Mon, 30 Apr 2018 10:35:25 GMT

I had the same issue.

This blog post by Splunk helped me: I can’t make my time range picker pick my time field.