How to work with timepicker in splunk using Database as back end.?

[SanthoshSreshta]

Hi Splunkers,

I am unable to understand how to add timepicker in dashboards and reports by using DB as back end.
I have column name TransDate in my SQL Server DB.
i have added panel "Time" in the dashboard and in the query I have added where condition as

| dbquery "Centro" "SELECT * FROM Centro_Customer_Visit WHERE TransDate='$Token1$'"

it is displaying nothing. Can anyone help me out on how to work on Timepicker with DB in splunk 😞

Thanks,
Santhosh

screenshot for the requirement

[jeyashini]

Any update on this . I have similar requirement

[sanjay_shrestha]

Santosh,

Here is how I have done it (for Oracle database). It is little tricky though :-).

When we use TimePicker in dashboard, it returns time range (info_max_time & info_min_time). So you need to change your query to use both upper and lower range. You need to use "addinfo" command to get info_max_time & info_min_time. Once you have those 2 fields, you can analyse the return values with TimePicker selection.

e.g. info_max_time would have "+Infinity", when we select "All time". Both info_max_time & info_min_time would return epoch time (Unix time number / counts every seconds on and after 1 January 1970 ) for other selection.

So you need to construct your query in such a way that if you have All time selected then not to apply date criteria. For this reason, you need to construct your query dynamically.

Let's take your query (removed few fields for simplicity):

<query>| dbquery "CENTRO" "SELECT  ShowRoomCode   FROM  Centro_Customer_Visit  WHERE  TransDate='$TOKEN2$'"</query>

and construct dynamically:

<query>|dbquery "CENTRO" [search index=main| head 1 | addinfo | eval query=IF(info_max_time="+Infinity","SELECT  ShowRoomCode   FROM  Centro_Customer_Visit","SELECT  ShowRoomCode   FROM  Centro_Customer_Visit WHERE  TransDate between (sysdate - ( (((sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy')) * 86400) - ".info_min_time.")/86400))  and  (sysdate - ( (((sysdate - to_date('01-Jan-1970', 'dd-Mon-yyyy')) * 86400) - ".info_max_time.")/86400))")|fields query|format "" "" "" "" "" ""]</query>

You might need to use appropriate command in MS SQL SERVER (may be getDate() ) instead of sysdate in Oracle).

I hope this will help. Let me know.

Thanks,
Sanjay

[bnorthway]

Have you followed the documentation here? Note that you will need to change your outer tags from to`. You also need to define a token for your input.

It would be very helpful if you could update your question with the XML for your dashboard.

[SanthoshSreshta]

<form>
  <fieldset submitButton="false" autoRun="true">
    <input type="dropdown" token="TOKEN1" searchWhenChanged="true">
      <label>field1</label>
      <choice value="*">ALL</choice>
      <search>
        <query>| dbquery "CENTRO" "SELECT  ShowRoomCode , TransDate  , Num_of_Customers  FROM  Centro_Customer_Visit "</query>
      </search>
      <fieldForLabel>Lable</fieldForLabel>
      <fieldForValue>ShowRoomCode</fieldForValue>
    </input>
    <input type="time" token="TOKEN2" searchWhenChanged="true">
      <label>Select TransDate</label>
      <default>
        <earliest>-1y@y</earliest>
        <latest>@y</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| dbquery "CENTRO" "SELECT  ShowRoomCode , TransDate  , Num_of_Customers  FROM  Centro_Customer_Visit   WHERE ShowRoomCode='$TOKEN1$' AND TransDate='$TOKEN2$'"</query>
          <earliest>1388514600</earliest>
          <latest>1420050600</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">cell</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
</form>

please mention if any more data to be posted

[bnorthway]

the code in your input tag is significantly different from the documentation example I linked. I suggest you follow the documentation, then modify the example to your needs after you have got the example working.

[jeffland]

What did you put into the token, and what is expected there?
To see the token value, you can simply put it in the title of a panel.

[SanthoshSreshta]

Hi @jeffland
firstly i have declared a selector (Dropdown) by using add input->time then there by I want to get the data based on time selection(TransDate).
I have attached the screenshot to my Question. please refer there.

[jeffland]

I was trying to clarify where

$Token1$

comes from, what it contains. You posted that in a comment to an answer below, so I see that now.

Two things, first: Token1 is not a time token so it doesn't make sense to use it in a WHERE clause of dbconnect with a field that expects a date (using Token2 there seems more appropriate; maybe you already do this and the question above was from a time before you had the second input on your page).
Second, what do the values for that TransDate in your database look like? Using a token from the time range picker will yield an object with two fields, earliest and latest, each containing the value the selection in your time range picker results in (i.e. -15m and now for the preset "Last 15 Minutes"). You'll have to adjust your token use and/or your time range picker.

[SanthoshSreshta]

Hi @jeffland
The values for the TransDate looks like: 2011-03-29.
Are you saying that we cant use token concept for DB. then can you please guide me in the requirement.
I want to restrict the table based on the time selection. ( Time picker ).

We had just started doing work on splunk before 2 months. please be brief.

Thanks in advance,
santhosh

[jeffland]

To clarify things for you, maybe you should have a look at what a time token looks like. Insert the following code to your XML, it should show you how time tokens work:

<input type="time" token="time_t">
  <label>time_t</label>
</input>
<row>
  <panel>
    <title>time_t is $time_t$, time_t.earliest is $time_t.earliest$, time_t.latest is $time_t.latest$</title>
  </panel>
</row>

You should see that your token itself does not display. (You can display it in javascript to see that it is an object, and you access the content of the object with .earliest and .latest.) These two contain either a relative time modifier (-15m), now, or an epoch timestamp.
So, neither of these looks at all like a date in the format of %Y-%m-%d, and thus the search does not produce any result. You can follow Sanjay's advice below, or you could do it similarly with an eval and strftime and insert that field in your WHERE clause.