How to add and subtract time in earliest and latest using parameters in my panel search?

[sridharreddy]

Hi Splunkers,

Requirement:

I have a line graph in PANEL1. If I click at a point on a line graph it will display another panel with the clicked time range.

Here is the thing I need. When you click at a point in the line graph in (PANEL1), the below graph (PANEL2) should display the data for LATEST+30MIN and EARLIEST -30 MIN for the above clicked line graph.

I tried some thing like this in the panel2 query, but it's not working:

index=_internal sourcetype=splunkd earliest = $earliest_dd$-30m latest = $latest_dd$+30

Working code:

TEST

<input type="time" token="field1" searchWhenChanged="true">
  <label></label>
  <default>
    <earliest>@d</earliest>
    <latest>now</latest>
  </default>
</input>


<panel>
  <chart>
    <title>PANEL1</title>
    <search>
      <query>index=_internal|timechart count as sourcetype</query>
      <earliest>$field1.earliest$</earliest>
      <latest>$field1.latest$</latest>
    </search>
    <option name="charting.chart">line</option>
    <option name="charting.axisY2.enabled">false</option>
    <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
    <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
    <option name="charting.axisTitleX.visibility">visible</option>
    <option name="charting.axisTitleY.visibility">visible</option>
    <option name="charting.axisTitleY2.visibility">visible</option>
    <option name="charting.axisX.scale">linear</option>
    <option name="charting.axisY.scale">linear</option>
    <option name="charting.axisY2.scale">inherit</option>
    <option name="charting.chart.bubbleMaximumSize">50</option>
    <option name="charting.chart.bubbleMinimumSize">10</option>
    <option name="charting.chart.bubbleSizeBy">area</option>
    <option name="charting.chart.nullValueMode">gaps</option>
    <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
    <option name="charting.chart.stackMode">default</option>
    <option name="charting.chart.style">shiny</option>
    <option name="charting.drilldown">all</option>
    <option name="charting.layout.splitSeries">0</option>
    <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
    <option name="charting.legend.placement">right</option>
    <option name="charting.axisTitleY.text">Count</option>
    <option name="charting.axisTitleX.text">Time</option>
    <drilldown>
      <set token="earliest_dd">$earliest$</set>
      <set token="latest_dd">$latest$</set>
    </drilldown>
  </chart>
</panel>
<panel depends="$earliest_dd$">
  <table>
    <title>PANEL2</title>
    <search>
      <query>index=_internal sourcetype=splunkd earliest = $earliest_dd$ latest = $latest_dd$
      </query>
      <earliest>$field1.earliest$</earliest>
      <latest>$field1.latest$</latest>
    </search>
    <option name="wrap">undefined</option>
    <option name="rowNumbers">undefined</option>
    <option name="drilldown">row</option>
    <option name="charting.legend.placement">right</option>
    <option name="dataOverlayMode">none</option>
    <option name="count">10</option>
  </table>
</panel>

[sundareshr]

Try this using relative_time

<input type="time" token="field1" searchWhenChanged="true">
   <label></label>
   <default>
     <earliest>@d</earliest>
     <latest>now</latest>
   </default>
 </input>    

 <panel>
   <chart>
     <title>PANEL1</title>
     <search>
       <query>index=_internal|timechart count as sourcetype</query>
       <earliest>$field1.earliest$</earliest>
       <latest>$field1.latest$</latest>
     </search>
     <option name="charting.chart">line</option>
     <option name="charting.axisY2.enabled">false</option>
     <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
     <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
     <option name="charting.axisTitleX.visibility">visible</option>
     <option name="charting.axisTitleY.visibility">visible</option>
     <option name="charting.axisTitleY2.visibility">visible</option>
     <option name="charting.axisX.scale">linear</option>
     <option name="charting.axisY.scale">linear</option>
     <option name="charting.axisY2.scale">inherit</option>
     <option name="charting.chart.bubbleMaximumSize">50</option>
     <option name="charting.chart.bubbleMinimumSize">10</option>
     <option name="charting.chart.bubbleSizeBy">area</option>
     <option name="charting.chart.nullValueMode">gaps</option>
     <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
     <option name="charting.chart.stackMode">default</option>
     <option name="charting.chart.style">shiny</option>
     <option name="charting.drilldown">all</option>
     <option name="charting.layout.splitSeries">0</option>
     <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
     <option name="charting.legend.placement">right</option>
     <option name="charting.axisTitleY.text">Count</option>
     <option name="charting.axisTitleX.text">Time</option>
     <drilldown>
       <eval token="earliest_dd">relative_time($earliest$, "-30m")</eval>
       <eval token="latest_dd">relative_time($latest$, "+30m")</eval>
     </drilldown>
   </chart>
 </panel>
 <panel depends="$earliest_dd$">
   <table>
     <title>PANEL2</title>
     <search>
       <query>index=_internal sourcetype=splunkd earliest = $earliest_dd$ latest = $latest_dd$
       </query>
       <earliest>$field1.earliest$</earliest>
       <latest>$field1.latest$</latest>
     </search>
     <option name="wrap">undefined</option>
     <option name="rowNumbers">undefined</option>
     <option name="drilldown">row</option>
     <option name="charting.legend.placement">right</option>
     <option name="dataOverlayMode">none</option>
     <option name="count">10</option>
   </table>
 </panel>

[sridharreddy]

@sundareshr

It is not working I am getting this error " Invalid value "NaN" for time term 'earliest'" in panel2.I think error is in this area

    <eval token="earliest_dd">relative_time($earliest$, "-30m")</eval>
    <eval token="latest_dd">relative_time($latest$, "+30m")</eval>

[sundareshr]

Try this

     <eval token="earliest_dd">relative_time($click.value$, "-30m")</eval>
     <eval token="latest_dd">relative_time($click.value$, "+30m")</eval>

You may have to adjust it based on which field you are retreiving the time value from.

http://docs.splunk.com/Documentation/Splunk/6.4.2/Viz/tokens#Define_tokens_for_dynamic_drilldown

[sridharreddy]

@sundareshr Thanks for the idea.

It is working fine for this _internal data. but when I tired to implement this logic in requirement i have issue.

requirement is like: I have a timechart query which is a linegraph with durations in panel1 when I CLICK at a point in a linegraph it should display a table in panel2 related to panel1 CLICKTIME +30 min and -30 min.Panel1 and panel2 query is similar.when I click at a point in a panel1 line graph this is the error " Error in 'search' command: Unable to parse the search: Invalid time bounds in search: start=1470420626 > end=1800."

I think earliest is passing from earliest_dd but latest_dd is not passing properly to panel2. Any idea?

[sundareshr]

That's strange.. Try this for latest

<eval token="latest_dd">relative_time($earliest_dd$, "+60m")</eval>

[sridharreddy]

@sundareshr

Ya it is strange.I tried that combination also earlier no luck 🙂

[sundareshr]

Try this in your query in Panel2

index=xyz earliest=$earliest_dd$ [|makeresults | eval latest=relative_time($earliest_dd$, "+60m") | fields latest] | rest of your query