How to manipulate a timestamp token from a (timestamp) input box

[sarge338]

I have a dashboard in which the customer can enter a start date/time, end date/time, and a string. I then use the information entered to query two different (not Splunk) databases to find/present records which contain the string entered which occur between the data/time entries.

The issue I am having is that the two databases store the timestamps in different formats. So, regardless of what format I have the user enter the date/time, it does not agree with one of the two systems.

So, how can I a date/timestamp that is valid for one system (2017-11-15 16:22:30), and convert it to a new token that is valid for the second system (11-15-17 16:22:30)?

I attempted to use the following, but it appears I have no idea where "eval" can be used. 🙂

<eval token='$conv_startTime_token$'>strptime($startTime_token$,%m-%d-%y %h:%M:%S)"</eval>

Thank you!

[maciep]

I wanted to throw this out there in case it might be helpful. We recently started to utilize the timepicker for database queries on some of our dashboards.

-Create a time picker
-Create a search tied to the time picker
-Use addinfo in the search to get the earliest/latest timestamps from timepicker
-Use spl to create fields in the search that represent those earliest/latest times in the formats needed
-After search runs, set tokens to those earliest/latest fields
-In the time picker, unset those tokens on change
-In you database query use those tokens

That way as users change the time picker, the tokens will be updated and your query will rerun. Seems to work ok for us. And so you could have the search create multiple earliest/latest fields formatted differently.

<form>
  <search id="sql_date_boundaries">
    <query>
      | stats count | addinfo | eval my_latest = if(info_max_time = "+Infinity",now(),info_max_time) | eval my_earliest = info_min_time | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(my_earliest) ctime(my_latest) | fields my_earliest my_latest
    </query>
    <earliest>$t_time.earliest$</earliest>
    <latest>$t_time.latest$</latest>
    <finalized>
      <set token="sql_earliest">$result.my_earliest$</set>
      <set token="sql_latest">$result.my_latest$</set>
    </finalized>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="t_time">
      <label>Closed Tickets Time Picker:</label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
      <change>
        <unset token="sql_earliest"></unset>
        <unset token="sql.latest"></unset>
      </change>
    </input>
  </fieldset>
  .
  .
  .
  <panel>
    <chart>
      <search>
        <query>| dbquery <database> "select <fields> from <table> where ClosedDateTime >= convert(datetime,'$sql_earliest$',120) and ClosedDateTime < convert(datetime,'$sql_latest$',120)" </query>
      </search>
    </chart>
   </panel>
  .
  .
  .
</form>

[cmerriman · Accepted solution]

try using this in your input:

<input type="text" token="startTime_token">
       <label>Start Date (YYYY-MM-DD HH:MI:SS)</label>
        <change>
            <eval token="conv_startTime_token">strftime(strptime($value$,"%Y-%m-%d %H:%M:%S"),"%m-%d-%y %H:%M:%S")</eval>
        </change>
     </input>
     <input type="text" token="endTime_token">
       <label>End Date (YYYY-MM-DD HH:MI:SS)</label>
     </input>

[sarge338]

Thank you, cmerriman. This was the easiest answer/solution to implement. I greatly appreciate the assistance.

[anjambha]

Hello,

Hope this will help you..

Dashboard.xml :

<form script="timepic.js">
  <label>Test</label>
  <fieldset submitButton="false" autoRun="false">
    <input id="input1" type="time" token="time1" searchWhenChanged="true">
      <label>Time</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index="_internal" | eval earliest_DB1 = strftime($time1.earliest$, "%Y-%m-%d %H:%M:%S") | eval latest_DB1 = strftime($time1.latest$, "%Y-%m-%d %H:%M:%S") | eval earliest_DB2 = strftime($time1.earliest$, "%m-%d-%y %H:%M:%S") | eval latest_DB2 = strftime($time1.latest$, "%m-%d-%y %H:%M:%S") | table earliest_DB1 latest_DB1 earliest_DB2 latest_DB2</query>
          <earliest>$time1.earliest$</earliest>
          <latest>$time1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

timepic.js (/opt/splunk/etc/apps/<app name>/appserver/static)

require([
 'underscore',
 'jquery',
 'splunkjs/mvc',
 'splunkjs/mvc/simplexml/ready!'], function(_, $, mvc){
 // Timerange Test
 var mypresetsettings = {
     showCustomDateTime: true,
     showPresets: false,
     showCustomRealTime: false,
     showCustomAdvanced: false,
     showCustomRelative: false,
     showCustomDate: false
 };

 // Show only the date and time submenue of the timerangepicker
 var timeRangePicker = mvc.Components.getInstance("input1");
 timeRangePicker.options.dialogOptions = mypresetsettings;
 console.log(timeRangePicker.options);
 mvc.Components.getInstance("input1").render();
 });

if it works, accept and upvote it

[cmerriman]

Can you give the xml code for your inputs? Are they text?
You should be able to do something like

<done>
<eval token="conv_startTime_token">strftime(strptime($startTime_token$,"%Y-%m-%d %H:%M:%S"),"%m-%d-%y %h:%M:%S")</eval>
</done>

Inside the input, so long that it’s always in that format.

[sarge338]

Hello cmerriman,

Yes, they are text. The code is below...

<input type="text" token="startTime_token">
      <label>Start Date (YYYY-MM-DD HH:MI:SS)</label>
    </input>
    <input type="text" token="endTime_token">
      <label>End Date (YYYY-MM-DD HH:MI:SS)</label>
    </input>