Convert Date Timestamp in Lookup for Drill-down

[smullins]

I have a dashboard that queries a Lookup file. The Lookup file contains a column containing Date Timestamps in this format DD/MM/YY. The column name in the Lookup is Date. It is called "Date (DD/MM/YY)" in the dashboard statistics panel.

I am converting that DD/MM/YY string to Unix time in the drill-down using something like this:

| eval unixtime=strptime('Date',"%d/%m/%y")

Which gives results like this:

Date unixtime
06/02/20 1580947200.000000

1580947200.000000
Is equivalent to:
02/06/2020 @ 12:00am (UTC)

That's a good start, but I want the drill-down search to search that entire 24 hour period. So all of 06/02/20, 24 hours.

Something like this seems like it would work.

<eval token="earliest">strptime($row."Date (DD/MM/YY)"$,"%d/%m/%y")</eval>
<eval token="latest">strptime($row."Date (DD/MM/YY)"$,"%d/%m/%y")+86400</eval>

86400 being the number of seconds in a day.

But I can't quite get it working. Can anyone point me in the right direction?

[smullins · Accepted solution]

So there were several things I was doing wrong. First off earliest and latest are reserved names so you cannot use them for your own custom time variables. Secondly, I have my Date timestamp in the far Left column so $click.value$ works better than trying to shove my Column name in there which contains all kinds of spaces and special characters. Finally, to get the time stamps to be Midnight to Midnight I had to do some simple math to alter the Unix time stamp accordingly because, for whatever reason, the system was giving me essentially 11 PM to 11 PM on the date clicked whereas I want Midnight on the date click to Midnight on the next day.

   <eval token="earliestTime">strptime($click.value$,"%d/%m/%y")+3600</eval>
   <eval token="latestTime">strptime($click.value$,"%d/%m/%y")+90000</eval>

[richgalloway]

How about this?

<eval token="earliest">strptime($row."Date (DD/MM/YY)"$."00:00", "%d/%m/%y %H:%M")</eval>
<eval token="latest">strptime($row."Date (DD/MM/YY)"$."23:59", "%d/%m/%y %H:%M")</eval>

[smullins]

The conversion and math isn't the problem, it's successfully passing the resulting tokens to the drill-down search.

Either the token variables don't resolve to values or the drill-down search just uses the time picker default (now - 24 hours).

I think the basic eval you gave or mine are equally valid, except you would no longer need the +86400 in your example. The problem is getting this to pass into the drill-down custom search.

Thanks!

Edit: I was trying to use earliest and latest as variable names as you can see but I believe those are reserved by the Splunk system so they would never be overwritten by the eval as expected.