How to drilldown an event to see data 15 minutes before and after the events time?

[VI371887]

Can we drilldown an event to see data 15 minutes before that events time and 15 minutes after

For example, the event is:

[8/16/18 6:49:41:163 EST] Website crashed Error : 404
[8/16/18 6:58:41:163 EST] Website crashed Error : 404
[8/16/18 7:25:41:163 EST] Website crashed Error : 404
[8/16/18 8:15:41:163 EST] Website crashed Error : 404

So I have a drop-down to select error code and see it's events, above user has selected error 404 for a time which lists all 404 events in last 30 minutes.

If the user selects the first event with [8/16/18 6:49:41:163 EST] they should be able to see 15 minutes before and after.

[niketn · Accepted solution]

@VI371887 following are the two options you can achieve your desired results for drilldown:

Option 1) Use independent search to perform relative_time() on clicked _time in the table and use Search Event Handler to set the required tokens i.e. adjusted Earliest and Latest time for drilldown panel. You can refer to one for my older answers on similar lines for working with _time field using independent search. https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html

PS: You can also use <eval> node in simple xml in the search event handler for independent search to set the adjusted time, however, time related operations like relative_time() may not function well with this approach and has been called out in the documentation. So I have not explained that as an answer.

Option 2) Use hidden fields in your main panel where eval with relative_time() function would calculate adjusted earliest and latest time in the main search. However, <fields> attribute for the table will be used to hide these two fields by showing all the remaining fields instead (you would need to know the other field names i.e. in your case Time, Error Message and Error Code fields). You can check out Splunk Dashboard Examples App from example which has the example for Drilldown from table using Hidden Field.

Following is a run anywhere example for both approach based on Splunk's _internal index (similar to your question).

Following is the Simple XML Code for screenshot attached:

<dashboard>
  <label>Pick Relative Time Token for Drilldown</label>
  <!-- Independent Search for setting relative time based on clicked time (Option 1)-->
  <search>
    <query>| makeresults
| eval clickedEventTime=$tokClickedEventTime$
| eval adjEarliestTime=relative_time(clickedEventTime,"-15m")
| eval adjLatestTime=relative_time(clickedEventTime,"+15m")
    </query>
    <done>
      <set token="tokAdjEarliestTime">$result.adjEarliestTime$</set>
      <set token="tokAdjLatestTime">$result.adjLatestTime$</set>
    </done>
  </search>
  <row>
    <panel>
      <title>Option 1: Using independent search to get adjusted Earliest and Latest Time</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd
| timechart count by log_level limit=2 useother=f</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">5</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <set token="tokClickedEventTime">$click.value$</set>
        </drilldown>
      </table>
    </panel>
    <panel>
      <title>Option 1: Panel to Test Adjusted Time</title>
      <table>
        <title>tokClickedEventTime: $tokClickedEventTime$ | tokAdjEarliestTime: $tokAdjEarliestTime$ | tokAdjLatestTime: $tokAdjLatestTime$</title>
        <search>
          <query>| makeresults 
| fields - _time 
| eval adjEarliestTimeString=strftime($tokAdjEarliestTime$,"%Y/%m/%d %H:%M:%S") 
| eval adjLatestTimeString=strftime($tokAdjLatestTime$,"%Y/%m/%d %H:%M:%S")</query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
        </search>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 2: Using hidden fields with adjusted Earliest and Latest Time for drilldown</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd
| timechart count by log_level limit=2 useother=f
| eval adjustedEarliestTime=relative_time(_time,"-15m"), adjustedLatestTime=relative_time(_time,"+15m")</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">5</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <fields>["_time","INFO","ERROR","WARN"]</fields>
        <drilldown>
          <set token="tokAdjustedEarliestTimeHidden">$row.adjustedEarliestTime$</set>
          <set token="tokAdjustedLatestTimeHidden">$row.adjustedLatestTime$</set>
        </drilldown>
      </table>
    </panel>
    <panel>
      <title>Option 2: Panel to Test Adjusted Time</title>
      <table>
        <title>tokClickedEventTime: $tokClickedEventTime$ | tokAdjustedEarliestTimeHidden: $tokAdjustedEarliestTimeHidden$ | tokAdjustedLatestTimeHidden: $tokAdjustedLatestTimeHidden$</title>
        <search>
          <query>| makeresults 
| fields - _time 
| eval adjEarliestTimeString=strftime($tokAdjustedEarliestTimeHidden$,"%Y/%m/%d %H:%M:%S") 
| eval adjLatestTimeString=strftime($tokAdjustedLatestTimeHidden$,"%Y/%m/%d %H:%M:%S")</query>
          <earliest>-1m</earliest>
          <latest>now</latest>
        </search>
      </table>
    </panel>
  </row>
</dashboard>

[niketn]

@VI371887 if your issue is resolved then do accept the answer to mark this question as answered!