How do I query relative times based on a query result?

[MonkeyK]

I have a dashboard that gives daily totals

1/1/17 500
1/2/17 332
1/3/17 509

I would like to be able to drill down to look at events making up the daily total. Is there a way for me to modify click.value to indicate the beginning and end of the day? what would I put in my earliest and latest values for the drill-down search?

[mpreddy · Accepted solution]

Hi Monkeyk,

create a timechart query with spans in panel A. and set the token in drilldown based on row(_time). write a sub search to calculate latest and earliest based on click event date.

The below sample from _internal Index will help you to understand further

<form>
  <label>SPLUNKANSWERS</label>
  <fieldset submitButton="false">
    <input type="time" token="field1">
      <label></label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index="_internal" |timechart count as total span=1d</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
        <set token="time">$row._time$ </set>

        </drilldown>
      </table>
    </panel>
  </row>
  <row depends="$time$">
    <panel>
      <event >
        <title>drilldown panel</title>
        <search>
          <query>index=_internal [|stats count |eval test="$time$"| eval earliest= strptime(test,"%Y-%m-%d") |eval latest=earliest+86400|return earliest,latest]</query>
           <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
      </event>
    </panel>
  </row>
</form>

[MonkeyK]

Thank you! I got away with something along the same lines but slightly more simple

      <drilldown target="_blank">
               <set token="latestTime">$click.value$</set>
               <eval token="latestTime">$click.value$+86400</eval>
               <link>
                 <![CDATA[/app/search/search?q=search%20[search
 terms]&earliest=$click.value$&latest=$latestTime$]]>
               </link>
             </drilldown>

[s2_splunk]

One approach would be to take the date from the clicked row and parse out day month and year and use it in your drilldown search as date_mday, date_month and date_year.
Or calculate the epoch for the clicked date at midnight and use as earliest= and add 86400 to that number and use as latest.
You may already have the correct start epoch value in the table cell for the date, not sure.