Dashboards & Visualizations
Highlighted

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

Builder

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?

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

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 datemday, datemonth 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.

Highlighted

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

Communicator

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>

View solution in original post

Highlighted

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

Builder

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>